Database Reference
In-Depth Information
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 answer this
question by saying, “Don't do it!” and some of the reasons are discussed in the following
sidebar. 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 file‐
system.
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 some‐
how, 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
3.48
or a string like
Je
voudrais
une
bicyclette
rouge
, but images
contain binary data and it's not easy to refer to them by value. So you must do something
else. Your options are:
• Use the
LOAD_FILE()
function.
• Write a program that reads in the image file and constructs the proper
INSERT
statement for you.
Either way, when you store images in the database, use a binary string data type such as
a
BLOB
, not a character string type.
Should You Store Images in Your Database?
Deciding where to store images involves trade-offs. There are advantages and disad‐
vantages regardless of whether you store images in the database or in the filesystem:
• Storing images in a database table bloats the table. With a lot of images, you're more
likely to approach any limits your operating system places on table size. On the
other hand, if you store images in the filesystem, directory lookups may become
slow.
• Using a database centralizes storage for images that are used across multiple web
servers on different hosts. Images stored in the filesystem must be stored locally on
the web server host, so in a multiple-host situation, you must replicate the set of
images to the filesystem of each host. If you store the images in MySQL, only one
copy of the images is required because each web server can get the images from the
same database server.
