在数据库中存储不同图像的最好方法是什么？ [英] What's the best way to store different images in the database?
问题描述
为了不同的目的，存储图像的最佳方式是什么（关于数据库设计）？
我有一堆用户照片，照片（像用户照片，但没有连接到用户照片）。
将所有照片存储在单个数据库表中并尝试从其中引用它们是最好的或者最好为每组照片创建不同的表格？
我可以看到创建多个表格的一个好处，那就是删除照片的级联删除功能
另一个例子可能是地址。用户可以具有地址，但是公司或位置也可以。
为所有地址创建一个表，并尝试使用某种索引表来引用什么地址属于什么对象或具有不同的表，并消除问题。
如何在sql server中存储大的blob
在SQL Server中存储大量的二进制数据不是一个好方法。它使你的数据库非常庞大的备份和性能一般不是很大。存储文件通常在文件系统上进行。 Sql Server 2008具有开箱支持 FILESTREAM 。
Microsoft记录案例以使用 FileStream ，如下所示
- 正在存储的对象
- 快速读取访问很重要。
- 您正在开发使用中间层进行应用程序逻辑的应用程序。
在您的情况下，我认为所有点都有效。
在服务器上启用
启用 FILESTREAM 支持在服务器上使用以下语句。
EXEC sp_configure filestream_access_level，2
RECONFIGURE
配置数据库
获取链接到数据库的filestream文件组create
ALTER DATABASE ImageDB ADD FILEGROUP ImageGroup CONTAINS FILESTREAM
ALTER DATABASE ImageDB
ADD FILE（NAME ='ImageStream' ，FILENAME ='C：\Data\Images\ImageStream.ndf'）
到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新的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新的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 $ b new [] {
new SqlParameter（id，SqlDbType.UniqueIdentifier）.Value = uId，
new SqlParameter（date，SqlDbType.DateTime）.Value = creationDate，
新的SqlParameter（image，SqlDbType.VarBinary）.Value = null
}
）;
var path =（string）cmd.ExecuteScalar（）;
cmd.CommandText =SELECT GET_FILESTREAM_TRANSACTION_CONTEXT（）;
var context =（byte []）cmd.ExecuteScalar（）;
使用（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
$ b ImageSet INTEGER NOT NULL PRIMARY KEY，
SetName nvarchar（500）NOT NULL，
作者INTEGER NOT NULL
REFERENCES用户（USerId）
）
$ b b CREATE TABLE用户
（
UserId整数不为null主键，
UserName nvarchar（500），
AddressId整数不为null
REFERENCES地址
）
CREATE TABLE组织
（
OrganisationId整数不为null主键
OrganisationName nvarchar（500），
AddressId整数不为空
参考地址
CREATE TABLE地址
（
AddressId整数不为null主键，
类型nvarchar（10），
街道nvarchar ，
ZipCode nvarchar（50），
City nvarchar（500），
）
CREATE TABLE OrganizersMembers
（
OrganisationId integer not null
参考组织，
UserId整数不为空
参考用户，
PRIMARY KEY（UserId，OrganisationId）
）
创建非隔离索引ix组织成员）
这转换为以下实体关系图：
- 性能方面，窄图像表非常好，因为它每个记录只包含几个字节的数据。
- 我们可以假设图片始终是图片集的成员，如果只有一个图片，则可以隐藏图片集信息。
- 我假设你想跟踪哪些用户是哪些组织的成员，所以我添加了一个表来链接他们（假设用户可以是多个组织的成员）。
- 主键OrganizationsMembers表有UserId作为第一个字段，因为通常有比组织更多的用户，并且您可能想要显示哪个组织的用户是反向的。
- 索引组织中的组织成员组织成员可以满足特定组织的成员列表需要显示的查询。
参考资料：
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.
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:
- 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:
- 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
这篇关于在数据库中存储不同图像的最好方法是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
