在数据库中存储不同图像的最佳方法是什么？

What is the best way (regarding database design) for storing images for different purposes?

为不同目的存储图像的最佳方法（关于数据库设计）是什么？

I have a bunch of user photos and I got another 5 different sets of photos (like user photos but with no connection to user photos).

我有一堆用户照片，我有另外5套不同的照片（比如用户照片，但没有连接到用户照片）。

Is the best thing to store all photos in a single database table and try to reference them from within that table, or is the best to create different tables for each set of photos?

将所有照片存储在单个数据库表中并尝试从该表中引用它们是最好的，还是最好为每组照片创建不同的表？

I can see one benefit from creating multiple tables and that's the cascade delete function for removing the photo when the main object is deleted.

我可以看到创建多个表的一个好处，那就是删除主对象时删除照片的级联删除功能。

Any other aspects to consider?

还需要考虑其他方面吗？

Another example could be addresses. A user can have an address but so can a company or a location. Create one table for all addresses and try to have some sort of index tables to reference what address belongs to what object or have different tables and eliminate the problem.

另一个例子可能是地址。用户可以拥有地址，但公司或位置也可以。为所有地址创建一个表，并尝试使用某种索引表来引用哪个地址属于哪个对象或具有不同的表并消除问题。

5 个解决方案

#1

How to store large blobs in sql server

Storing large chunks of binary data in SQL Server is not a great approach. It makes your database very bulky to backup and performance is generally not great. Storing files is usually done on the file system. Sql Server 2008 has out of the box support for FILESTREAM. Microsoft documents the cases to use FileStream as follows

在SQL Server中存储大量二进制数据并不是一个好方法。它使您的数据库备份非常笨重，性能通常不是很好。存储文件通常在文件系统上完成。 Sql Server 2008具有开箱即用的FILESTREAM支持。 Microsoft将案例记录为使用FileStream，如下所示

• Objects that are being stored are, on average, larger than 1 MB.
• 存储的对象平均大于1 MB。
• Fast read access is important.
• 快速读取访问很重要。
• You are developing applications that use a middle tier for application logic.
• 您正在开发使用中间层应用程序逻辑的应用程序。

In your case I think all points are valid.

在你的情况下，我认为所有点都是有效的。

Enable on Server

To enable FILESTREAM support on the server use the following statement.

要在服务器上启用FILESTREAM支持，请使用以下语句。

EXEC sp_configure filestream_access_level, 2
RECONFIGURE

Configure the Database

To get a filestream filegroup linked to your database create

要获取链接到数据库的文件流文件组，请创建

ALTER DATABASE ImageDB ADD FILEGROUP ImageGroup CONTAINS FILESTREAM
ALTER DATABASE ImageDB 
  ADD FILE ( NAME = 'ImageStream', FILENAME = 'C:\Data\Images\ImageStream.ndf')
  TO FILEGROUP TodaysPhotoShoot

Creating the table

The next step is getting your data in the database with filestream storage:

下一步是使用文件流存储将数据存储到数据库中：

CREATE TABLE Images
(
    [Id] [uniqueidentifier] ROWGUIDCOL NOT NULL PRIMARY KEY, 
    [CreationDate] DATETIME NOT NULL,
    [ImageFile] VARBINARY(MAX) FILESTREAM NULL
)

For Filestream to work you not only need the FILESTREAM property on a field in the table, but also a field which has the ROWGUIDCOL property.

要使Filestream工作，您不仅需要表中字段的FILESTREAM属性，还需要具有ROWGUIDCOL属性的字段。

Inserting Data with TSQL

Now to insert data in this table you can use TSQL:

现在要在此表中插入数据，您可以使用TSQL：

using(var conn = new SqlConnection(connString))
using(var cmd = new SqlCommand("INSERT INTO Images VALUES (@id, @date, cast(@image as varbinary(max))", conn))
{
     cmd.Parameters.AddRange(new {
          new SqlParameter("id", SqlDbType.UniqueIdentifier).Value = uId,
          new SqlParameter("date", SqlDbType.DateTime).Value = creationDate,
          new SqlParameter("image", SqlDbType.varbinary).Value = imageFile,
      });
     conn.Open
     cmd.ExecuteScalar();
}

Inserting data using SqlFileStream

There also exists an approach to get the file data on disk using Win32 directly. This offers you streaming access SqlFileStream inherits from IO.Stream.

还存在一种使用Win32直接在磁盘上获取文件数据的方法。这为您提供了流式访问，SqlFileStream继承自IO.Stream。

Inserting data using win32 can be done with for example the code below:

使用win32插入数据可以使用以下代码完成：

    public void InsertImage(string connString, Guid uId, DateTime creationDate, byte[] fileContent)
    {
        using (var conn = new SqlConnection(connString))
        using (var cmd = new SqlCommand(@"INSERT INTO Images VALUES (@id, @date, cast(@image as varbinary(max)) output INSERTED.Image.PathName()" , conn))
        {
            conn.Open();

            using (var transaction = conn.BeginTransaction())
            {
                cmd.Transaction = transaction;
                cmd.Parameters.AddRange(
                    new[] {
                         new SqlParameter("id", SqlDbType.UniqueIdentifier).Value = uId,
                         new SqlParameter("date", SqlDbType.DateTime).Value = creationDate,
                         new SqlParameter("image", SqlDbType.VarBinary).Value = null
                        }
                    );

                var path = (string)cmd.ExecuteScalar();

                cmd.CommandText = "SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()";

                var context = (byte[])cmd.ExecuteScalar();

                using (var stream = new SqlFileStream(path, context, FileAccess.ReadWrite))
                {
                    stream.Write(fileContent, 0, fileContent.Length);
                }

                transaction.Commit();
            }
        }

---

How to model a Photo storage database

With the filestream approach to store the images the table is very narrow which is good for performance since many records can be stored per 8K data page. I would use the following model:

使用文件流方法来存储图像时，表格非常窄，这有利于提高性能，因为每个8K数据页面可以存储许多记录。我会使用以下模型：

    CREATE TABLE Images
    (
        Id uniqueidentifier ROWGUIDCOL NOT NULL PRIMARY KEY, 
        ImageSet INTEGER NOT NULL 
            REFERENCES ImageSets,
        ImageFile VARBINARY(MAX) FILESTREAM NULL
    )

    CREATE TABLE ImageSets
    (  
        ImageSet INTEGER NOT NULL PRIMARY KEY,
        SetName nvarchar(500) NOT NULL,
        Author INTEGER NOT NULL
            REFERENCES Users(USerId)
    )

   CREATE TABLE Users
   (
        UserId integer not null primary key,
        UserName nvarchar(500),
        AddressId integer not null
             REFERENCES Addresses
   )

   CREATE TABLE Organsations
   (
        OrganisationId integer not null primary key
        OrganisationName nvarchar(500),
        AddressId integer not null
             REFERENCES Addresses
   )

   CREATE TABLE Addresses
   (
       AddressId integer not null primary key,
       Type nvarchar(10), 
       Street nvarchar(500),
       ZipCode nvarchar(50),
       City nvarchar(500),
   )

   CREATE TABLE OrganisationMembers
   (
       OrganisationId integer not null
          REFERENCES Organisations,
       UserId integer not null
          REFERENCES Users,
       PRIMARY KEY (UserId, OrganisationId)
   )
   CREATE NONCLUSTERED INDEX ixOrganisationMembers on OrganisationMembers(OrganisationId)

This translates to the following Entity RelationShip Diagram:

这转换为以下实体关系图：

• Performance wise, the narrow images table is very good as it contains only a few bytes of data per record.
• 性能方面，窄图像表非常好，因为它每个记录只包含几个字节的数据。
• We can assume that an image is always member of an Image Set, The Set information could be hidden if there is only 1 image in it.
• 我们可以假设图像始终是图像集的成员。如果图像集中只有1个图像，则可以隐藏集合信息。
• I assume you want to track which users are member of which organisations, so I added a table to link them (Assuming a user can be member of multiple organisations).
• 我假设你想跟踪哪些用户是哪个组织的成员，所以我添加了一个表来链接它们（假设用户可以是多个组织的成员）。
• The primary key on the OrganisationMembers table has UserId as first field since there normally a lot more users than Organisations and you probably will want to show which organisations a user is member off more often than the inverse.
• OrganisationMembers表上的主键具有UserId作为第一个字段，因为通常比组织更多的用户，并且您可能希望显示用户成员的组织比反向更多。
• The index on OrganisationId in OrganisationMembers is there to cater for queries where the list of members for a specific Organisation needs to be shown.
• OrganisationMembers中的OrganisationId索引用于满足需要显示特定组织的成员列表的查询。

References:

参考文献：

• Enable and configure Filestream
• 启用并配置Filestream
• Create a filestream enabled Database
• 创建启用文件流的数据库
• Create a table for storing filestream data
• 创建一个用于存储文件流数据的表
• Managing Filestream with Transact SQL
• 使用Transact SQL管理文件流
• Create Client applications with Filestreams
• 使用文件流创建客户端应用程序

#2

The only reason to have different tables is that you can have FKs. But this is veruy,very importantfor data integrity.

拥有不同表格的唯一原因是您可以拥有FK。但这是非常重要的，对数据完整性非常重要。

One reason to have a single table with all the photos would be if you wanted to make a single query against all the photos.

如果您想对所有照片进行单一查询，那么拥有所有照片的单个表的一个原因就是如此。

Another reason could be that it makes writing your app easier (i.e because you don't have to change code which is working in a single photos table)

另一个原因可能是它使您的应用程序编写更容易（即因为您不必更改在单个照片表中工作的代码）

As the second and third reasons are quite improbable, I'd recommend you to use the first option.

由于第二和第三个原因很不可能，我建议你使用第一个选项。

#3

When I have some sort of entity that recurs in several contexts, e.g. a mailing address, I'll often gather them all in a single table. That generally simplifies validation (e.g. ZIP codes), managing duplicates, ... .

当我有某种实体在几种情况下重现时，例如邮寄地址，我经常将它们全部收集在一张桌子上。这通常简化了验证（例如邮政编码），管理重复，......

Where appropriate, I'll have a cross reference table. For example, telephone numbers might reside in one table along with a note ("home", "mobile", ...). A cross reference table between vendors and telephone numbers can match up one individual with as many telephone numbers as they need. It also provides an opportunity to add a rank so that they can specify their preferred telephone number. In some cases you may want to prompt a user updating information about related changes, e.g. when you update the 800 number for a company, should any other references to it be updated?

在适当的情况下，我会有一个交叉参考表。例如，电话号码可能与一个注释（“家庭”，“移动”，......）一起存在于一个表中。供应商和电话号码之间的交叉参考表可以将一个人与所需的电话号码相匹配。它还提供了添加排名的机会，以便他们可以指定他们的首选电话号码。在某些情况下，您可能希望提示用户更新有关相关更改的信息，例如当您更新公司的800号码时，是否应更新对其的任何其他引用？

In any event, deletion requires checking for any outstanding references to an entity. In most applications this doesn't happen frequently enough to be a problem. I'm not a big fan of using cascading deletion. I'd much rather have a stored procedure that manages deletions and handle any cascading "by hand" to avoid really big surprises.

无论如何，删除需要检查对实体的任何未完成的引用。在大多数应用程序中，这并不是经常发生的问题。我不是使用级联删除的忠实粉丝。我宁愿有一个存储过程来管理删除并处理任何“手动”级联，以避免真正的重大意外。

BLOBs are another discussion. Photographs, PDF documents and other bulky binaries have issues regarding database size, naming conventions, backup/restore, ... . These vary somewhat depending on the specific version of SQL Server being employed.

BLOB是另一个讨论。照片，PDF文档和其他庞大的二进制文件在数据库大小，命名约定，备份/恢复等方面存在问题。这些有所不同，具体取决于所使用的SQL Server的特定版本。

#4

Retrieving a row from a table that contains any sort of large data takes time. Images tend to be very big these days, and if I were to design a database that stores images or other large files within its structure then I would:

从包含任何类型的大数据的表中检索行需要时间。图像往往是非常大的，如果我要设计一个数据库，在其结构中存储图像或其他大型文件，那么我会：

1. attempt to spread the images over a number of tables, especially if you intend to display thumbnails of the images which would be considerably quicker to retrieve than full-size images.
2. 尝试将图像分布在多个表格上，特别是如果您打算显示图像的缩略图，这些缩略图比全尺寸图像要快得多。
3. the image tables should be independant of related data eg. alt text, name, description or labels. The only data I would have with the image is the primary-key and the doctype eg. jpg, jpeg, png, gif, bmp, etc.
4. 图像表应该与相关数据无关，例如。替代文字，名称，描述或标签。我对图像的唯一数据是主键和doctype，例如。 jpg，jpeg，png，gif，bmp等
5. avoid using linq's where function. Instead structure the sql query yourself, as for reasons I have not yet figured out, the where function is far slower than writing an sql query that does the same thing. Not in all cases though, but if you do use linq and while debugging, you discover that the where method takes a long time to finish then definitely write your own sql query.
6. 避免使用linq的where函数。而是自己构建sql查询，因为我还没有想到的原因，where函数远比编写执行相同操作的sql查询慢得多。但并非在所有情况下，但是如果您使用linq并在调试时，您发现where方法需要很长时间才能完成，那么一定要编写自己的sql查询。
7. Try to enforce that uploaded photos are either cropped to a fixed ratio, or even shrunk to a standard size. It might not be necessary depending on your purposes, but in my experience, it saves a lot of pain when it comes to displaying a collectionOfImage in a grid or list.
8. 尝试强制将上传的照片裁剪为固定比例，甚至缩小到标准尺寸。根据您的目的，可能没有必要，但根据我的经验，当在网格或列表中显示collectionOfImage时，它会节省很多痛苦。

#5

FileStream is ok, as discussed above. But it's complicated. You know what's best to store a file? The file system. That's what it does. You just need to set up a share that all your web servers can write to, and your save process is 1) generate an image id, 2) save the file using that as a name, 3) insert a row specifying the file share network path or a url to the file. Then your db table stays small and fast and your client can pull the file from the file system. It is cheaper, faster, more reliable to set up a terabyte file server with RAID on SSDs to store your files and just store the access path in the database server. BLOBs have weird effects in sql server, like not relinquishing their space once deleted, and lots of other issue (can't rebuild clustered index online, etc).

FileStream没问题，如上所述。但它很复杂。你知道什么是最好存储文件？文件系统。这就是它的作用。您只需要设置所有Web服务器都可以写入的共享，并且您的保存过程是1）生成图像ID，2）使用该名称保存文件，3）插入指定文件共享网络的行路径或文件的URL。然后，您的数据库表保持小而快，客户端可以从文件系统中提取文件。在SSD上设置带有RAID的TB级文件服务器来存储文件并将访问路径存储在数据库服务器中更便宜，更快速，更可靠。 BLOB在sql server中有奇怪的效果，比如一旦删除就不会放弃它们的空间，还有很多其他问题（无法在线重建聚簇索引等）。