Mysql，在另一个表的单列中存储多个值

Mysql, storing multiple value in single column from another table

提问by slier

Bear with me, im really bad at explaining thing and i dont even know an appropriate title for this problem
Ok guys i have this problem
I already have one table name meal

忍受我，我真的不擅长解释事情，我什至不知道这个问题的合适标题
好吧伙计们我有这个问题
我已经有了一个表名meal

+------+--------+-----------+---------+
|  id  |  name  | serving   |  price  |
+------+--------+-----------+---------+
|  1   | soup1  |  2 person |  12.50  |
+------+--------+-----------+---------+
|  2   | soup2  |  2 person |  15.50  |
+------+--------+-----------+---------+
|  3   | soup3  |  2 person |  23.00  |
+------+--------+-----------+---------+
|  4   | drink1 |  2 person |  4.50   |
+------+--------+-----------+---------+
|  5   | drink2 |  2 person |  3.50   |
+------+--------+-----------+---------+
|  6   | drink3 |  2 person |  5.50   |
+------+--------+-----------+---------+
|  7   | frui1  |  2 person |  3.00   |
+------+--------+-----------+---------+
|  8   | fruit2 |  2 person |  3.50   |
+------+--------+-----------+---------+
|  9   | fruit3 |  2 person |  4.50   |
+------+--------+-----------+---------+

Ok now i want to allow admin to create a combo meal from this mealtable
So that mean, a combo meal can have unlimited number amout of meal

好的，现在我想允许管理员从这张meal桌子创建一个组合餐
所以这意味着，组合餐可以有无限数量的餐

Currently im puzzle how to store/link combo meal to the meal I donw want to store something lke below

目前我很困惑如何将组合餐存储/链接到餐我不想在下面存储一些东西

+------+--------------+-----------+-----------+
|  id  |  combo_name  | serving   |  meal_id  |
+------+--------------+-----------+-----------+
|  1   |   combo1     |  2 person |   1,4,7,9 |
+------+--------------+-----------+-----------+
|  2   |   combo2     |  2 person |   2,5,8   |
+------+--------------+-----------+-----------+
|  4   |   combo3     |  2 person |   3,5,6,9 |
+------+--------------+-----------+-----------+

Look at the meal_idcolumn, i dont think that is a good way to store a data

看meal_id列，我不认为这是存储数据的好方法

回答by Quassnoi

Create a many-to-many link table:

创建多对多链接表：

combo_id    meal_id
1           1
1           4
1           7
1           9
2           2
2           5
2           8
3           3
3           5
3           6
3           9

To select all meals for a given combo:

要选择给定组合的所有餐点：

SELECT  m.*
FROM    combo_meal cm
JOIN    meal m
ON      m.id = cm.meal_id
WHERE   cm.combo_id = 1

回答by Pablo Santa Cruz

No. It's definitely not a good way to store data. You will be better with a combo_headertable and a combo_detailstable.

不。这绝对不是存储数据的好方法。一张combo_header桌子和一张combo_details桌子会更好。

combo_headerwill be something like:

combo_header将是这样的：

+------+--------------+-----------+
|  id  |  combo_name  | serving   |
+------+--------------+-----------+
|  1   |   combo1     |  2 person |
+------+--------------+-----------+
|  2   |   combo2     |  2 person |
+------+--------------+-----------+
|  4   |   combo3     |  2 person |
+------+--------------+-----------+

And then, combo_detailswill be something like:

然后，combo_details将是这样的：

+------+-----------+
|  id  |  meal_id  |
+------+-----------+
|  1   |  1        |
+------+-----------+
|  1   |  4        |
+------+-----------+
|  1   |  7        |
+------+-----------+
|  1   |  9        |
+------+-----------+
... / you get the idea!

By the way, by using multiple values in a single column you are violating first normal form of relational databases.

顺便说一句，通过在单个列中使用多个值，您违反了关系数据库的第一范式。

The way I'm proposing will let you answer queries like get all name of the meals of combo1very easy to resolve.

我提议的方式将让您回答查询，例如获取组合 1 的所有膳食名称，非常容易解决。

回答by Dercsár

This is called a many-to-many relationship between meals and combo. A meal can be listed in multiple combos and a combos can contain multiple meals. You will need a link table (instead of the combo.meal_id field) that contains all possible meal-combo pairs.

这被称为膳食和组合之间的多对多关系。一顿饭可以在多个组合中列出，一个组合可以包含多顿饭。您将需要一个包含所有可能的膳食组合对的链接表（而不是 combo.meal_id 字段）。

In the end, you will have three tables:

最后，您将拥有三个表：

1. meal (meal_id, serving, name)
2. combo (combo_id, serving, name)
3. meal_combo (autoid, meal_id, combo_id)

1. 膳食（meal_id，服务，名称）
2. 组合（combo_id，服务，名称）
3. 餐组合（autoid、meal_id、combo_id）

meal_combo.autoid is not strictly needed, it's just a general recommendation.

meal_combo.autoid 不是严格需要的，它只是一般建议。

To list a combo with all it's meals in it:

要列出包含所有餐点的组合：

SELECT meal.id, meal.name FROM comboINNER JOIN meal_combo ON meal_combo.combo_id = combo.id INNER JOIN meal ON meal.id = meal_combo.meal_id WHERE combo.id = 132

SELECT meal.id, meal.name FROM comboINNER JOIN meal_combo ON meal_combo.combo_id = combo.id INNER JOIN meal ON meal.id = meal_combo.meal_id WHERE combo.id = 132

Google for 'many-to-many relationship' or 'database link table' for details.

谷歌的“多对多关系”或“数据库链接表”的详细信息。