什么时候推荐使用 MySQL BLOB？

When is using MySQL BLOB recommended?

提问by amosrivera

I'm coding an application that will be uploading and deleting many files, i usually just move the files to a folder in the server naming them with the row unique id. But as i understand MySQL also lets me store binary data (files) when would this be a better choice?.

我正在编写一个将上传和删除许多文件的应用程序，我通常只是将文件移动到服务器中的一个文件夹中，并使用 unique 行命名它们id。但据我所知，MySQL 还允许我存储二进制数据（文件），什么时候这会是更好的选择？

Please use solid arguments, like When does using BLOB will mean performance improvement?.

请使用可靠的论据，例如何时使用 BLOB 将意味着性能改进？。

P.S: I'm using MyISAM if that matters.

PS：如果这很重要，我正在使用 MyISAM。

Thanks.

谢谢。

---

---

UPDATE:

更新：

Related questions:
- Storing Images in DB - Yea or Nay?
- To Do or Not to Do: Store Images in a Database(thanks to Sebastian)

相关问题：
-在数据库中存储图像 - 是还是不是？
-做或不做：将图像存储在数据库中（感谢塞巴斯蒂安）

UPDATE 2

更新 2

Storing the files in the database is not a needi'm trying to know when is this a better idea than storing them in folders.

不需要将文件存储在数据库中，我想知道什么时候这比将它们存储在文件夹中更好。

采纳答案by Sebastian Zaklada

Read:

读：

• MySQL Binary Storage using BLOB VS OS File System: large files, large quantities, large problems
• To Do or Not to Do: Store Images in a Database

• MySQL 二进制存储使用 BLOB VS OS 文件系统：大文件、大数量、大问题
• 要做或不做：将图像存储在数据库中

which concludes

其结论

If you on occasion need to retrieve an image and it has to be available on several different web servers. But I think that's pretty much it.

• If it doesn't have to be available on several servers, it's always better to put them in the file system.
• If it has to be available on several servers and there's actually some kind of load in the system, you'll need some kind of distributed storage.

如果您有时需要检索图像并且它必须在多个不同的 Web 服务器上可用。但我认为差不多就是这样。

• 如果它不必在多台服务器上可用，最好将它们放在文件系统中。
• 如果它必须在多台服务器上可用，并且系统中实际上存在某种负载，则您将需要某种分布式存储。

回答by Roman

If you are using MyISAM db engine then BLOB fields can be indexed so you can perform quick searches on your files using the database.

如果您使用的是 MyISAM 数据库引擎，则可以对 BLOB 字段进行索引，以便您可以使用数据库对文件执行快速搜索。

Also another advantage of storing files in BLOB fields is that they can be accessed more efficiently than files on the disk (there is no need for directory traversal, open, read, close).

将文件存储在 BLOB 字段中的另一个优点是它们可以比磁盘上的文件更有效地访问（不需要目录遍历、打开、读取、关闭）。

If you are planning to store lots of files in MYSQL, it's usually a good practice to have the files stored in a separate table. This allows you to scan the meta info without stumbling over the blobs. Then, when you actually need to fetch a blob, the JOIN is adequately efficient.

如果您计划在 MYSQL 中存储大量文件，那么将文件存储在单独的表中通常是一个好习惯。这使您可以扫描元信息而不会绊倒斑点。然后，当您确实需要获取一个 blob 时，JOIN 就足够高效了。

回答by Tieson T.

Well, it's a bit old, but this article makes a few decent arguments for BLOB storage: http://www.dreamwerx.net/site/article01.

好吧，它有点旧，但这篇文章为 BLOB 存储提出了一些不错的论据：http: //www.dreamwerx.net/site/article01。

While not a performance gain per se, having your images and whatnot in a DB as opposed to in a directory should also eliminate problems with hotlinking (assuming this is a web app that's publicly available).

虽然本身不​​是性能提升，但将图像和其他内容放在数据库中而不是目录中也应该可以消除盗链问题（假设这是一个公开可用的网络应用程序）。

回答by guykaplan

Memcache is not an alternative solution as you need to manage redundancy and TTL across distributed servers which make it harder to maintain.

Memcache 不是替代解决方案，因为您需要跨分布式服务器管理冗余和 TTL，这使得维护变得更加困难。

The better solution in my opinion is to put public static data on CDN which is distributed by design and private static data on the DB for ease of distribution across multiple servers.

我认为更好的解决方案是将公共静态数据放在 CDN 上，CDN 上是按设计分发的，私有静态数据放在 DB 上，以便于跨多个服务器分发。

Each server can implement it's own Memcache upon each hit.

每个服务器都可以在每次命中时实现自己的 Memcache。

If you already stored data in the filesystem and you want to migrate it into database, the easiest way is to create a key,value table of the following:

如果您已经在文件系统中存储了数据，并且想要将其迁移到数据库中，那么最简单的方法是创建如下的键值表：

KEY='/image/filename' (string of the filesystem location), value=BLOB (the actual file) and build a wrapper which will get this from the database with the help of rewrite rule and application handling. This way you can use full transparency with your existing code.

KEY='/image/filename'（文件系统位置的字符串），value=BLOB（实际文件）并构建一个包装器，它将在重写规则和应用程序处理的帮助下从数据库中获取它。通过这种方式，您可以对现有代码使用完全透明。

回答by guykaplan

Are you bound to using MySQL? If not, try an ODBMS or PostgreSQL to store files, or you could store just the paths for the files. See thisfor instance.

你一定要使用 MySQL 吗？如果没有，请尝试使用 ODBMS 或 PostgreSQL 来存储文件，或者您可以只存储文件的路径。例如看这个。