数据库查询字段拆分：将分号分隔资质导入新表

嘿，我懂你遇到的麻烦了——把profile表里用分号攒在一起的资质拆成单独行插入qualifications表对吧？你原来的SQL用SPLIT直接返回数组，数据库没法直接把数组映射成多行，得用能把数组/分隔字符串拆成行的方法，不同数据库的实现不一样，我给你几个主流数据库的解决方案：

PostgreSQL #

PostgreSQL可以用string_to_array把字符串转成数组，再用unnest把数组拆成单独行：

INSERT INTO qualifications (id, qual)
SELECT p.id, q.qual
FROM profile p, unnest(string_to_array(p.quals, ';')) q(qual)
-- 过滤空的资质值，避免插入无效数据
WHERE q.qual != '';

针对你的示例数据，这个语句会生成3行(5, 'cpr')、(5, 'firstaid')、(5, 'lifeguard')插入目标表。

MySQL 8.0+ #

MySQL 8.0及以上版本支持STRING_SPLIT函数，配合JOIN就能拆分：

INSERT INTO qualifications (id, qual)
SELECT p.id, s.value
FROM profile p
JOIN STRING_SPLIT(p.quals, ';') s
WHERE s.value != '';

如果是MySQL 5.x（没有STRING_SPLIT），可以用递归CTE来实现拆分：

WITH RECURSIVE split_quals AS (
    SELECT 
        id,
        SUBSTRING_INDEX(quals, ';', 1) AS qual,
        SUBSTRING(quals, LENGTH(SUBSTRING_INDEX(quals, ';', 1)) + 2) AS remaining
    FROM profile
    WHERE quals IS NOT NULL AND quals != ''
    UNION ALL
    SELECT 
        id,
        SUBSTRING_INDEX(remaining, ';', 1),
        SUBSTRING(remaining, LENGTH(SUBSTRING_INDEX(remaining, ';', 1)) + 2)
    FROM split_quals
    WHERE remaining IS NOT NULL AND remaining != ''
)
INSERT INTO qualifications (id, qual)
SELECT id, qual FROM split_quals
WHERE qual != '';

SQL Server 2016+ #

SQL Server 2016及以上支持STRING_SPLIT，用CROSS APPLY来关联拆分后的行：

INSERT INTO qualifications (id, qual)
SELECT p.id, s.value
FROM profile p
CROSS APPLY STRING_SPLIT(p.quals, ';') s
WHERE s.value != '';

额外提醒 #

• 记得提前确认qualifications表的结构是否和插入字段匹配（要有id和qual字段）
• 如果quals字段可能存在NULL或者全空的情况，一定要加过滤条件，避免插入无效数据

内容的提问来源于stack exchange，提问作者GFL