如何通过纯SQL实现十年前创建的现有表的数据规范化：拆分VARCHAR类型数字列表至关联表

当然可以完全用SQL搞定这个事儿！不用写PHP脚本，纯数据库层面就能高效完成数据的规范化迁移，下面我针对主流数据库给出具体实现方案，你可以根据自己使用的数据库调整：

一、SQL Server（2016及以上版本） #

利用内置的STRING_SPLIT函数直接拆分字符串，再通过交叉应用关联原表数据：

INSERT INTO Table_B (table_A_id, platform_id)
SELECT 
    ta.id AS table_A_id,
    CAST(s.value AS INT) AS platform_id
FROM table_A ta
-- 拆分platforms列的逗号分隔值
CROSS APPLY STRING_SPLIT(ta.platforms, ',') s
-- 可选：确保插入的platform_id在Platforms表中存在（避免无效数据）
JOIN Platforms p ON CAST(s.value AS INT) = p.id
-- 过滤空值或无效行
WHERE ta.platforms IS NOT NULL AND ta.platforms != '';

二、MySQL（8.0及以上版本） #

借助递归CTE（公共表表达式）来迭代拆分字符串：

WITH RECURSIVE split_platforms AS (
    -- 初始化：提取第一个分隔值
    SELECT 
        id AS table_A_id,
        platforms,
        SUBSTRING_INDEX(platforms, ',', 1) AS platform_id,
        SUBSTRING(platforms, LENGTH(SUBSTRING_INDEX(platforms, ',', 1)) + 2) AS remaining
    FROM table_A
    WHERE platforms IS NOT NULL AND platforms != ''
    UNION ALL
    -- 递归：提取剩余的分隔值
    SELECT 
        table_A_id,
        platforms,
        SUBSTRING_INDEX(remaining, ',', 1),
        SUBSTRING(remaining, LENGTH(SUBSTRING_INDEX(remaining, ',', 1)) + 2)
    FROM split_platforms
    WHERE remaining IS NOT NULL AND remaining != ''
)
INSERT INTO Table_B (table_A_id, platform_id)
SELECT 
    table_A_id,
    CAST(platform_id AS UNSIGNED) AS platform_id
FROM split_platforms
-- 可选：验证platform_id的有效性
JOIN Platforms p ON CAST(split_platforms.platform_id AS UNSIGNED) = p.id;

如果是MySQL 5.x（无CTE支持），可以用数字辅助表来拆分：

INSERT INTO Table_B (table_A_id, platform_id)
SELECT 
    ta.id AS table_A_id,
    CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(ta.platforms, ',', n.num), ',', -1) AS UNSIGNED) AS platform_id
FROM table_A ta
-- 数字表：覆盖platforms列最多的分隔数量（比如最多10个就加到10）
JOIN (
    SELECT 1 AS num UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
) n 
ON CHAR_LENGTH(ta.platforms) - CHAR_LENGTH(REPLACE(ta.platforms, ',', '')) >= n.num - 1
WHERE ta.platforms IS NOT NULL AND ta.platforms != ''
-- 可选：验证有效性
JOIN Platforms p ON CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(ta.platforms, ',', n.num), ',', -1) AS UNSIGNED) = p.id;

三、PostgreSQL #

用string_to_array把字符串转成数组，再用unnest展开数组元素：

INSERT INTO Table_B (table_A_id, platform_id)
SELECT 
    ta.id AS table_A_id,
    UNNEST(string_to_array(ta.platforms, ','))::INT AS platform_id
FROM table_A ta
-- 可选：验证platform_id存在
JOIN Platforms p ON UNNEST(string_to_array(ta.platforms, ','))::INT = p.id
WHERE ta.platforms IS NOT NULL AND ta.platforms != '';

额外注意事项 #

• 数据校验：如果platforms列存在非数字、空值或无效ID，可以用TRY_CAST（SQL Server）、REGEXP（MySQL/PostgreSQL）等函数过滤无效数据，避免插入出错。
• 性能优化：如果table_A数据量很大，建议先分批处理，或者在插入前对相关字段加临时索引。

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