mysql - 如何在 phpmyadmin - MySQL 中将 id 与名称映射以形成新表

Question

我有一个名为“item”的表，如下所示:

itemId  itemName
001     Chicken Chop
002     Mushroom Chicken
003     Grilled Chicken
004     Chicken Maryland

然后，我有一个名为“genRecom”的表，如下所示:

id     orderItem1   recomItem1
359    001          002
360    001,003      002
361    001          002,003
362    001          003
363    001,002      003

从上面的两个表中，我希望我的表“Recom”如下所示:

id     orderItem1                      recomItem1
359    Chicken Chop                    Mushroom Chicken
360    Chicken Chop, Grilled Chicken   Mushroom Chicken
361    Chicken Chop                    Mushroom Chicken, Grilled Chicken
362    Chicken Chop                    Grilled Chicken
363    Chicken Chop, Mushroom Chicken  Grilled Chicken

有谁知道我应该如何组合这两个代码来生成一个表“Recom”，看起来像我上面提到的那个？感谢您的帮助。

Answer 1

您不应存储分隔值。您应该规范化数据。表示一个单元格中有一个值。

您现在可以使用 find_in_set 使用以下查询:

select
    g.id,
    group_concat(i1.itemName order by i1.itemId) as orderItem1,
    group_concat(i2.itemName order by i2.itemId) as recomItem1
from genRecom g
join item i1 on find_in_set(i1.itemId, g.orderItem1) > 0
join item i2 on find_in_set(i2.itemId, g.recomItem1) > 0
group by g.id;

Demo

根据当前存储的数据，只有当 Item 表中的 itemId 列是 varchar 类型且其中的零数完全相同时，这才会起作用。

请注意，上述查询不是 Sargable，这意味着它不能使用索引，因此可能会导致性能问题。

如果您在 genRecom 表中存储的数据有点不同(两列的类型可以是 int。类似地，item 表中的 itemId 类型可以是 int):

id    orderItem1   recomItem1
01    001          002
02    001          002
02    003          002
03    001          002
03    001          003
04    001          003

然后，这是一个简单的连接:

select
    g.id,
    group_concat(i1.itemName order by i1.itemId) as orderItem1,
    group_concat(i2.itemName order by i2.itemId) as recomItem1
from genRecom g
join item i1 on i1.itemId = g.orderItem1
join item i2 on i2.itemId = g.recomItem1
group by g.id;

哪个会表现得更好并且是 Sargable。

Demo

编辑:

上述查询产生了您预期的输出。如果您不想获得逗号分隔的输出，只需单独使用 join:

select
    g.id,
    i1.itemName as orderItem1,
    i2.itemName as recomItem1
from genRecom g
join item i1 on i1.itemId = g.orderItem1
join item i2 on i2.itemId = g.recomItem1;