Database Reference
In-Depth Information
• Images stored in the filesystem constitute, in essence, a foreign key. Image manip‐
ulation requires two operations: one in the database and one in the filesystem. This
in turn means that if you require transactional behavior, it's more difficult to im‐
plement—not only do you have two operations, but they take place in different
domains. Storing images in the database is simpler because adding, updating, or
removing an image requires only a single-row operation. It becomes unnecessary
to make sure the image table and the filesystem remain in synchrony.
• It can be faster to serve images over the Web from the filesystem than from the
database because the web server itself opens the file, reads it, and writes it to the
client. Images stored in the database must be read and written twice. First, the
MySQL server reads the image from the database and writes it to your web script.
Then the script reads the image and writes it to the client.
• Images stored in the filesystem can be referred to directly in web pages by means
of
<img>
tag links that point to the image files. Images stored in MySQL must be
served by a script that retrieves an image and sends it to the client. However, even
if images are stored in the filesystem and accessible to the web server, you might
still want to serve them through a script. This would be appropriate if you must
account for the number of times you serve each image (such as for banner ad dis‐
plays where you charge customers by the number of ad impressions) or if you want
to select an image at request time (such as when you pick an ad at random).
Storing images with LOAD_FILE()
The
LOAD_FILE()
function takes an argument indicating a file to be read and stored in
the database. For example, to load an image stored in
/tmp/myimage.png
into a table,
do this:
INSERT
INTO
mytbl
(
image_data
)
VALUES
(
LOAD_FILE
(
'/tmp/myimage.png'
));
To load images with
LOAD_FILE()
, these requirements must be satisfied:
• The image file must be located on the MySQL server host.
• The file must be readable by the server.
• You must have the
FILE
privilege.
These constraints mean that
LOAD_FILE()
is available only to some MySQL users.
Storing images using a script
If
LOAD_FILE()
is not an option, or you don't want to use it, you can use a short program
to load your images. The program should either read the contents of an image file and
create a row that contains the image data, or create a row that indicates where in the
filesystem the image file is located. If you elect to store the image in MySQL, include
the image data in the row-creation statement the same way as any other kind of data.
