我应该使用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服务器将能够应用HTTP缓存提示，例如Last-Modified和ETag，这将有助于提高用户多次访问同一文件的性能.此外，Web服务器在提供服务时将自动为文件设置正确的Content-Type.如果将Blob存储在数据库中，那么当您应该从Web服务器免费获取它们时，最终将实现上述功能以及更多功能.

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语句的结果集中看到较大的blob会很不方便.如果您只想检查上载的文件，这样做会很不方便，因为它会笨拙地存储在数据库列中.

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屋！