数据库表中的键值对

Question

我需要在我的数据库中设计一个键/值表，并寻求如何最好地完成此操作的指导。基本上，我需要能够将值与一组命名属性关联起来，并将它们应用于外部键。

我需要支持的操作是：

- 将键/值对应用于一组项目 - 枚举所有当前活动的键 - 确定所有具有给定键值的项目 - 确定所有值与给定键关联且符合某些条件的项目。

似乎最简单的方法是定义一个表：

CREATE TABLE KeyValue (
  id    int,
  Key   varchar...,
  Value varchar...
);

看起来我可能会在Key列中重复许多数据，因为任何给定的键都可能针对大量文档进行定义。将Key varchar替换为另一个表中的整数查找似乎可以缓解这个问题（并使枚举所有活动键变得更加高效），但是这样会导致我维护该查找表的问题（每当我想要定义属性时就更新它，同时可能删除任何时候清除键/值的条目）。

这个问题有什么最好的解决方法呢？

- Andrew

Answer 1

你正在使用一个名为实体属性值模型的数据库模型。 这是在关系数据库中存储键值对的常见方式，但它在数据库规范化和效率方面有一些弱点。

是的，你展示的表设计是最常用的方法。 在这个设计中，每个实体的每个属性都在你的KeyValue表中得到了一个独立的行。

将键/值对应用于一组项目：你需要为组中的每个项目添加一行。

INSERT INTO KeyValue (id, key, value) VALUES (101, 'color', 'green');
INSERT INTO KeyValue (id, key, value) VALUES (102, 'color', 'green');
INSERT INTO KeyValue (id, key, value) VALUES (103, 'color', 'green');

您还可以使用参数准备INSERT语句，并通过循环或其他方式运行一系列项ID。

枚举所有当前激活的键：

SELECT DISTINCT Key FROM KeyValue;

确定所有具有给定键的值的项目:

SELECT id FROM KeyValue WHERE Key = 'color';

确定所有值与给定键关联且符合某些条件的项目：

SELECT id FROM KeyValue WHERE Value = 'green';

实体属性值（Entity-Attribute-Value）存在以下问题：

• 没有办法确保所有项目的键名拼写相同。
• 没有办法使某些键对所有项目强制要求（即在传统表设计中的非空）。
• 所有键必须使用VARCHAR作为值；不能针对每个键存储不同的数据类型。
• 没有办法使用引用完整性；不能创建一个适用于某些键的值而不是其他键值的外键（FOREIGN KEY）。

基本上，实体属性值不是一种规范化的数据库设计。

- Bill Karwin

Answer 2

不必优化，除非必要。关键字的平均长度是多少？如果你采用天真的方法实现，这个表会不会太大而无法全部装入服务器的内存中？我建议先采用最简单的方式实现，测量性能，只有在性能成为问题时才重新实现。

如果性能是一个问题，那么使用整数关键字和单独的表可能是正确的方式（使用整数列进行连接通常比使用可变长度字符串列更快）。但是优化的第一条规则是先进行测量——确保你所谓的优化代码确实可以使事情运行更快。

- gavinandresen

---

+1 简单易用。除非性能影响明显且严重，否则始终选择最易于使用的选项，然后根据需要进行测试和优化。 - Rex M

Answer 3

一个值得探索的选项是，在将密钥插入表中之前，使用SHA1或MD5对其进行摘要处理。

这样可以去掉查找表，但您将无法通过迭代来访问密钥，因为它只单向运行。

- sambo99

---

如果你只取SHA的前四个字节，那么你可以使用“int”作为键类型。 - 700 Software

Answer 4

创建可更新视图！。此外，请查看示例。

- Learning

Answer 5

我觉得你可能有几个设计选择。

选择1：你在回答中暗示的两个表格设计。

Keys (
 id int not null auto_increment
 key string/int
)
values (
 id int not null auto_increment
 key_id int
 value string/varchar/int
)

选择2：也许正如sambo99所指出的那样，您可以修改这个：

keys (
 id int not null auto_increment
 key string/int
 hash_code int -- this would be computed by the inserting code, so that lookups would effectively have the id, and you can look them up directly
)

values (
 id int not null auto_increment -- this column might be nice since your hash_codes might colide, and this will make deletes/updates easier
 key_id int -- this column becomes optional
 hash_code int
 value string/varchar/int...
)

--

- Nathan Feger

---

SHA1碰撞的概率在现实中几乎为0，你需要非常邪恶才能引起碰撞。如果你非常担心，可以使用SHA256。 - Sam Saffron

我个人在DC++中见过哈希冲突（TTH），所以这并非不可能。 - erikkallen

Answer 6

键值对通常不是关系型数据库的最佳使用方式。关系型数据库的好处在于其约束、验证和结构。如果在表中使用通用的键值结构，则会失去使关系型数据库优秀的验证和约束。如果您想要键值对的灵活设计，最好选择像MongoDB或类似的NoSQL数据库。

键值对（例如NoSQL数据库）在底层数据是非结构化、不可预测或经常变化时效果最佳。如果您没有结构化数据，则关系型数据库将带来更多麻烦，因为您需要进行大量模式更改和/或跳过许多障碍以符合不断变化的结构。

KVP / JSON / NoSql很棒，因为更改数据结构不需要完全重构数据模型。向数据对象添加字段只是将其添加到数据中。另一方面，与关系型数据库相比，KVP / Nosql数据库中的约束和验证检查较少，因此您的数据可能会变得混乱。

关系数据模型具有性能和空间节省的优势。规范化的关系数据可以使理解和验证数据更容易，因为有表键关系和约束来帮助您。这将使您的应用程序在长期内更易于维护和支持。另一种方法是在您的代码中使用数据抽象层，例如Python的Django或SQL Alchemy，.NET的Entity Framework。这样，随着您的代码变化，您的数据库也会自动变化。

我见过的最糟糕的模式之一是试图两全其美。试图将键值对放入关系数据库通常是灾难的开始。我建议使用最适合您数据的技术。

- FistOfFury