Physical database design-whether the file should be stored in the database

Multimedia files have been widely used in many programs. such as the user's avatar, car product pictures and so on.

From my personal experience, the path of the file is stored in the database, and then the file itself is stored on the hard disk as a perpetual solution.

In fact, both scenarios have a good foothold in storing the image path and storing the image file itself, but most programmers store the files outside the database. Although there is no big problem with this approach, there is a certain risk.

First, the problem description

Here's a look at the drawbacks of storing files outside the database.

1, garbage collection problem.

When you want to delete a picture, you can only delete the records in the database, the picture file is not the way to delete the SQL statement, you must be in your high-level program to maintain the images. Make sure to delete the image file while deleting the data row.

2. The file does not support the database backup tool

When we back up the database, there is no way to back up together with external files. So when backing up, you must use the File System Backup tool to back up external pictures at the same time.

3. File does not support SQL access permission settings

The external file will bypass the access permissions set through the GRANT and revoke SQL statements. SQL permissions manage this access to tables and columns, but they do not apply to external files.

4. Picture path is not SQL data type

The picture path is not a SQL data type, it is just a string, and the database does not verify that this is a valid file path. If the file is renamed, moved, or deleted, the database does not automatically update the corresponding path.

There are also advantages to storing files outside the database:

1, the database can behaving a lot when there is no picture, because the picture is bigger than the simple data type.

2. When no pictures are included, backing up the database is faster and the backup file is smaller, although you must perform an additional file backup, but it is easier to manage than backing up a large database.

3, if the picture is stored outside the database, then the preview of the picture or edit can use a more straightforward way to handle. For example, often to edit or modify the picture, then stored in the database is a good choice.

Second, the solution uses BLOB types when needed

When stored, use a binary blob to store the file.

Pictures are stored in the database and do not require additional loading, and there is no risk of incorrect file paths.

Deleting a record also deletes the picture.

When the record is updated, locks are added, so no other clients update the picture concurrently.

The database backup will contain all the pictures.

SQL Permissions control is also valid for pictures.

All kinds of advantages and disadvantages, according to their own procedures, at the discretion of the right solution to choose.

Physical database design-whether the file should be stored in the database