Entry

How can I store a picture in MySQL?

How can I get an image (or blob field) back out of the database and into a file?

Dec 15th, 2005 08:06
Marco Basset, May 18th, 2000 06:11 Nathan Wallace, Sasha Pachev and the book "MYSQL 4 by Anthony Butcher

I quote the answer of Nathan Wallace and then I will add a correction:

"You should use BLOB type. Choose a subtype according to the sizes of
pictures that you intend to store.

If you are using 3.23.xx, you should use load_file(...) function for
storing images.

To get the image back out into a file try "select ... into outfile".

In general though, it's really not worth doing this for performance
reasons... in fact there aren't many reasons to do this at all."

The answer is correct in the part concerning image loading, for example:

update nametable set namecolumn=load_file("/home/goofy/image.gif") where
id=7;

works; but if you want to correctly rebuild the file from data in the
DB, you dont have to use "select ... into outfile" but instead "select
... into dumpfile" (using "outfile" the formatting characters in the
file would be translated).

I think storing images directly in the DB is sometimes very useful: for
example in one of my DB I have a very very large number of little images
to store (one for every row in the DB) and I don't want to have a so
large amount of files in the filesystem, so I load the images in the
table; it is better also when you want to backup your data.