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

Question

为不同目的存储图像的最佳方式(关于数据库设计)是什么？

我有一堆用户照片，我还有另外 5 组不同的照片(类似用户照片但与用户照片没有关联)。

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

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

还有其他需要考虑的方面吗？

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

Answer 1

NOTE: this answer is now ancient and I recommend you upload yourimages to Amazon S3, Google Cloud Storage or Azure Blob storageaccounts and store the id in your database. The How to model a Photo storage database is still relevant.

如何在sql server中存储大的blob

在 SQL Server 中存储大块二进制数据并不是一个好的方法。它使您的数据库备份起来非常庞大，而且性能通常不是很好。存储文件通常是在文件系统上完成的。 Sql Server 2008 开箱即用地支持 FILESTREAM。Microsoft 记录使用 FileStream 的情况如下

• 存储的对象平均大于 1 MB。
• 快速阅读很重要。
• 您正在开发将中间层用于应用程序逻辑的应用程序。

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

在服务器上启用

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

EXEC sp_configure filestream_access_level, 2
RECONFIGURE

配置数据库

获取链接到您的数据库的文件流文件组创建

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

创建表

下一步是使用文件流存储将数据放入数据库中:

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

要使 Filestream 正常工作，您不仅需要表中某个字段的 FILESTREAM 属性，还需要一个具有 ROWGUIDCOL 的字段属性(property)。

使用 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();
}

使用SqlFileStream插入数据

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

使用 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();
            }
        }

---

如何为照片存储数据库建模

使用文件流方法存储图像时，表非常窄，这对性能有好处，因为每个 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)

这转化为以下实体关系图:

• 就性能而言，窄图像表非常好，因为它每条记录仅包含几个字节的数据。
• 我们可以假设图像始终是图像集的成员，如果其中只有一张图像，则可以隐藏图像集信息。
• 我假设您想跟踪哪些用户是哪些组织的成员，因此我添加了一个表格来链接它们(假设一个用户可以是多个组织的成员)。
• OrganisationMembers 表的主键将 UserId 作为第一个字段，因为通常用户比 Organizations 多得多，您可能希望更频繁地显示用户是哪些组织的成员，而不是相反。
• OrganisationMembers 中的 OrganisationId 索引用于满足需要显示特定组织的成员列表的查询。

引用资料:

• Enable and configure Filestream
• Create a filestream enabled Database
• Create a table for storing filestream data
• Managing Filestream with Transact SQL
• Create Client applications with Filestreams