我应该使用 MySQL blob 字段类型吗? [英] Should I use MySQL blob field type?

问题描述

我正在努力决定是否应该在我即将开展的项目中使用 MySQL blob 字段类型.

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.

所以以一种方式看待它，如果我走 MySQL 路线，我不必担心病毒的蔓延或随机的 php 文件被上传并以某种方式执行.我也有一个更简单的方法来许可和保持数据与记录密切相关.

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.

另一个明显的途径是将数据存储在 webroot 之外的特定文件夹结构中.在这种情况下，我必须为文件夹/文件制定一个特殊的命名约定，以跟踪它们在数据库中引用的内容.

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.

使用 MySQL blob 字段类型会影响性能吗?我担心选择一个会阻碍网站未来发展的解决方案，以及选择一个不容易维护的解决方案.

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.

推荐答案

如果您的 Web 服务器将通过 Web 提供这些上传的文件，那么如果将它们存储在文件系统中，性能几乎肯定会更好.然后，Web 服务器将能够应用 HTTP 缓存提示，例如 Last-Modified 和 ETag，这将有助于多次访问同一文件的用户的性能.此外，Web 服务器会在服务时自动为文件设置正确的 Content-Type.如果您将 blob 存储在数据库中，那么您最终会实现上述功能，而当您应该从您的网络服务器免费获取它们时，您将实现更多功能.

If your web server will be serving these uploaded files over the web, the performance will almost certainly be better if they are stored on the filesystem. The web server will then be able to apply HTTP caching hints such as Last-Modified and ETag which will help performance for users accessing the same file multiple times. Additionally, the web server will automatically set the correct Content-Type for the file when serving. If you store blobs in the database, you'll end up implementing the above mentioned features and more when you should be getting them for free from your web server.

此外，从数据库中提取大型 blob 数据可能最终成为数据库的性能瓶颈.此外，您的数据库备份可能会变慢，因为它们将备份更多数据.如果您在开发期间进行临时查询，那么在 select 语句的结果集中看到大块会很不方便.如果您只想检查上传的文件，这样做会很不方便且迂回，因为它会笨拙地存储在数据库列中.

Additionally, pulling large blob data out of your database may end up being a performance bottleneck on your database. Also, your database backups will probabaly be slower because they'll be backing up more data. If you're doing ad-hoc queries during development, it'll be inconvenient seeing large blobs in result sets for select statements. If you want to simply inspect an uploaded file, it will be inconvenient and roundabout to do so because it'll be awkwardly stored in a database column.

我会坚持将文件存储在文件系统上并将文件路径存储在数据库中的常见做法.

I would stick with the common practice of storing the files on the filesystem and the path to the file in the database.

这篇关于我应该使用 MySQL blob 字段类型吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！