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

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

忍受我，我真的很难解释的事情，我甚至不知道这个问题的合适的标题好的家伙我有这个问题我已经有一个表名餐

+------+--------+-----------+---------+
|  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 meal table
So that mean, a combo meal can have unlimited number amout of 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_id column, i dont think that is a good way to store a data

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

3 个解决方案

#1

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

#2

No. It's definitely not a good way to store data. You will be better with a combo_header table and a combo_details table.

不，这绝对不是存储数据的好方法。使用combo_header表和combo_details表会更好。

combo_header will be something like:

combo_header将是这样的：

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

And then, combo_details will 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 combo1 very easy to resolve.

我提出的方式将让你回答查询，比如让combo1的所有名字都很容易解决。

#3

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:

最后，您将有三个表：

meal (meal_id, serving, name)
餐（meal_id，服务，名称）
combo (combo_id, serving, name)
组合（combo_id，服务，名称）
meal_combo (autoid, meal_id, combo_id)
meal_combo（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.

Google提供“多对多关系”或“数据库链接表”以获取详细信息。

#1