Why store binary data in MySQL?

Question

I'm a little confused - what are the pros of storing binary data in DB? Is it for security reasons, or there are some more complicated motives i don't see?

Thanks for your time.

Answer 1

As opposed to what? Putting it in the filesystem?

The drawbacks to using the filesystem for binary file storage would be:

• you don't get ACID compliance;

• if you might be hosting the application on more than one server (eg load-balancing, failover), you have to work out some kind of shared file storage to avoid the backend getting out of sync;

• having only one storage backend instead of two makes deployment simpler.

So with database-only storage, you don't have to worry about creating a folder with write access for the web user, the whole app can be read-only. If you need to move the application, you can just check it out of source control and point it to the database without having to copy more file data across. Your database backups can be covering everything instead of having to have a separate file backup step. And so on.

On the other hand the drawbacks to database BLOB storage:

• unwieldy for very large files;

• you don't get to use the web server to serve the files up efficiently for free.