I am planning the development of a photo gallery application for a client. I am developing the app in asp.net 3.5 and would like to develop it so that I can re-use the application across multiple platforms using various front-ends. Basically, I am wondering what are the dis-advantages and advantages of storing images in the database as binary files as opposed to simply storing the files in an application folder.
我正计划为客户开发照片库应用程序。我正在开发asp.net 3.5中的应用程序，并希望开发它，以便我可以使用各种前端在多个平台上重用该应用程序。基本上，我想知道将数据存储在数据库中作为二进制文件的不利和优势是什么，而不是简单地将文件存储在应用程序文件夹中。
Any advice would be greatly appreciated!
任何建议将不胜感激！
Thanks, Tristan
谢谢，特里斯坦
5 个解决方案
#1
6
The disadvantage of storing as binary is that you blow the database size to incredible sizes. If you were to use an express edition of SQL Server, which is limited to 4GB per database, you photo gallery would "finish" quite soon.
存储为二进制文件的缺点是您将数据库大小吹成令人难以置信的大小。如果您使用SQL Server的快速版本（每个数据库限制为4GB），您的照片库很快就会“完成”。
The advantage is that you can easily manipulate the access restrictions per file and per user. You just look at user rights and decide whether you serve back the image or not.
优点是您可以轻松地操纵每个文件和每个用户的访问限制。您只需查看用户权限并决定是否回送图像。
#2
18
SQL Server 2008 supports FILESTREAM storage.
SQL Server 2008支持FILESTREAM存储。
The files are stored on an NTFS volume like plain files, but are subject to transaction control and can be accessed via special file names passed to Win32 API functions (and of course any API built upon it) with additional SQL Server security checks (like GRANT options etc).
这些文件存储在NTFS卷上，就像普通文件一样，但是受事务控制的影响，可以通过传递给Win32 API函数的特殊文件名（当然还有任何构建在它上面的API）和其他SQL Server安全检查（如GRANT）进行访问。选项等）。
#3
4
File system storage will offer much better performance in saving and serving images, and is supported in every platform. If you can live without the security and transactional goodies you get from db storage, then I would go with the file system.
文件系统存储将在保存和提供图像方面提供更好的性能，并且在每个平台都受支持。如果你没有从数据库存储中获得的安全性和事务性好东西，那么我会使用文件系统。
#4
2
We had large LOB application that provided Bank tellers identification information about the member standing in front of them. Our textual data was stored in SQL Server. Image data was stored in files. The database field simply had a filename. This approach works well if you are behind the firewall. Reading and writing files is easy. The trouble is the file management. You should secure the file system so that random people cannot view the directory. Also, backups are more complicated with loose image files. You have to backup the database and the image files. The fields can reference paths that no longer exist. For example, some IT dude decides to move the image folder and now all the references in the db are broken. If your application needs to pass information through the firewall, I would suggest storing images in the SQL Server using the mentioned FileStream storage.
我们有大型LOB应用程序，为银行出纳员提供有关站在他们面前的成员的识别信息。我们的文本数据存储在SQL Server中。图像数据存储在文件中。数据库字段只有一个文件名。如果您在防火墙后面，这种方法很有效。读取和写入文件很容易。问题是文件管理。您应该保护文件系统，以便随机人员无法查看该目录。此外，对于松散的图像文件，备份更复杂。您必须备份数据库和映像文件。这些字段可以引用不再存在的路径。例如，某些IT人员决定移动图像文件夹，现在数据库中的所有引用都被破坏了。如果您的应用程序需要通过防火墙传递信息，我建议使用提到的FileStream存储将图像存储在SQL Server中。
Storing the images in the database would have saved us some grief. We would have only had to backup the database, it would be more secure, the references would never break and we would not have had to jump through hoops to get files from network outside the firewall.
将图像存储在数据库中会让我们感到悲伤。我们只需要备份数据库，它会更安全，引用永远不会破坏，我们不必跳过箍来从防火墙外的网络获取文件。
#5
1
This debate has been going on in almost any SQL Server community for ages. there are good arguments for both sides and there is definitely not just one size fits all answer. It really depends on your individual situation and on many factors, such as number of users, avg. file size, update frequency, read/write ratio, disk-subsystem yadayadayada...
几年来，几乎所有SQL Server社区都在进行这场辩论。双方都有很好的论据，肯定不只是一个尺寸适合所有答案。这实际上取决于您的个人情况和许多因素，例如用户数量，平均值。文件大小，更新频率，读/写比率，磁盘子系统yadayadayada ...
But as you mention SQLExpress probably the most important factor is the max database size limit and this is a very good reason to go for the filesystem approach. Anyway, this research paper might still be interesting for you: To BLOB or Not To BLOB: Large Object Storage in a Database or a Filesystem?
但是当你提到SQLExpress时，最重要的因素可能是最大数据库大小限制，这是采用文件系统方法的一个很好的理由。无论如何，这篇研究论文对你来说可能仍然很有趣：对BLOB还是不对BLOB：数据库或文件系统中的大对象存储？
This paper used to be on the Microsoft Research site here: http://research.microsoft.com/apps/pubs/default.aspx?id=64525, but that link doesn't work for me. SQL Server has come a long way since then. Quassnoi already mentioned FILSTREAM, for example.
本文曾在此处的Microsoft Research网站上发布：http：//research.microsoft.com/apps/pubs/default.aspx？id = 64525，但该链接对我不起作用。从那时起，SQL Server已经走过了漫长的道路。例如，Quassnoi已经提到了FILSTREAM。
#1
6
The disadvantage of storing as binary is that you blow the database size to incredible sizes. If you were to use an express edition of SQL Server, which is limited to 4GB per database, you photo gallery would "finish" quite soon.
存储为二进制文件的缺点是您将数据库大小吹成令人难以置信的大小。如果您使用SQL Server的快速版本（每个数据库限制为4GB），您的照片库很快就会“完成”。
The advantage is that you can easily manipulate the access restrictions per file and per user. You just look at user rights and decide whether you serve back the image or not.
优点是您可以轻松地操纵每个文件和每个用户的访问限制。您只需查看用户权限并决定是否回送图像。
#2
18
SQL Server 2008 supports FILESTREAM storage.
SQL Server 2008支持FILESTREAM存储。
The files are stored on an NTFS volume like plain files, but are subject to transaction control and can be accessed via special file names passed to Win32 API functions (and of course any API built upon it) with additional SQL Server security checks (like GRANT options etc).
这些文件存储在NTFS卷上，就像普通文件一样，但是受事务控制的影响，可以通过传递给Win32 API函数的特殊文件名（当然还有任何构建在它上面的API）和其他SQL Server安全检查（如GRANT）进行访问。选项等）。
#3
4
File system storage will offer much better performance in saving and serving images, and is supported in every platform. If you can live without the security and transactional goodies you get from db storage, then I would go with the file system.
文件系统存储将在保存和提供图像方面提供更好的性能，并且在每个平台都受支持。如果你没有从数据库存储中获得的安全性和事务性好东西，那么我会使用文件系统。
#4
2
We had large LOB application that provided Bank tellers identification information about the member standing in front of them. Our textual data was stored in SQL Server. Image data was stored in files. The database field simply had a filename. This approach works well if you are behind the firewall. Reading and writing files is easy. The trouble is the file management. You should secure the file system so that random people cannot view the directory. Also, backups are more complicated with loose image files. You have to backup the database and the image files. The fields can reference paths that no longer exist. For example, some IT dude decides to move the image folder and now all the references in the db are broken. If your application needs to pass information through the firewall, I would suggest storing images in the SQL Server using the mentioned FileStream storage.
我们有大型LOB应用程序，为银行出纳员提供有关站在他们面前的成员的识别信息。我们的文本数据存储在SQL Server中。图像数据存储在文件中。数据库字段只有一个文件名。如果您在防火墙后面，这种方法很有效。读取和写入文件很容易。问题是文件管理。您应该保护文件系统，以便随机人员无法查看该目录。此外，对于松散的图像文件，备份更复杂。您必须备份数据库和映像文件。这些字段可以引用不再存在的路径。例如，某些IT人员决定移动图像文件夹，现在数据库中的所有引用都被破坏了。如果您的应用程序需要通过防火墙传递信息，我建议使用提到的FileStream存储将图像存储在SQL Server中。
Storing the images in the database would have saved us some grief. We would have only had to backup the database, it would be more secure, the references would never break and we would not have had to jump through hoops to get files from network outside the firewall.
将图像存储在数据库中会让我们感到悲伤。我们只需要备份数据库，它会更安全，引用永远不会破坏，我们不必跳过箍来从防火墙外的网络获取文件。
#5
1
This debate has been going on in almost any SQL Server community for ages. there are good arguments for both sides and there is definitely not just one size fits all answer. It really depends on your individual situation and on many factors, such as number of users, avg. file size, update frequency, read/write ratio, disk-subsystem yadayadayada...
几年来，几乎所有SQL Server社区都在进行这场辩论。双方都有很好的论据，肯定不只是一个尺寸适合所有答案。这实际上取决于您的个人情况和许多因素，例如用户数量，平均值。文件大小，更新频率，读/写比率，磁盘子系统yadayadayada ...
But as you mention SQLExpress probably the most important factor is the max database size limit and this is a very good reason to go for the filesystem approach. Anyway, this research paper might still be interesting for you: To BLOB or Not To BLOB: Large Object Storage in a Database or a Filesystem?
但是当你提到SQLExpress时，最重要的因素可能是最大数据库大小限制，这是采用文件系统方法的一个很好的理由。无论如何，这篇研究论文对你来说可能仍然很有趣：对BLOB还是不对BLOB：数据库或文件系统中的大对象存储？
This paper used to be on the Microsoft Research site here: http://research.microsoft.com/apps/pubs/default.aspx?id=64525, but that link doesn't work for me. SQL Server has come a long way since then. Quassnoi already mentioned FILSTREAM, for example.
本文曾在此处的Microsoft Research网站上发布：http：//research.microsoft.com/apps/pubs/default.aspx？id = 64525，但该链接对我不起作用。从那时起，SQL Server已经走过了漫长的道路。例如，Quassnoi已经提到了FILSTREAM。