mysql What should be the datatype of the Mysql table column to store images?

[Shammikit]

im making a website using java jsp and the users accessing the website should be able to upload images to it.i want to know what data type i should be using to store images. im familiar with sql server and it has a data type called image but mysql doesnt. so any help would be great. Thanks   

[SCHISCHKA]

store a string with the location of the image

[Shammikit]

Just now, SCHISCHKA said:

store a string with the location of the image

if the database and the website is moved to another computer will the images be still there in the database?

[SCHISCHKA]

13 minutes ago, Shammikit said:

if the database and the website is moved to another computer will the images be still there in the database?

that depends on how good you are at relative and absolute links

[leonfagan71]

7 minutes ago, Shammikit said:

if the database and the website is moved to another computer will the images be still there in the database?

It depends on the location of the images, if you had a directory on your website where you would store the images, it would be fine.

It's possible to store images in a database but I'm not advising it.

 

Regards,
Leon.

[uzarnom]

"BLOB", Though i advice against it

[burnttoastnice]

Blob or binary, but highly not advisable.

 

In my database setup I store an absolute path to the images for each user on the server.

[uzarnom]

1 hour ago, Erik Sieghart said:

-snip

Would something like this still be done for just regularly serving images?

(sorry to try and sap more information)

[burnttoastnice]

18 hours ago, Erik Sieghart said:

Blob should die in a fire. This is the worst thing you could do.

Store file_ids and information about the files in the server. DON'T STORE ADDRESSES or links to where it's located. That needs to reside in your application layer. Your application will connect to a separate WEB SERVICE (read: not ftp) that will serve the file to the user.

Bonus points with being secure is to start generating hashes to serve to specific Users so it works like this:
> user requests file
> database sends request to web service with IP of the requester.
> web service generates hash of IP and random key and stores it locally, also what file to serve
> database sends OK that the file is ready to be downloaded once it logs who is downloading it and what file.
> user is redirected to a web link containing key.
> key is plugged into hashing function with IP and serves file to user.

Extra bonus points if you split your validation and web service servers, so that your validation server is firewalled from EVERYTHING except for your web service server.

This insures that the id is never exposed to the user, and there is no way to reverse the link you sent to the client to get other files off the server.

 My API sends the base64 of the image in the JSON response because...

 

1) I hate web caching

2) Web caching should die in a fire

3) The images are shrunk to 128x128 by imagemagick before conversion to base64 so size isn't a concern the smaller the better

[mariushm]

Let user upload the image.

Generate a hash (MD5 or SHA1 for example), you'll have a 16-20 byte key for the file. This will make it possible to not store the same image multiple times on your servers if disk space is a concern (but while extremely small there is some small risk of collisions, so it would be safer practice to binary compare existing files with that hash of your new uploaded file)

If you want to differentiate between same image uploaded by two different users or same image uploaded twice by the same user, append the user id (if you use a 32bit variable to hold the user id, then you could use 8 characters to append the 4 bytes to the file name) or some kind of unique ID (for that user) and maybe the upload time (the 32bit microtime value for example) or some random series of characters to the hash.

Now you have your unique file name .. which you can format in Base16 or Base64 as 32-40+ characters.. for example 01AE6901....56   and if you want to be file system friendly, you can store in a structure like  /images/01/01AE/01AE6901....56 

Store the image information in the database in a table .. a fixed char(32+) column .. fixed because the filename will always be the same number of characters, a unique id (if you use auto increment on primary key you have your unique id), the user id of the account who uploaded the image, upload time, maybe width and height of the image (if needed), mime type (jpg,png etc)

 

Then you can make a script like script.jsp?filename=unique_id  and maybe use mod_rewrite or similar to make your urls nice like images.domain.tld/image[unique_id].jpg

The script queries the database retrieving the row with the unique ID, gets the hash, then it knows from where to read the file on the disk using the hash, reads image, sends to the browser caching information and headers (content-type etc) if desired, then dumps the image to user.

If you want to, at a later point you can also add a layer of server caching, like using memcached to store in memory the id and hash for files so that you don't have to connect to database for that information. You could also have a script like images.domain.tld/thumbnail/120x90/[unique_id].jpg (where 120 and 90 would be passed to the script and the script could accept several combinations) and have the script create the thumbnail if it doesn't exist, store it in memory using memcached and to a physical location and serve it to user ... at a second request, the script can query memcache and get the 1-10 KB of data from ram instead of hitting the hard drive or fall back to storage if memcache drops it to make room for more often accessed resources - the basic idea is that with lots of users accessing your server at one point the hard drive won't handle so many concurrent requests, and you want the OS the cache files or you want to keep in memory stuff that's often accessed (and that's what systems like memcache do)

SSDs help with the disk I/O but you can't beat ram for lots of requests per second.