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

如果您愿意，可以使用左连接返回没有产品的媒体

也正如其他人提到的，看看规范化。它在设计数据库时确实很有帮助，您可以选择要使用它的程度。