MySQL blob: how to get just a subset of the stored

问题:

I would like to use MYSQL as a storage system for a huge number of files. I would like to read/write just a portion of the data stored in a column (data is stored as bytes) so I don't have to load the entire file into the application (because it can be > than a GB). So, in brief, I would like to have random read/write access in a blob column without loading the entire data into memory. Are there functions available to perform these operations? Thank you.

回答1:

MySQL treats blobs the same as strings (more or less):

BLOB values are treated as binary strings (byte strings). They have no character set, and sorting and comparison are based on the numeric values of the bytes in column values.

So all the usual string functions work on blobs. In particular, you can use substring to grab just part of of a blob.

That said, storing a multi-gigabyte data file in a relational database as a BLOB isn't the best thing to do. You'd be better off storing the file's metadata in the database and leaving the file itself in the file system; file systems are pretty good at managing files, relational databases are good at handling structured data.

回答2:

You could use e.g. MID() [1] to cut portions of the BLOB; though I would prefer to store files in the file system, not in a database. MySQL performs rather poor on BLOBs.

[1] http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_mid

回答3:

You can try this approach. Store the meta data of your files (like path, name, etc.) in the database and store the files under a directory. From the database you can fetch the filepath and the read the file in random access mode. Using the file-offset you can get the required subset of the stored data.