database 保存图像：文件还是 blob？

Saving images: files or blobs?

提问by kurast

When you save your images (supose you have lots of them) do you store then as blobs in your Database, or as files? Why?

当您保存图像时（假设您有很多图像），您是将图像存储为数据库中的 blob，还是存储为文件？为什么？

Duplicate of: Storing Images in DB - Yea or Nay?

重复：在数据库中存储图像 - 是还是不是？

回答by Brandon Wood

I usually go with storing them as files, and store the path in the database. To me, it's a much easier and more natural approach than pushing them into the database as blobs.

我通常将它们存储为文件，并将路径存储在数据库中。对我来说，这是一种比将它们作为 blob 推送到数据库中更容易、更自然的方法。

One argument for storing them in the database: much easier to do full backups, but that depends on your needs. If you need to be able to easily take full snapshots of your database (including the images), then storing them as blobs in the database is probably the way to go. Otherwise you have to pair your database backup with a file backup, and somehow try to associate the two, so that if you have to do a restore, you know which pair to restore.

将它们存储在数据库中的一个论点是：进行完整备份要容易得多，但这取决于您的需要。如果您需要能够轻松获取数据库（包括图像）的完整快照，那么将它们作为 blob 存储在数据库中可能是一种可行的方法。否则，您必须将数据库备份与文件备份配对，并以某种方式尝试将两者关联起来，这样如果您必须进行恢复，您就知道要恢复哪一对。

回答by Sean

It depends on the size of the image.

这取决于图像的大小。

Microsoft Research has an interesting documenton the subject

微软研究院有一篇关于这个主题的有趣文档

回答by giorgian

I've tried to use the db (SQL Server and MySQL) to store medium (< 5mb) files, and what I got was tons of trouble.

我尝试使用 db（SQL Server 和 MySQL）来存储中等（< 5mb）文件，但我遇到了很多麻烦。

1) Some DBs (SQL Server Express) have size limits;

1) 一些数据库（SQL Server Express）有大小限制；

2) Some DBs (MySQL) become mortally slow;

2）一些数据库（MySQL）变得非常慢；

3) When you have to display a list of object, if you inadvertedly do SELECT * FROM table, tons of data will try to go up and down from the db, resulting in a deadly slow response or memory fail;

3）当你必须显示一个对象列表时，如果你不小心做了SELECT * FROM table，大量的数据会试图从db上上下下，导致致命的缓慢响应或内存失败；

4) Some frontends (ruby ActiveRecord) have very big troubles handling blobs.

4) 一些前端（ruby ActiveRecord）在处理 blob 时遇到很大的麻烦。

Just use files. Don't store them all in the same directory, use some technique to put them on several dirs (for instance, you could use last two chars of a GUID or last two digits of an int id) and then store the path on db.

只需使用文件。不要将它们全部存储在同一目录中，使用某种技术将它们放在多个目录中（例如，您可以使用 GUID 的最后两个字符或 int id 的最后两个数字），然后将路径存储在 db 上。

回答by Sfynx

The performance hit of a database server is a moot issue. If you need the performance benefits of a file system, you simply cache it there on the first request. Subsequent requests can then be served directly from the file system by a direct link (which, in case of a web app, you could rewrite the HTML with before flushing the output buffer).

数据库服务器的性能损失是一个没有实际意义的问题。如果您需要文件系统的性能优势，您只需在第一次请求时将其缓存在那里。然后可以通过直接链接直接从文件系统提供后续请求（在 Web 应用程序的情况下，您可以在刷新输出缓冲区之前使用它重写 HTML）。

This provides the best of both worlds:

这提供了两全其美的：

• The authoritative store is the database, keeping transactional and referential integrity
• You can deploy all user data by simply deploying the database
• Emptying this cache (e.g. by adding a web server) would only cause a temporary performance hit while it is refilled automatically.

• 权威存储是数据库，保持事务和参照完整性
• 只需部署数据库即可部署所有用户数据
• 清空此缓存（例如通过添加 Web 服务器）只会在自动重新填充时导致临时性能下降。

There is no need to constantly hammer the database for things that won't change all the time, but the important thing is that the user data is all there and not scattered around different places, making multi-server operation and deployment a total mess. I'm always advocating the "database as the user data store, unless" approach, because it is better architecturally, and not necessarily slower with effective caching.

没有必要为了不会一直变化的东西不断敲打数据库，但重要的是用户数据都在那里，而不是分散在不同的地方，使多服务器操作和部署变得一团糟。我一直提倡“数据库作为用户数据存储，除非”方法，因为它在架构上更好，并且有效缓存不一定更慢。

Having said that, a good reason to use the file system as the authoritative store would be when you really need to use external independent tools for accessing it, e.g. SFTP and whatnot.

话虽如此，使用文件系统作为权威存储的一个很好的理由是当您确实需要使用外部独立工具来访问它时，例如 SFTP 等。

回答by APC

The question is, does your application handle BLOBS or other files like other application data? Do your users upload images alongside other data? If so, then you ought to store the BLOBs in the database. It makes it easier to back up the database and, in the event of a problem, to recover to a transactionally consistent state.

问题是，您的应用程序是否像其他应用程序数据一样处理 BLOBS 或其他文件？您的用户是否将图像与其他数据一起上传？如果是这样，那么您应该将 BLOB 存储在数据库中。它可以更轻松地备份数据库，并在出现问题时更轻松地恢复到事务一致的状态。

But if you mean images which are part of the application infratstructure rather than user data then probably the answer is, No.

但是如果你的意思是图像是应用程序基础结构的一部分而不是用户数据，那么答案可能是，不。

回答by Mikey

Given that you might want to save an image along with a name, brief description, created date, created by, etc., you might find it better to save in a database. That way, everything is together. If you saved this same info and stored the image as a file, you would have to retrieve the whole "image object" from two places...and down the road, you might find yourself having syncing issues (some images not being found). Hopefully this makes sense.

鉴于您可能想要保存图像以及名称、简要说明、创建日期、创建者等，您可能会发现保存在数据库中会更好。这样，一切都在一起了。如果您保存了相同的信息并将图像存储为文件，则必须从两个地方检索整个“图像对象”……然后，您可能会发现自己有同步问题（某些图像未找到） . 希望这是有道理的。

回答by The Disintegrator

By saving you mean to use them to show in a webpage or something like that? If it's the case, the better option will be to use files, if you use a database it will be constantly hammered with the request for photos. And it's a situation that doesn't scale too well.

通过保存您的意思是使用它们显示在网页或类似的东西中？如果是这种情况，更好的选择是使用文件，如果您使用数据库，它将不断受到照片请求的影响。这是一种不能很好地扩展的情况。

回答by chaos

If I'm running on one web server and will only ever be running on one web server, I store them as files. If I'm running across multiple webheads, I put the reference instance of the image in a database BLOBand cache it as a file on the webheads.

如果我在一台 Web 服务器上运行并且只会在一台 Web 服务器上运行，我会将它们存储为文件。如果我在多个 webheads 上运行，我会将图像的引用实例放入数据库中，BLOB并将其作为文件缓存在 webheads 上。

回答by chaos

Blobs can be heavy on the db/scripts, why not just store paths. The only reason we've ever used blobs is if it needs to be merge replicated or super tight security for assets (as in cant pull image unless logged in or something)

Blob 在 db/scripts 上可能很重，为什么不只存储路径。我们曾经使用过 blob 的唯一原因是它是否需要合并复制或资产的超严格安全性（例如，除非登录或其他原因，否则无法拉取图像）

回答by Kirti Chaturvedi

Using file System is better as the basic feature you would be provided with while storing images as a blob would be 1. mutability which is not needed for an image as we won't be changing the binary data of images, we will be removing images as whole only 2. Indexed searching :which is not needed for image as the content of images can't be indexed and indexed searching searches the content of the BLOB.

使用文件系统更好，因为将图像存储为 blob 时将提供的基本功能是 1。图像不需要可变性，因为我们不会更改图像的二进制数据，我们将删除图像2. 索引搜索：图像不需要，因为图像的内容不能被索引，索引搜索搜索 BLOB 的内容。

Using file system is beneficial here because 1. its cheaper 2. Using CDN for fast access

使用文件系统在这里是有益的，因为 1. 它更便宜 2. 使用 CDN 进行快速访问

hence one way forward could be to store the images as a file and provide its path in database

因此，一种前进的方法可能是将图像存储为文件并在数据库中提供其路径