如何在 MySQL 中存储数组?
我在 MySQL 中有两个表.表 Person 具有以下列:
I have two tables in MySQL. Table Person has the following columns:
| id | 姓名 | 水果 |
|---|
fruits 列可能包含 null 或字符串数组，例如 ('apple', 'orange', 'banana') 或 ('strawberry') 等.第二个表是 TableFruit 并有以下三列:
The fruits column 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:
| fruit_name | 颜色 | 价格 |
|---|---|---|
| 苹果 | 红色 | 2 |
| 橙色 | 橙色 | 3 |
| ----------- | -------- | ------ |
那么我应该如何设计第一个表中的 fruits 列，以便它可以保存从第二个表中的 fruit_name 列中获取值的字符串数组?既然MySQL中没有数组数据类型，我该怎么做?
So how should I design the fruits column in the first table so that it can hold array of strings that take values from the fruit_name column in the second table? Since there is no array data type in MySQL, how should I do it?
推荐答案
正确的做法是使用多个表并在查询中JOIN它们.
The proper way to do this is to use multiple tables and JOIN them in your queries.
例如:
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`)
);
person_fruit 表包含与一个人相关联的每个水果的一行，并将 person 和 fruits 表有效地链接在一起，IE
The person_fruit table contains one row for each fruit a person is associated with and effectively links the person and fruits tables together, I.E.
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
相关文章