MySQL Cookbook, 2nd Edition

Storing Images or Other Binary Data

Problem

You want to store images in MySQL.

Solution

That’s not difficult, provided that you follow the proper precautions for encoding the image data.

Discussion

Web sites are not limited to displaying text. They can also serve various forms of binary data such as images, music files, PDF documents, and so forth. Images are a common kind of binary data, and because image storage is a natural application for a database, a very common question is “How do I store images in MySQL?” Many people will answer this question by saying, “Don’t do it!” and some of the reasons are discussed in the sidebar “Should You Store Images in Your Database?” Because it’s important to know how to work with binary data, this section does show how to store images in MySQL. Nevertheless, in recognition that that may not always be the best thing to do, the section also shows how to store images in the filesystem.

Although the discussion here is phrased in terms of working with images, the principles apply to any kind of binary data, such as PDF files or compressed text. In fact, they apply to any kind of data at all, including text. People tend to think of images as special somehow, but they’re not.

One reason that image storage confuses people more often than does storing other types of information like text strings or numbers is that it’s difficult to type in an image value manually. For example, you can easily use mysql to enter an INSERT statement to store a number like