Storing Images in Blobs

Deciding whether to store your images inside the database or in separate files is one that you are going to need to make based on the pros and cons associated with the alternatives. What you do not want to do is to make the decision based on not knowing how to save the images in the database.

If you decide not to store the images in the database then most likely you will save the name of the image in the database instead and use that to reference the image where ever it is you decide to put the files. To switch to storing the images in the database itself is as simple as defining a blob field to store the image in and then using LOAD_FILE to load the image into the blob and DUMPFILE to extract the field back into an image file.

INSERT INTO myimagetable (image_name, myimage) VALUES ('myimage.jpg', LOAD_FILE('images/myimage.jpg'));
UPDATE myimagetable SET myimage = LOAD_FILE('images/myimage.jpg') WHERE image_name = 'myimage.jpg';
SELECT myimage INTO DUMPFILE 'images/myimage.jpg' FROM myimagetable WHERE image_name = 'myimage.jpg';

Changing existing database code to add the image itself is relatively simple. You will still need the file name to be stored whether you store the image in the database or not so adding the field to the database to store the images in is as simple as adding a blob field to the existing table - provided that each image can only occur once in the database.

If the same image name (referring to the same image) is allowed to occur more than once then the best solution is to create a new table containing two fields - the image name as the key and the blob to hold the actual image. The name field in the existing tables will then become a foreign key referencing this new table.


This article written by Stephen Chapman, Felgall Pty Ltd.

go to top

FaceBook Follow
Twitter Follow