Build Your Own DatabaseDriven Website Using PHP amp;amp; MySQL [Electronic resources] نسخه متنی

Binary Column Types

As with most database-driven Web applications,
the first thing to consider is the layout of the database. For each of the
files that's stored in our database, we will store the file name, the MIME
type (e.g. image/pjpeg for JPEG image files), a short description
of the file, and the binary data itself. Here's the CREATE TABLE statement that
must be entered in MySQL to create the table:

mysql>CREATE TABLE filestore (
    ->  ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    ->  FileName VARCHAR(255) NOT NULL,
    ->  MimeType VARCHAR(50) NOT NULL,
    ->  Description VARCHAR(255) NOT NULL,
    ->  FileData MEDIUMBLOB
    ->);

Most of this syntax should be familiar to you; however, the MEDIUMBLOB column
type is new. If you consult the MySQL Column Type Reference in "MySQL Column Types", you'll find that MEDIUMBLOB is the same
as MEDIUMTEXT, except that it performs case-sensitive searches and sorts. In
fact, from MySQL's point of view, there is no difference between binary data
and blocks of text—both are just long strings of bytes to be stored
in the database. The reason we'll use MEDIUMBLOB instead
of MEDIUMTEXT is simply to anticipate the situation where
we might need to compare the contents of one binary file with another. In
such cases, we'd want the comparison to be 'case sensitive', as binary files
may use byte patterns that are equivalent to alphabetical letters, and we'd
want to distinguish between the byte pattern that represents 'A' from that
which represents 'a'.

MEDIUMBLOB is one of several 'BLOB' column types designed to store variable-length binary data (BLOB
stands for Binary Large OBject). These column types differ from one another
only in two aspects: the maximum size of the data a particular value in the
column can contain, and the number of bytes used to store the length of each
data value. The different binary column types are listed with these details
in "Binary Column Types in MySQL".

As you can see, the table we created above will be able to store files
up to 16.7MB in size. If you think you'll need larger files, you can bump
the FileData column up to a LONGBLOB.
Each file will occupy 1 more byte in the database, because MySQL will require
that extra byte in order to record larger file sizes, but you'll be able to
store files up to 4.3GB in size—assuming that your operating system
allows files of that size!