mysql - 使用另一个表中的相关值更新逗号分隔的 ID

Question

尝试找到一种有意义的方法，用另一个表中的相关值更新 table1 中一列中所有逗号分隔的 ID。请注意，由于第二个表是引用表，因此表之间没有相关的 ROW id。

格式(我没有创建)是这样的:

项目表1

    id | color_ids
    ---+----------
     1 | 2,5
     2 | 1
     3 | 2,3,9 
etc...

颜色表 2

    id | color_name
    ---+-----------
     1 | red
     2 | yellow
     3 | green 
etc...

我最终需要的是更新表 1 列(或新列)，例如:

项目表1

    id | color_ids
    ---+--------------------
     1 | yellow,purple
     2 | red
     3 | yellow,green,orange
etc...

更新:我需要所述格式的原因是数据库将定期更新数据并准备导出，我无法更改传入结构。

Answer 1

这是一种在 SQL 中执行此操作的方法，但它非常痛苦，因为它会导致相关子查询中的 Colors 表扫描。

UPDATE Items
SET color_ids = (
    SELECT GROUP_CONCAT(Colors.color_name) FROM Colors 
    WHERE FIND_IN_SET(Colors.id, Items.color_ids));

根据您的表的大小，这可能需要很长时间才能运行。

我同意其他评论，认为这不是在关系数据库中构建数据的正确方法，但我同情您目前仍坚持设计，而且我认为这更会打扰您的工作此时申请重新设计它。

我希望这段经历能帮助你成为一名开发者，而不是用类似的设计诅咒你的继任者!