MySQL Cookbook, 2nd Edition

Retrieving Images or Other Binary Data

Problem

You can store images or other binary data values in your database, using the techniques discussed in Storing Images or Other Binary Data. But how do you get them back out?

Solution

You need nothing more than a SELECT statement. Of course, what you do with the information after you retrieve it might be a little more involved.

Discussion

As described in Storing Images or Other Binary Data, it’s difficult to issue a statement manually that stores a literal image value into a database, so normally you use LOAD_FILE() or write a script that encodes the image data for insertion. However, there is no problem at all entering a statement that retrieves an image:

SELECT * FROM image WHERE id = 1;

But binary information tends not to show up well on text-display devices, so you probably don’t want to do this interactively from the mysql program unless you want your terminal window to turn into a horrible mess of gibberish, or possibly even to lock up. It’s more common to use the information for display in a web page. Or you might send it to the client for downloading, although that is more common for nonimage binary data such as PDF files. (Serving Query Results for Download discusses downloading.)

To display an image in a web page, include an <img> tag in the page that tells the client’s web browser where to get the image. If you’ve stored images as files in a directory that the web server has access to, you can refer to an image directly. For example, ...