关系数据库中的键值对

有人有在数据库中存储键-值对的经验吗？

我一直用这种桌子:

CREATE TABLE key_value_pairs (
itemid           varchar(32) NOT NULL,
itemkey         varchar(32) NOT NULL,
itemvalue       varchar(32) NOT NULL,
CONSTRAINT ct_primarykey PRIMARY KEY(itemid,itemkey)
)

例如，下列行可能存在:

 itemid            itemkey        itemvalue
----------------  -------------  ------------
123               Colour         Red
123               Size           Medium
123               Fabric         Cotton

这种方案的问题在于提取数据所需的 SQL 语法非常复杂。 只创建一系列键/值列是否更好？

CREATE TABLE key_value_pairs (
itemid            varchar(32) NOT NULL,
itemkey1        varchar(32) NOT NULL,
itemvalue1      varchar(32) NOT NULL,
itemkey2        varchar(32) NOT NULL,
itemvalue2      varchar(32) NOT NULL,
. . .etc . . .
)

这将使查询更加容易和快速，但是缺乏第一种方法的可扩展性。 有什么建议吗？

120624 次浏览
小开

第一个方法很好。您可以创建一个 UDF 来提取所需的数据，然后调用它。

小开

根据经验，我发现某些键将被更广泛地使用或更频繁地查询。然后，我们通常会对设计进行轻微的反规范化，以便在主“ item”表中包含一个特定的字段。

例如，如果每个项目都有颜色，则可以将“颜色”列添加到项目表中。织物和大小可以使用较少，可以在键-值对表中保持分离。您甚至可以在键-值对表中保留颜色，但是复制 item 表中的数据以获得性能优势。

显然，这取决于数据以及需要键-值对的灵活程度。它还可能导致属性数据位置不一致。但是，反规范化确实极大地简化了查询并提高了它们的性能。

我通常只在性能出现问题时才考虑反规范化，而不仅仅是为了简化查询。

小开

第二张桌子严重不正常，我还是坚持第一种方法。

小开

如果可能的键非常少，那么我只将它们存储为列。但是如果可能的键集合很大，那么第一种方法就很好(而第二种方法是不可能的)。

或者每个项目只能有有限数量的键，但键可以是来自一个大集合的某个东西？

您还可以考虑使用对象关系映射器来简化查询。

小开

第一种方法更加灵活，但是要以您提到的成本为代价。

第二种方法永远不可行，相反，你会这样做(根据你的第一个例子)

create table item_config (item_id int, colour varchar, size varchar, fabric varchar)

当然，这只有在已知数据量并且不会发生很大变化的情况下才会起作用。

作为一般规则，任何要求更改表的 DDL 以执行正常工作的应用程序都应该三思而后行。

小开

我认为您做的是正确的，只要给定类型的项的键/值经常更改。
如果它们相当静态，那么简单地使条目表更宽更有意义。

我们使用一种类似(但更复杂)的方法，围绕键/值以及每个键允许的值类型的表使用大量逻辑。
这允许我们将项定义为一个键的另一个实例，并且我们的中心表将任意键类型映射到其他任意键类型。它可以迅速地把你的大脑绑成一个结，但是一旦你编写并封装了处理这一切的逻辑，你就有了很大的灵活性。

如果需要的话，我可以写更多的细节。

小开

我不明白为什么提取数据的 SQL 对于您的第一个设计来说应该是复杂的。当然，要获得一个项目的所有值，只需要这样做:

SELECT itemkey,itemvalue FROM key_value_pairs WHERE itemid='123';

或者如果你只是想要一个特定的关键为该项目:

SELECT itemvalue FROM key_value_pairs WHERE itemid='123' AND itemkey='Fabric';

第一种设计还提供了灵活性，可以随时方便地添加新键。

小开

如果键是动态的，或者有很多键，那么使用您的映射表作为第一个示例。除此之外，这是最通用的解决方案，当你添加更多的关键字时，它的扩展性最好，编写 SQL 语句来获取数据很容易，而且数据库能够比你想象的更好地优化查询(也就是说，我不会过早地优化这种情况，除非它被证明是以后测试的瓶颈，在这种情况下，你可以考虑下面的两个选项)。

如果键是一个已知的集合，并且数量不多(< 10，也许 < 5) ，那么我认为在项目中将它们作为值列没有问题。

如果有一个中等数量的已知固定键(10-30) ，那么可能有另一个表来保存 item _ Details。

然而，我从来没有看到需要使用您的第二个示例结构，它看起来很麻烦。

小开

还有一个介于两者之间的解决方案。可以对键和值使用 xml 类型列。因此，保留 itemid 字段，然后有一个 xml 字段，其中包含为某些键值对(如 <items> <item key="colour" value="red"/><item key="xxx" value="blah"/></items>)定义的 xml 然后，当您从数据库中提取数据时，您可以用许多不同的方式处理 xml。取决于你的用法。这是一个可扩展的解决方案。

小开

只要仍然可以满足业务需求，违反规范化规则是可以的。有 key_1, value_1, key_2, value_2, ... key_n, value_n可以是好的，直到你需要 key_n+1, value_n+1的点。

我的解决方案是共享属性的数据表和唯一属性的 XML 表。也就是说我两个都用。如果每样东西(或大多数东西)都有一个大小，那么 size 就是表中的一列。如果只有对象 A 具有属性 Z，则将 Z 存储为 XML，类似于 Peter Marshall 已经给出的答案。

小开

在大多数情况下，您会使用第一种方法，这是因为您还没有真正坐下来思考您的模型。“我们还不知道钥匙是什么”。一般来说，这是非常糟糕的设计。这比实际将键作为列要慢，实际上键应该是列。

我还想问为什么你的 ID 是 varchar。

在极少数情况下，您确实必须实现一个键/值表，第一种解决方案是可行的，但是，我通常希望将键放在一个单独的表中，这样就不需要将 varchars 作为键存储在键/值表中。

例如,

CREATE TABLE valid_keys (
id            NUMBER(10) NOT NULL,
description   varchar(32) NOT NULL,
CONSTRAINT pk_valid_keys PRIMARY KEY(id)
);


CREATE TABLE item_values (
item_id NUMBER(10) NOT NULL,
key_id  NUMBER(10) NOT NULL,
item_value VARCHAR2(32) NOT NULL,
CONSTRAINT pk_item_values PRIMARY KEY(item_id),
CONSTRAINT fk_item_values_iv FOREIGN KEY (key_id) REFERENCES valid_keys (id)
);

然后，您甚至可以疯狂地向键中添加一个“ TYPE”，从而允许进行一些类型检查。

小开
最佳答案

在您继续您的方法之前，我谨建议您退一步考虑是否真的希望将此数据存储在“键-值对”表中。我不知道你的应用程序，但我的经验表明，每次我已经做了你正在做的事情，以后我希望我已经创建了一个彩色表，一个布料表和大小表。

考虑一下参照完整性约束，如果你使用键-值对的方法，数据库不能告诉你什么时候你试图在一个大小字段中存储颜色 id

考虑一下在具有10个值的表上进行连接的性能好处，与跨多个域具有数千个值的通用值相比较。关键价值的索引究竟有多大用处？

通常，做你正在做的事情背后的原因是因为域需要是“用户可定义的”。如果是这样的话，那么即使是我也不会推动您动态地创建表(尽管这是一种可行的方法)。

但是，如果您的理由是因为您认为它比多个表更容易管理，或者因为您想象的是一个适用于所有域的通用维护用户界面，那么在继续之前请停下来认真考虑一下。

小开

如果你走 KVP 表的路线，我不得不说，我自己一点也不喜欢这种技术，因为它确实很难查询，那么你应该考虑使用适合你所在平台的适当技术，将一个条目 id 的值聚集在一起。

RDBMS 倾向于将行分散在各处以避免插入上的块争用，如果有8行要检索，那么很容易发现自己要访问表的8个块来读取它们。在 Oracle 上，最好考虑使用散列集群来存储这些数据，这将大大提高访问给定项 ID 的值的性能。

小开

您的示例不是使用键值对的很好的示例。一个更好的例子是在计费应用程序中使用诸如 Fee 表、 Customer 表和 Customer _ Fee 表之类的东西。费用表将包括以下字段: Fee _ id，fee _ name，fee _ description Customer _ Fee 表由以下字段组成: Customer _ id，fee _ id，fee _ value

小开

我认为设计这些表格的最佳方法如下:

  • 将常用字段作为数据库中的列。
  • 提供一个 Misc 列，其中包含一个字典(以 JSON/XML/other string forMeat 格式) ，其中将字段作为键-值对包含。

要点:

  • 在大多数情况下，您可以编写常规的 SQL 查询来查询 SQL。
  • 可以对键-值对执行 FullTextSearch。MySQL 有一个全文搜索引擎，否则你可以使用比较慢的“ like”查询。虽然全文搜索不好，但我们假设这样的查询较少，因此不会引起太多问题。
  • 如果您的键-值对是简单的布尔标志，那么这种技术与为键单独设置一列的功能相同。关于键值对的任何更复杂的操作都应该在数据库之外进行。
  • 查看一段时间内查询的频率将告诉您需要在列中转换哪些键-值对。
  • 这种技术还使得对数据库强制完整性约束变得容易。
  • 它为开发人员重构模式和代码提供了更自然的途径。
小开

我曾经在一个数据库中使用键-值对来创建一个电子表格(用于数据输入) ，在这个表格中，出纳员将从现金抽屉中汇总他的活动。每个 k/v 对代表一个指定的单元格，用户在其中输入货币金额。这种方法的主要原因是电子表格非常容易改变。新的产品和服务被定期添加(因此出现了新的细胞)。此外，某些单元格在某些情况下是不需要的，可以删除。

我编写的这个应用程序是对一个应用程序的重写，该应用程序确实将出纳员表分成了不同的部分，每个部分在不同的表中表示。这里的问题是，随着产品和服务的添加，需要对模式进行修改。正如所有的设计选择一样，与其他选择相比，选择某个方向有利也有弊。我的重新设计当然执行得更慢，更快地消耗磁盘空间; 但是，它非常敏捷，允许在几分钟内添加新的产品和服务。然而，唯一值得注意的问题是磁盘消耗; 我记得没有其他令人头疼的问题了。

如前所述，我通常考虑键-值对方法的原因是当用户(可能是业务所有者)希望创建具有特定于用户的一组属性的自己的类型时。在这种情况下，我下定决心。

如果不需要通过这些属性检索数据，或者一旦检索到数据块，搜索就可以推迟到应用程序，我建议将所有属性存储在一个文本字段中(使用 JSON、 YAML、 XML 等)。如果非常需要通过这些属性检索数据，那么就会出现混乱。

您可以创建一个单独的“ Attribute”表(id、 item _ id、 key、 value、 data _ type、 sort _ value) ，其中 sort 列将实际值转换为字符串排序表示形式。(例如 date: “2010-12-2512:00:00”，number: “000000001”)或者你可以根据数据类型创建单独的属性表(例如 string _ properties，date _ properties，number _ properties)。在这两种方法的众多优缺点中: 第一种更简单，第二种更快。这两种情况都会导致您编写难看、复杂的查询。

小开

时代变了。现在除了关系数据库之外，还可以使用其他数据库类型。NOSQL 的选择现在包括列存储、文档存储、图形和多模型(参见: http://en.wikipedia.org/wiki/NoSQL)。

对于键值数据库，您的选择包括(但不限于) CouchDB、 Redis 和 MongoDB。

小开

PostgreSQL 8.4支持 hstore 数据类型，用于在单个 PostgreSQL 数据字段中存储一组(键，值)对。 请参考 http://www.postgresql.org/docs/8.4/static/hstore.html的使用信息。虽然这是一个很古老的问题，但是我认为传递这些信息会对某人有所帮助。

小开

我一直在想同样的挑战这就是我想到的。 这个任务是一个关系表，我在其中存储公共属性:

CREATE TABLE `tasks` (
`task_id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`account_id` BIGINT(20) UNSIGNED NOT NULL,
`type` VARCHAR(128) COLLATE UTF8MB4_UNICODE_CI DEFAULT NULL,
`title` VARCHAR(256) COLLATE UTF8MB4_UNICODE_CI NOT NULL,
`description` TEXT COLLATE UTF8MB4_UNICODE_CI NOT NULL,
`priority` VARCHAR(40) COLLATE UTF8MB4_UNICODE_CI DEFAULT NULL,
`created_by` VARCHAR(40) COLLATE UTF8MB4_UNICODE_CI DEFAULT NULL,
`creation_date` TIMESTAMP NULL DEFAULT NULL,
`last_updated_by` VARCHAR(40) COLLATE UTF8MB4_UNICODE_CI DEFAULT NULL,
`last_updated_date` TIMESTAMP NULL DEFAULT NULL,
PRIMARY KEY (`task_id`),
KEY `tasks_fk_1` (`account_id`),
CONSTRAINT `tasks_fk_1` FOREIGN KEY (`account_id`)
REFERENCES `accounts` (`account_id`)
ON DELETE CASCADE ON UPDATE NO ACTION
)  ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=UTF8MB4 COLLATE = UTF8MB4_UNICODE_CI ROW_FORMAT=DYNAMIC;

这是存储额外任务信息的 KV 表。我更喜欢使用它们的类型来存储值，以便以适当的方式处理数据。欢迎评论。

CREATE TABLE `task_variables` (
`row_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`task_id` bigint(20) unsigned NOT NULL,
`name` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL,
`type` varchar(40) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`variable_text_value` varchar(256) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`variable_number_value` double DEFAULT NULL,
`variable_date_value` datetime DEFAULT NULL,
`created_by` varchar(40) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`creation_date` timestamp NULL DEFAULT NULL,
`last_updated_by` varchar(40) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`last_updated_date` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`row_id`),
KEY `task_variables_fk` (`task_id`),
CONSTRAINT `task_variables_fk` FOREIGN KEY (`task_id`) REFERENCES `tasks` (`task_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;

提交答案