如何在MySQL中存储键值对?

I am new to databases and MySQL in particular. Suppose I need to store flat key-value data structures in MySQL. Each data structure has a few required fields and a number of optional fields that are not known in advance and may change frequently.

我对数据库和MySQL特别陌生。假设我需要在MySQL中存储平面键值数据结构。每个数据结构都有一些必需的字段和一些预先不知道的可选字段，并且可能经常更改。

I will need to retrieve all data structures by one of the required fields and probably delete them.

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

So I'd like to store these data structures in a table like that: (just a copy-paste from the Internet rather than working code)

因此，我想将这些数据结构存储在这样的表中:(只是来自Internet的复制粘贴，而不是工作代码)

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)
)

Does this approach make sense ? How to define the primary key for the second table ?

这种方法有意义吗?如何定义第二个表的主键?

2 个解决方案

#1

For the second table, I would recommend:

对于第二张表，我建议:

adding an explicit auto incrementing primary key.
添加显式自动递增主键。
declare the length of the varchar
声明varchar的长度。
declare my_data_structure_id
声明my_data_structure_id
have a unique constraint
有一个独特的约束

The result is something like:

结果是这样的:

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)
);

I am guessing that the unique constraint is on the pair. However, if you just want one field of a given name, exclude the value from the unique constraint.

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

#2

I frequently warn about the hazards of EAV, but I don't say it's EVIL. It's just fundamentally not relational, so using a language like SQL that is designed to store and query relational data is always going to be awkward and inefficient.

我经常警告EAV的危害，但我不认为它是邪恶的。它根本不是关系语言，所以使用SQL这样的语言来存储和查询关系数据总是很尴尬和低效的。

Use EAV if there's no other option, but be warned that you're obligating yourself to more work when you use EAV. Your queries will be more complex, you lose the ability for the database server to enforce constraints, and so on.

如果没有其他选择，请使用EAV，但是要注意，在使用EAV时，你必须承担更多的工作。您的查询将更加复杂，您将失去数据库服务器执行约束的能力，等等。

An alternative is to use some type of non-relational database, like a document store, so you can insert a set of user-defined fields as needed.

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

MySQL provides the JSON data type, so you have a sort of hybrid mode where you can use conventional columns with SQL data types for attributes you always need, and then JSON for dynamic attributes.

MySQL提供了JSON数据类型，因此您有一种混合模式，您可以使用传统的列和SQL数据类型来为您一直需要的属性，然后为动态属性使用JSON。

#1