在数据库中存储不同图片的最佳方式是什么?[英] What's the best way to store different images in the database?

本文是小编为大家收集整理的关于在数据库中存储不同图片的最佳方式是什么?的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

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

我有一堆用户照片,我还有另外5套不同的照片(例如用户照片,但与用户照片没有连接).

是将所有照片存储在单个数据库表中并尝试从该表中引用它们的最佳选择,或者最好是为每组照片创建不同的表?

我可以从创建多个表中看到一个好处,这是删除主对象时删除照片的级联删除函数.

其他任何方面要考虑?

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

推荐答案

注意:此答案现在为ancient,我建议您上传您的 图像到亚马逊S3,Google云存储或Azure Blob存储 帐户并将ID存储在您的数据库中. How to model a Photo storage database仍然相关.

如何将大斑点存储在SQL Server

在SQL Server中存储大量二进制数据并不是一个很好的方法.它使您的数据库非常笨重,并且性能通常不出色.存储文件通常在文件系统上完成. SQL Server 2008具有FILESTREAM的开箱即用支持. Microsoft记录了使用 FileStream 的情况,如下所示

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

在您的情况下,我认为所有要点都是有效的.

在服务器上启用

启用服务器上的支持使用以下语句.

EXEC sp_configure filestream_access_level, 2
RECONFIGURE

配置数据库

获取链接到数据库创建

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

创建表

下一步是使用FileStream存储在数据库中获取您的数据:

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

要Filestream工作,您不仅需要表中字段上的FILESTREAM属性,还需要一个具有ROWGUIDCOL属性的字段.

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

如何建模照片存储数据库

使用用于存储图像的FileStream方法,表非常狭窄,这对性能有益,因为每个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)

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

实体关系图

  • 性能明智,狭窄的图像表非常好,因为它仅包含每个记录的几个字节.
  • 我们可以假设图像始终是图像集的成员,如果其中只有1个图像,则可以隐藏集合信息.
  • 我假设您想跟踪哪些用户是哪些组织的成员,因此我添加了一个表来链接它们(假设用户可以是多个组织的成员).
  • OrganisationMembers表上的主要键是用户ID作为第一字段,因为通常使用的用户比组织多得多,您可能想显示一个用户的成员比逆向更频繁.
  • .
  • 有机化成员中有机化的指数可以满足需要显示特定组织成员列表的查询.

参考:

其他推荐答案

拥有不同表的唯一原因是您可以拥有FKS.但这是veruy,对于数据完整性非常重要.

有一个带有所有照片的表的原因是,如果您想对所有照片进行单个查询.

另一个原因可能是它使编写应用程序更容易(即,您不必更改单个照片表中的代码)

由于第二和第三个原因是不可能的,我建议您使用第一个选项.

其他推荐答案

当我有某种在几种情况下恢复的实体时,例如邮寄地址,我经常将它们全部收集到一张桌子中.这通常简化了验证(例如邮政编码),管理重复项,...

在适当的情况下,我将有一个交叉参考表.例如,电话号码可能位于一个表格中,并带有注释("家庭","移动",...).供应商和电话号码之间的交叉参考表可以与一个人的电话号码相匹配.它还提供了添加排名的机会,以便他们可以指定自己的首选电话号码.在某些情况下,您可能需要提示用户更新有关相关更改的信息,例如当您更新公司的800号码时,是否应更新其他引用?

无论如何,删除都需要检查对实体的任何未偿还引用.在大多数应用中,这不足以成为问题.我不是使用级联删除的忠实拥护者.我宁愿有一个存储程序,可以管理删除并处理任何"手动"级联以避免真正的惊喜.

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

本文地址:https://www.itbaoku.cn/post/597666.html

问题描述

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).

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.

推荐答案

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

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

  • Objects that are being stored are, on average, larger than 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.

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.

Inserting Data with TSQL

Now to insert data in this table you can use 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.

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

    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:

    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:

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.
  • 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.
  • 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.

References:

其他推荐答案

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

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.

其他推荐答案

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?

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.