Storing/uploading file/s with MySQL: indexing vs blob

Question

I want to allow file upload(public/per user), but not sure about how to properly do it.

I've read that it is not recommended to use MySQL for this and instead should use file system for files and indexing them in the database. I remember reading some popular(with many votes) Q/A about this in SO, but can't find it(please send a link if you can).

So how should I do it? I should use some inaccessible folder and store files there with an ID as their name and this name(ID of the file) in the table of files with user_id, and when the user request a file, I should check auth and then send the corresponding file to the user?

Answer 1

There's no single answer to this question. It depends how you want your application to work. It could be perfectly fine, for example, to put the files in a directory that is http-accessible, if you don't need to restrict access. Then you don't need to pass the file through any code, you just link directly to it.

There are also legitimate reasons to store a file in the database. For instance, it's automatically included in backups, it is guaranteed to be deleted when you delete the database row, it obeys transaction semantics, and so on.

This has been asked frequently on Stack Overflow. Here are a few links to ones that I have answered.

• Should I use MySQL blob field type? (2009)
• What is difference between storing data in a blob, vs. storing a pointer to a file? (2012)
• Save file as blob in MYSQL database or as file path (2018)

I also cover this in the chapter "Phantom Files" in my book, SQL Antipatterns Volume 1: Avoiding the Pitfalls of Database Programming.