MySQL通过一个字段加入多条记录

Question

我正在使用这种情况，它是关于连接两个表并返回多条记录。

让我们假设我有两个表是这样的：

表产品：

+----+-----------+-----------+ 
| id | name | media_id | 
+----+-----------+-----------+ 
| 1 + product 1 + 32,33,34 + 
+----+-----------+-----------+ 
media_id(VARCHAR(50)) 

表媒体：

+----+-----------+------------------------------------------+ 
| id | name |     path     + 
+----+-----------+------------------------------------------+ 
| 31 + media 1 +  localhost://uploads/image/image_1 + 
+----+-----------+------------------------------------------+ 
| 32 + media 2 +  localhost://uploads/image/image_2 + 
+----+-----------+------------------------------------------+ 
| 33 + media 3 +  localhost://uploads/image/image_3 + 
+----+-----------+------------------------------------------+ 
| 34 + media 4 +  localhost://uploads/image/image_4 + 
+----+-----------+------------------------------------------+ 

我试图用JOIN与WHERE IN条款：

SELECT 
m.id, 
m.name as media_name, 
p.name, 
p.media_id 

FROM 
media as m 
JOIN product as p 

on m.id in (p.media_id); 

等JOIN条款，但返回的结果是：

+----+-----------+-----------+------------+-------------------------+ 
| id | name | image_id | media_name +   path   + 
+----+-----------+-----------+------------+-------------------------+ 
| 1 + product 1 + 32,33,34 + media 2 + uploads/image/image_1 + 
+----+-----------+-----------+------------+-------------------------+ 

我要的是返回结果有3条是这样的：

+----+-----------+-----------+------------+-----------------------+ 
| id | name | image_id | media_name +   path   + 
+----+-----------+-----------+------------+-----------------------+ 
| 1 + product 1 +  32 + media 2 + uploads/image/image_2 + 
+----+-----------+-----------+------------+-----------------------+ 
| 2 + product 1 +  33 + media 3 + uploads/image/image_3 + 
+----+-----------+-----------+------------+-----------------------+ 
| 3 + product 1 +  34 + media 4 + uploads/image/image_4 + 
+----+-----------+-----------+------------+-----------------------+ 

有谁知道如何连接表这样吗？

Answer 1

为了实现您要查找的内容，需要对数据库模式稍作更改，从产品表中删除逗号分隔值。

然后，您可以使用链接表，然后您可以查询连接。

产品

| id | name  | 
|----|-----------| 
| 1 | product 1 | 
| 2 | product 2 | 
| 3 | product 3 | 

product_media链接

| id | product_id | media_id | 
|----|------------|----------| 
| 1 | 1   | 1  | 
| 2 | 1   | 2  | 
| 3 | 1   | 3  | 

然后，您可以做检查链接表并返回您正在寻找的多行结果的查询。

例如

SELECT 
m.id, 
m.name as media_name, 
p.name as product_name 
m.path 

FROM 
media as m 
LEFT JOIN product_media_links as pl on m.id = pl.media_id 
LEFT JOIN products as p on p.id = pl.product_id 

使用左加入到无的产品来回报媒体，如果你愿意的话

另外，作为由别人提及，看看正常化。它在设计数据库时确实有帮助，并且您可以选择想要使用它的距离。