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".
| Column Type | Maximum Size | Space req'd per entry |
|---|---|---|
| TINYBLOB | 255B | Data size + 1 byte |
| BLOB | 65KB | Data size + 2 bytes |
| MEDIUMBLOB | 16.7MB | Data size + 3 bytes |
| LONGBLOB | 4.3GB | Data size + 4 bytes |
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!