在数据库中存储字典或键值对

本文关键字：字符串 键值对 int 数据库 存储 字典 | 更新日期: 2023-09-27 18:30:23

我想看看其他人在使用List<>或Dictionary等类型时经历了什么<>并反过来存储和检索这些数据？

下面是一个示例场景：用户将创建自己的"模板"，其中这些模板本质上是字典的集合，例如，对于user1，值为（1，帐户），（2，银行），（3，代码），（4，储蓄），对于user2，值（不相关）可以是（1，名称），（2，等级），（3，类）等。这些模板/列表的长度可能不同，但它们始终具有索引和值。此外，每个列表/模板将有一个且只有一个用户链接到它。

您在数据库端选择了哪些类型？

以及我应该注意的痛点和/或建议？

在数据库中存储字典<int，字符串>或键值对

就集合中的类型而言，.Net 类型和 SQL 类型之间存在相当 1 对 1 的映射：SQL Server 数据类型映射。您最需要担心的是字符串字段：

  • 它们是否始终是 ASCII 值 （0 - 255）？然后使用 VARCHAR .如果它们可能包含非 ASCII/UCS-2 字符，请使用 NVARCHAR 。
  • 它们可能的最大长度是多少？

当然，有时您可能希望在数据库中使用略有不同的数值类型。主要原因是如果在应用程序端选择了int，因为它比Int16和byte"更容易"（或者我被告知）处理，但值永远不会超过 32，767 或 255，那么您很可能分别使用 SMALLINT 或 TINYINT。int 和 byte 在应用程序层内存方面的差异可能很小，但它确实对物理存储有影响，尤其是在行计数增加时。如果不清楚这一点，"影响"意味着减慢查询速度，有时当您需要购买更多 SAN 空间时会花费更多资金。但是，我说"最有可能使用SMALLINT或TINYINT"的原因是，如果您拥有企业版并启用了行压缩或页面压缩，那么这些值将存储在它们适合的最小数据类型中。

至于从数据库中检索数据，这只是一个简单的SELECT。

就存储数据而言（至少在有效方面），嗯，这是更有趣的:)。将字段列表传输到 SQL Server 的一种好方法是使用表值参数 （TVP）。这些是在 SQL Server 2008 中引入的。我在此答案中发布了一个代码示例（C# 和 T-SQL），内容涉及一个非常相似的问题：将字典<字符串，int>传递给存储过程 T-SQL。关于这个问题还有另一个 TVP 示例（公认的答案），但它没有使用 IEnumerable<SqlDataRecord> ，而是使用一个DataTable，这是集合的不必要的副本。

编辑：关于指定实际持久数据的问题的最新更新，应存储在类似于以下内容的表中：

UserID INT NOT NULL,
TemplateIndex INT NOT NULL,
TemplateValue VARCHAR(100) NOT NULL

主键应该是（UserID，TemplateIndex），因为这是一个唯一的组合。不需要（至少不需要给定的信息）标识字段。

TemplateIndex和TemplateValue字段将在 TVP 中传递，如我对上面链接的问题的回答所示。UserID将作为第二SqlParameter自行发送。在存储过程中，您将执行类似于以下内容的操作：

INSERT INTO SchemaName.TableName (UserID, TemplateIndex, TemplateName)
    SELECT  @UserID,
            tmp.TemplateIndex,
            tmp.TemplateName
    FROM    @ImportTable tmp;

只是为了明确说明它，除非有一个非常具体的原因这样做（这需要包括永远不需要在任何查询中使用这些数据，这样这些数据实际上只是一个文档，在查询中没有比PDF或图像更有用），那么你不应该把它序列化为任何格式。不过，如果你倾向于这样做，XML是比JSON更好的选择，至少对于SQL Server来说是这样，因为有内置的支持与SQL Server中的XML数据交互，但对JSON则没有那么多。

List 或任何集合在数据库中的表示形式都应该是表。始终将其视为集合，并将其与数据库提供的内容相关联。

虽然你总是

可以序列化一个集合，但我不建议这样做，因为更新或插入记录，你总是更新整个记录或数据，而有一个表，你只需要查询 KEY，其中字典，你已经有了它。

相关文章：