如何在 MySQL 中存储数组？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17371639/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to store arrays in MySQL?
提问by tonga
I have two tables in MySQL. Table Person has the following columns:
我在 MySQL 中有两个表。表 Person 具有以下列：
id | name | fruits
The fruitscolumn may hold null or an array of strings like ('apple', 'orange', 'banana'), or ('strawberry'), etc. The second table is Table Fruit and has the following three columns:
该fruits列可能包含 null 或字符串数组，如 ('apple', 'orange', 'banana') 或 ('strawberry') 等。第二个表是 Table Fruit，具有以下三列：
____________________________
fruit_name | color | price
____________________________
apple | red | 2
____________________________
orange | orange | 3
____________________________
...,...
So how should I design the fruitscolumn in the first table so that it can hold array of strings that take values from the fruit_namecolumn in the second table? Since there is no array data type in MySQL, how should I do it?
那么我应该如何设计fruits第一个表中的列，以便它可以保存从fruit_name第二个表中的列中获取值的字符串数组？既然MySQL中没有数组数据类型，应该怎么做呢？
回答by Bad Wolf
The proper way to do this is to use multiple tables and JOINthem in your queries.
正确的方法是JOIN在查询中使用多个表和它们。
For example:
例如：
CREATE TABLE person (
`id` INT NOT NULL PRIMARY KEY,
`name` VARCHAR(50)
);
CREATE TABLE fruits (
`fruit_name` VARCHAR(20) NOT NULL PRIMARY KEY,
`color` VARCHAR(20),
`price` INT
);
CREATE TABLE person_fruit (
`person_id` INT NOT NULL,
`fruit_name` VARCHAR(20) NOT NULL,
PRIMARY KEY(`person_id`, `fruit_name`)
);
The person_fruittable contains one row for each fruit a person is associated with and effectively links the personand fruitstables together, I.E.
该person_fruit表包含一个人所关联的每个水果的一行，person并将fruits表和表有效地链接在一起，IE
1 | "banana"
1 | "apple"
1 | "orange"
2 | "straberry"
2 | "banana"
2 | "apple"
When you want to retrieve a person and all of their fruit you can do something like this:
当您想检索一个人及其所有水果时，您可以执行以下操作：
SELECT p.*, f.*
FROM person p
INNER JOIN person_fruit pf
ON pf.person_id = p.id
INNER JOIN fruits f
ON f.fruit_name = pf.fruit_name
回答by Janus Troelsen
The reason that there are no arrays in SQL, is because most people don't really need it. Relational databases (SQL is exactly that) work using relations, and most of the time, it is best if you assign one row of a table to each "bit of information". For example, where you may think "I'd like a list of stuff here", instead make a new table, linking the row in one table with the row in another table.[1] That way, you can represent M:N relationships. Another advantage is that those links will not clutter the row containing the linked item. And the database can index those rows. Arrays typically aren't indexed.
SQL 中没有数组的原因是因为大多数人并不真正需要它。关系数据库（SQL 就是这样）使用关系工作，并且大多数情况下，最好为每个“信息位”分配表的一行。例如，您可能会认为“我想要这里的内容列表”，而是创建一个新表，将一个表中的行与另一个表中的行链接起来。 [1] 这样，您就可以表示 M:N 关系。另一个优点是这些链接不会使包含链接项目的行变得混乱。数据库可以索引这些行。数组通常没有索引。
If you don't need relational databases, you can use e.g. a key-value store.
如果您不需要关系数据库，您可以使用例如键值存储。
Read about database normalization, please. The golden rule is "[Every] non-key [attribute] must provide a fact about the key, the whole key, and nothing but the key.". An array does too much. It has multiple facts and it stores the order (which is not related to the relation itself). And the performance is poor (see above).
请阅读有关数据库规范化的信息。黄金法则是“[每个]非键[属性]必须提供一个关于键的事实，整个键，除了键什么都没有。”。数组做的太多了。它有多个事实并存储顺序（与关系本身无关）。并且性能很差（见上文）。
Imagine that you have a person table and you have a table with phone calls by people. Now you could make each person row have a list of his phone calls. But every person has many other relationships to many other things. Does that mean my person table should contain an array for every single thing he is connected to? No, that is not an attribute of the person itself.
想象一下，您有一个人员表，并且您有一张表，上面放着人们的电话。现在，您可以让每个人行都有他的电话列表。但是每个人都与许多其他事物有许多其他关系。这是否意味着我的 person 表应该为他所连接的每一个事物都包含一个数组？不，这不是人本身的属性。
[1]: It is okay if the linking table only has two columns (the primary keys from each table)! If the relationship itself has additional attributes though, they should be represented in this table as columns.
[1]：如果链接表只有两列（每个表的主键）也可以！但是，如果关系本身具有其他属性，则它们应在此表中表示为列。
回答by Charles Addis
MySQL 5.7 now provides a JSON data type. This new datatype provides a convenient new way to store complex data: lists, dictionaries, etc.
MySQL 5.7 现在提供了JSON 数据类型。这种新数据类型提供了一种方便的新方法来存储复杂数据：列表、字典等。
That said, rrays don't map well databases which is why object-relational maps can be quite complex. Historically people have stored lists/arrays in MySQL by creating a table that describes them and adding each value as its own record. The table may have only 2 or 3 columns, or it may contain many more. How you store this type of data really depends on characteristics of the data.
也就是说，rray 不能很好地映射数据库，这就是对象关系映射可能非常复杂的原因。历史上，人们通过创建一个描述列表/数组的表并将每个值添加为自己的记录来在 MySQL 中存储列表/数组。该表可能只有 2 或 3 列，也可能包含更多列。您如何存储此类数据实际上取决于数据的特征。
For example, does the list contain a static or dynamic number of entries? Will the list stay small, or is it expected to grow to millions of records? Will there be lots of reads on this table? Lots of writes? Lots of updates? These are all factors that need to be considered when deciding how to store collections of data.
例如，列表是否包含静态或动态数量的条目？该列表会保持很小，还是预计会增长到数百万条记录？这张桌子上会有很多阅读吗？多写？很多更新？在决定如何存储数据集合时，这些都是需要考虑的因素。
Also, Key:Value data stores / Document stores such as Cassandra, MongoDB, Redis etc provide a good solution as well. Just be aware of where the data is actually being stored (if its being stored on disk or in memory). Not all of your data needs to be in the same database. Some data does not map well to a relational database and you may have reasons for storing it elsewhere, or you may want to use an in-memory key:value database as a hot-cache for data stored on disk somewhere or as an ephemeral storage for things like sessions.
此外，Cassandra、MongoDB、Redis 等 Key:Value 数据存储/文档存储也提供了一个很好的解决方案。只需注意数据实际存储的位置（如果它存储在磁盘或内存中）。并非所有数据都需要在同一个数据库中。某些数据不能很好地映射到关系数据库，您可能有理由将其存储在其他地方，或者您可能希望使用内存键：值数据库作为存储在磁盘某处的数据的热缓存或临时存储对于诸如会话之类的事情。
回答by Eric Grotke
A sidenote to consider, you can store arrays in Postgres.
要考虑的旁注，您可以在 Postgres 中存储数组。
回答by drew
In MySQL, use the JSON type.
在 MySQL 中，使用 JSON 类型。
Contra the answers above, the SQL standard has included array types for almost twenty years; they are useful, even if MySQL has not implemented them.
与上面的答案相反，SQL 标准已经包含数组类型将近 20 年了；它们很有用，即使 MySQL 没有实现它们。
In your example, however, you'll likely want to create three tables: person and fruit, then person_fruit to join them.
但是，在您的示例中，您可能希望创建三个表：person 和fruit，然后使用person_fruit 来加入它们。
DROP TABLE IF EXISTS person_fruit;
DROP TABLE IF EXISTS person;
DROP TABLE IF EXISTS fruit;
CREATE TABLE person (
person_id INT NOT NULL AUTO_INCREMENT,
person_name VARCHAR(1000) NOT NULL,
PRIMARY KEY (person_id)
);
CREATE TABLE fruit (
fruit_id INT NOT NULL AUTO_INCREMENT,
fruit_name VARCHAR(1000) NOT NULL,
fruit_color VARCHAR(1000) NOT NULL,
fruit_price INT NOT NULL,
PRIMARY KEY (fruit_id)
);
CREATE TABLE person_fruit (
pf_id INT NOT NULL AUTO_INCREMENT,
pf_person INT NOT NULL,
pf_fruit INT NOT NULL,
PRIMARY KEY (pf_id),
FOREIGN KEY (pf_person) REFERENCES person (person_id),
FOREIGN KEY (pf_fruit) REFERENCES fruit (fruit_id)
);
INSERT INTO person (person_name)
VALUES
('John'),
('Mary'),
('John'); -- again
INSERT INTO fruit (fruit_name, fruit_color, fruit_price)
VALUES
('apple', 'red', 1),
('orange', 'orange', 2),
('pineapple', 'yellow', 3);
INSERT INTO person_fruit (pf_person, pf_fruit)
VALUES
(1, 1),
(1, 2),
(2, 2),
(2, 3),
(3, 1),
(3, 2),
(3, 3);
If you wish to associate the person with an array of fruits, you can do so with a view:
如果您希望将这个人与一系列水果相关联，您可以使用一个视图来实现：
DROP VIEW IF EXISTS person_fruit_summary;
CREATE VIEW person_fruit_summary AS
SELECT
person_id AS pfs_person_id,
max(person_name) AS pfs_person_name,
cast(concat('[', group_concat(json_quote(fruit_name) ORDER BY fruit_name SEPARATOR ','), ']') as json) AS pfs_fruit_name_array
FROM
person
INNER JOIN person_fruit
ON person.person_id = person_fruit.pf_person
INNER JOIN fruit
ON person_fruit.pf_fruit = fruit.fruit_id
GROUP BY
person_id;
The view shows the following data:
该视图显示以下数据：
+---------------+-----------------+----------------------------------+
| pfs_person_id | pfs_person_name | pfs_fruit_name_array |
+---------------+-----------------+----------------------------------+
| 1 | John | ["apple", "orange"] |
| 2 | Mary | ["orange", "pineapple"] |
| 3 | John | ["apple", "orange", "pineapple"] |
+---------------+-----------------+----------------------------------+
In 5.7.22, you'll want to use JSON_ARRAYAGG, rather than hack the array together from a string.
在 5.7.22 中，您需要使用JSON_ARRAYAGG，而不是从字符串中将数组组合在一起。
回答by webdevfreak
Use database field type BLOB to store arrays.
使用数据库字段类型 BLOB 来存储数组。
Ref: http://us.php.net/manual/en/function.serialize.php
参考：http: //us.php.net/manual/en/function.serialize.php
Return Values
Returns a string containing a byte-stream representation of value that can be stored anywhere.
Note that this is a binary string which may include null bytes, and needs to be stored and handled as such. For example, serialize() output should generally be stored in a BLOB field in a database, rather than a CHAR or TEXT field.
返回值
返回一个字符串，其中包含可以存储在任何地方的值的字节流表示。
请注意，这是一个可能包含空字节的二进制字符串，需要按原样进行存储和处理。例如，serialize() 输出通常应存储在数据库中的 BLOB 字段中，而不是 CHAR 或 TEXT 字段中。
回答by echo_Me
you can store your array using group_Concat like that
您可以像这样使用 group_Concat 存储您的数组
INSERT into Table1 (fruits) (SELECT GROUP_CONCAT(fruit_name) from table2)
WHERE ..... //your clause here
HERE an example in fiddle
