database 将图像存储在数据库与文件系统中

Storing images in a database versus a filesystem

提问by Dimitri

Well we all know how many arguments and lives have been lost with the discussion of using databases for file storage (images specifically). I'm in a bit of a pickle when it comes to deciding on how to proceed with my project.

好吧，我们都知道在讨论使用数据库进行文件存储（特别是图像）时已经失去了多少争论和生命。在决定如何继续我的项目时，我有点不高兴。

I have a website that allows admins to upload employee pictures. For now, these pictures are stored in BLOB in my MySQL database. Also, I have a windows application that runs alongside the website. This application enables employees to punch in and have their pictures appear when they've successfully done so. The picture is retrieved via a mysql query within the application (from a non-local remote location) that converts the image content to a readable image that's being outputted in a picture box, confirming the identity of the employee.

我有一个网站，允许管理员上传员工图片。目前，这些图片存储在我的 MySQL 数据库中的 BLOB 中。另外，我有一个与网站一起运行的 Windows 应用程序。此应用程序使员工能够在成功打卡后显示他们的照片。图片是通过应用程序中的 mysql 查询（从非本地远程位置）检索的，该查询将图像内容转换为在图片框中输出的可读图像，从而确认员工的身份。

In my eyes, it is much much easier to have the images stored in the database and have them retrieved via a simple query. I found this a lot easier than storing image paths in the database and having to deal with the application downloading the images. I also don't have to deal with collisions, folder organization and security and paths being re-written for x,y reasons, etc etc.

在我看来，将图像存储在数据库中并通过简单的查询检索它们要容易得多。我发现这比在数据库中存储图像路径和处理下载图像的应用程序要容易得多。我也不必处理冲突、文件夹组织和安全性以及由于 x、y 原因等而重写的路径。

The images stored in the DB are a mere 20 kb after being cropped to a certain size. My question is, is it still worth embedding the database with image paths or should they simply be stored as they are right now? If storing images in the database is still ill-advised in this case, is there a formal way to store image paths?

存储在数据库中的图像在裁剪到一定大小后只有 20 kb。我的问题是，是否仍然值得将图像路径嵌入数据库，还是应该像现在这样简单地存储它们？如果在这种情况下将图像存储在数据库中仍然不明智，是否有一种正式的方法来存储图像路径？

Any help on this would be greatly appreciated. If this question doesn't belong here, I'll be happy to move it.

对此的任何帮助将不胜感激。如果这个问题不属于这里，我很乐意移动它。

采纳答案by Alex Trebek

If the images are user data, rather than part of your application's code or theme, then storing the images in the database is a good idea, because…

如果图像是用户数据，而不是应用程序代码或主题的一部分，那么将图像存储在数据库中是个好主意，因为……

• Backups are easier to manage if all you have to back up is the database. On the other hand, if you store some application data in the database and some in the filesystem, then you'll have to coordinate the backup schedules of your database and your filesystem to ensure that the two are consistent.

  If you have a database administrator at your disposal, then great! Your backups should already be taken care of. If not, then database backups may be slightly tricky to set up, but once you do have a backup system, it can be better than filesystem backups. For example, many database systems have support for streaming replication.

• If your application is load-balanced and served by a pool of multiple webservers, then you'll either have to replicate the data to all of the machines, or share them among your servers using a network filesystem.

• 如果您只需要备份数据库，备份就更容易管理。另一方面，如果您将一些应用程序数据存储在数据库中，而将一些应用程序数据存储在文件系统中，那么您必须协调数据库和文件系统的备份计划以确保两者一致。

  如果您有一个数据库管理员可以使用，那就太好了！您的备份应该已经得到处理。如果没有，那么数据库备份的设置可能会有些棘手，但是一旦您有了备份系统，它就会比文件系统备份更好。例如，许多数据库系统都支持流式复制。

• 如果您的应用程序是负载平衡的并且由多个网络服务器池提供服务，那么您要么必须将数据复制到所有机器，要么使用网络文件系统在您的服务器之间共享它们。

Of course, having the images on a filesystem also has its advantages, namely in performance and simplicity, since most webservers are built to serve static files. A hybrid approach could give you the best of both worlds:

当然，将图像放在文件系统上也有其优势，即性能和简单性，因为大多数网络服务器都是为静态文件而构建的。混合方法可以让您两全其美：

• The images stored in the database would be the authoritative data.
• Your application can have a feature to extract them as files in their local filesystem as a kind of cache. That cache can be rebuilt at any time, since it is not authoritative.
• The webserver can then serve the files directly from the filesystem.

• 存储在数据库中的图像将是权威数据。
• 您的应用程序可以具有将它们提取为本地文件系统中的文件作为一种缓存的功能。该缓存可以随时重建，因为它不具有权威性。
• 然后网络服务器可以直接从文件系统提供文件。

回答by Alex Trebek

There are several reasons why I think storing images in a database is a bad idea:

我认为将图像存储在数据库中是一个坏主意的原因有几个：

1) The server will have timestamp info associated with files that the database won't keep track of. if you ever need to this for forensics reasons, the DB solution will likely be limited in this regard. Feel free to save info about images uploaded regarding IP info, timestamp, etc. in the DB though too.

1) 服务器将具有与数据库不会跟踪的文件关联的时间戳信息。如果您出于取证原因需要这样做，那么 DB 解决方案在这方面可能会受到限制。也可以在数据库中保存有关上传的关于 IP 信息、时间戳等的图像信息。

2) If you ever want these files used by, say, another system/service you'll have to constantly reference the database and interact with it, when you could far more easily just target a specific folder.

2）如果您希望这些文件被其他系统/服务使用，则您必须不断引用数据库并与之交互，这样您就可以更轻松地定位特定文件夹。

3) Any time an image needs to be retrieved, you have to open a connection to the database just to generate it. This may add extra code and steps to things that could be easier to implement by pointing to a folder.

3) 任何时候需要检索图像时，您都必须打开与数据库的连接才能生成它。这可能会向通过指向文件夹更容易实现的事情添加额外的代码和步骤。

To avoid naming collisions, if I were on a Linux box, I'd use something like a Unix timestamp as a prefix to the filename when it's saved, or simply use that (+ maybe a short random #) as the image ID altogether. So instead of 'jane-image.jpg', it'd be '1407369600_img3547.jpg'. Then, just put a reference to that in the DB and viola, that's a random enough ID where there should never be a collision, unless time starts flowing backwards. Whatever the Windows timestamp equivalent is would be used, obviously.

为了避免命名冲突，如果我在 Linux 机器上，我会在保存文件时使用 Unix 时间戳之类的东西作为文件名的前缀，或者简单地使用它（+ 可能是一个短随机 #）作为图像 ID。所以不是'jane-image.jpg'，而是'1407369600_img3547.jpg'。然后，只需在 DB 和中提琴中放置一个对它的引用，这是一个足够随机的 ID，永远不会发生冲突，除非时间开始倒流。显然，无论使用什么 Windows 时间戳等效项。

NOTE: What you're doing now isn't bad and from what it sounds like it may work best for you...but generally speaking I try not to put everything in the hands of a database, just because I can. But that's me :)

注意：您现在所做的还不错，而且听起来它可能最适合您……但一般来说，我尽量不将所有内容都放在数据库中，只是因为我可以。但那是我:)