如何在MySQL中存储键值对？

Question

我对数据库特别是MySQL是个新手。假设我需要在MySQL中存储平面键值数据结构。每个数据结构都有几个必填字段和多个可选字段，这些字段事先是未知的，可能会频繁更改。

我将需要通过一个必需的字段检索所有数据结构，并可能将其删除。

所以我想把这些数据结构存储在一个表中，就像这样：(只是从互联网上复制-粘贴，而不是工作代码)

CREATE TABLE my_data_structures (
   my_data_structure_id INT     NOT NULL,
   my_required_field1   VARCHAR NOT NULL,
   my_required_field2   INT     NOT NULL,
   PRIMARY KEY (my_data_structure_id)
)

CREATE TABLE my_optional_fields (
   my_optional_field_name  VARCHAR  NOT NULL,
   my_optional_field_value VARCHAR  NOT NULL,
   FOREIGN KEY (my_data_structure_id) REFERENCES my_data_structures(my_data_structure_id)
)

这种方法有意义吗？如何定义第二个表的primary key？

Answer 1

对于第二个表，我建议：

• 添加显式自动递增主键。约束声明varchar
• declare my_data_structure_id
• have of
• a unique constraint

结果类似于：

CREATE TABLE my_optional_fields (
   my_optional_fields_id int auto_increment primary key,
   my_data_structure_id int not null,
   my_optional_field_name VARCHAR(255)  NOT NULL,
   my_optional_field_value VARCHAR(255)  NOT NULL,
   FOREIGN KEY (my_data_structure_id) REFERENCES my_data_structures(my_data_structure_id)
   UNIQUE (my_data_structure_id, my_optional_field_name, my_optional_field_value)
);

我猜唯一的约束是在这对上。但是，如果您只需要一个给定名称的字段，请从unique约束中排除该值。

Answer 2

我经常警告EAV (实体-属性-值)的危害，但我不会说它是邪恶的。它根本不是关系型的，所以使用像SQL这样的语言来存储和查询关系型数据总是很笨拙和低效的。

如果没有其他选择，请使用EAV，但请注意，当您使用EAV时，您需要做更多的工作。您的查询将更加复杂，您将失去数据库服务器强制执行约束的能力，等等。

另一种方法是使用某种类型的非关系数据库，比如文档存储，这样您就可以根据需要插入一组用户定义的字段。

MySQL提供了JSON data type，因此您有了一种混合模式，您可以将传统列与SQL数据类型一起用于始终需要的属性，然后将JSON用于动态属性。