Should I use MySQL blob field type?

Question

I am struggling to decide if I should be using the MySQL blob field type in an upcoming project I have.

My basic requirements are, there will be certain database records that can be viewed and have multiple files uploaded and "attached" to those records. Seeing said records can be limited to certain people on a case by case basis. Any type of file can be uploaded with virtually no restriction.

So looking at it one way, if I go the MySQL route, I don't have to worry about virus's creeping up or random php files getting uploaded and somehow executed. I also have a much easier path for permissioning and keeping data tied close to a record.

The other obvious route is storing the data in a specific folder structure outside of the webroot. in this case I'd have to come up with a special naming convention for folders/files to keep track of what they reference inside the database.

Is there a performance hit with using MySQL blob field type? I'm concerned about choosing a solution that will hinder future growth of the website as well as choosing a solution that wont be easy to maintain.

Answer 1

Many people recommend against storing file attachments (usually this applies to images) in blobs in the database. Instead they prefer to store a pathname as a string in the database, and store the file somewhere safe on the filesystem. There are some merits to this:

• Database and database backups are smaller.
• It's easier to edit files on the filesystem if you need to work with them ad hoc.
• Filesystems are good at storing files. Databases are good at storing tuples. Let each one do what it's good at.

There are counter-arguments too, that support putting attachments in a blob:

• Deleting a row in a database automatically deletes the associated attachment.
• Rollback and transaction isolation work as expected when data is in a row, but not when some part of the data is on the filesystem.
• Backups are simpler if all data is in the database. No need to worry about making consistent backups of data that's changing concurrently during the backup procedure.

So the best solution depends on how you're going to be using the data in your application. There's no one-size-fits-all answer.

I know you tagged your question with MySQL, but if folks reading this question use other brands of RDBMS, they might want to look into BFILE when using Oracle, or FILESTREAM when using Microsoft SQL Server 2008. These give you the ability store files outside the database but access them like they're part of a row in a database table (more or less).