Mysql，在另一个表的单列中存储多个值 [英] Mysql, storing multiple value in single column from another table

问题描述

与我一起，我真的很难解释这个问题，我甚至不知道这个问题的适当标题

好​​吧，我有这个问题

我已经有一个表名餐

  + ---- -  + -------- + ----------- + --------- + 
 | id |名称|服务|价格| 
 + ------ + -------- + ----------- + --------- + 
 | 1 |汤1 | 2人| 12.50 | 
 + ------ + -------- + ----------- + --------- + 
 | 2 |汤2 | 2人| 15.50 | 
 + ------ + -------- + ----------- + --------- + 
 | 3 |汤3 | 2人| 23.00 | 
 + ------ + -------- + ----------- + --------- + 
 | 4 | drink1 | 2人| 4.50 | 
 + ------ + -------- + ----------- + --------- + 
 | 5 | drink2 | 2个人| 3.50 | 
 + ------ + -------- + ----------- + --------- + 
 | 6 | drink3 | 2人| 5.50 | 
 + ------ + -------- + ----------- + --------- + 
 | 7 | frui1 | 2人| 3.00 | 
 + ------ + -------- + ----------- + --------- + 
 | 8 | fruit2 | 2个人| 3.50 | 
 + ------ + -------- + ----------- + --------- + 
 | 9 | fruit3 | 2人| 4.50 | 
 + ------ + -------- + ----------- + --------- + 
  

现在我想允许管理员从这个餐中创建一个组合餐。表

所以这意味着，一个组合餐可以有无限数量的膳食

目前我的难题如何存储/链接组合餐
我想要存储一些以下的东西

  + ------ + ------ -------- + ----------- + ----------- + 
 | id | combo_name |服务| meal_id | 
 + ------ + -------------- + ----------- + ----------- + 
 | 1 | combo1 | 2人| 1,4,7,9 | 
 + ------ + -------------- + ----------- + ----------- + 
 | 2 | combo2 | 2人| 2,5,8 | 
 + ------ + -------------- + ----------- + ----------- + 
 | 4 | combo3 | 2人| 3,5,6,9 | 
 + ------ + -------------- + ----------- + ----------- + 
  

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

解决方案

创建一个多对多链接表：

  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 
  / pre> 
 
 
为特定组合选择所有餐点：
  SELECT m。* 
 FROM combo_meal cm 
 JOIN餐m 
 ON m.id = cm.meal_id 
 WHERE cm.combo_id = 1 
  
 


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
解决方案
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


                        
这篇关于Mysql，在另一个表的单列中存储多个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！