mysql - 将逗号分隔的字符串分隔到新表中

Question

我继承了一个项目，该项目以逗号分隔的字符串存储在名为“com_barchan_project”的表中名为“subsector”的字段中。我需要改变这个可怕的设计，因为事实证明这是一个试图解析这个字段的问题。请参阅HERE完整故事:

| id | name | sector | subsector |
+----+------+--------+-----------+
|  1 | test |   2    |  3,4,7    |
+----+------+--------+-----------+
|  2 | door |   5    |  2        |

我创建了一个名为“com_barchan_project_subsector_join”的新表，其中包含必填字段，并且希望将“com_barchan_project”中存储的值移动到这个新的空表中。

任何人都可以帮我完成这个任务的 SQL 语句吗？

新的“com_barchan_project_subsector_join”表格应如下所示:

| id | project_id | subsector_id |
+----+------------+--------------+
|  1 |      1     |      3       |
+----+------------+--------------+
|  2 |      1     |      4       |
+----+------------+--------------+
|  3 |      1     |      7       |
+----+------------+--------------+
|  4 |      2     |      2       |

移动数据后，我将从“com_barchan_project”表中删除“subsector”字段并完成处理。

感谢您的帮助!!!

约翰

Answer 1

为了简洁/清晰，使用较短的表名称；并假设您有(或可以轻松制作)一个全面的子部门表...并假设您的 csv 以一致的格式存储(至少没有空格)。

INSERT INTO `project_subsectors` (project_id, subsector_id)
SELECT p.id, s.id
FROM projects AS p
INNER JOIN subsectors AS s ON p.subsector = s.id
                           OR p.subsector LIKE CONCAT(s.id, ',%')
                           OR p.subsector LIKE CONCAT('%,', s.id, ',%')
                           OR p.subsector LIKE CONCAT('%,', s.id)
;

我不能保证它会很快；如果是的话我会感到惊讶。

ON FIND_IN_SET(s.id, p.subsector) > 0 也可能有效，但我对该函数的行为不太熟悉。