如何在MariaDB 10.3中将Genre表的逗号分隔数据拆分后插入到types新表

针对你在MariaDB 10.3.36里拆分Genre表逗号分隔数据并插入types表的需求，我整理了一套可行的方案，先假设你的表结构大概是这样的（如果和实际不符，只需要调整字段名即可）：

• Genre表：包含关联产品的product_id（INT类型）和存储逗号分隔类型的genre（VARCHAR类型）
• types表：包含自增主键id、关联产品的product_id，以及存储单个类型的type_name（VARCHAR类型）

步骤1：用递归CTE验证拆分结果 #

MariaDB 10.2及以上支持递归CTE（公共表表达式），这是拆分字符串最简洁高效的方式之一。先运行下面的查询，确认拆分结果符合预期，避免直接插入出错：

WITH RECURSIVE split_genres AS (
    -- 初始查询：提取每个genre字段的第一个元素，同时保留剩余未拆分的部分
    SELECT
        product_id,
        SUBSTRING_INDEX(genre, ',', 1) AS type_name,
        SUBSTRING(genre, LENGTH(SUBSTRING_INDEX(genre, ',', 1)) + 2) AS remaining_genres
    FROM Genre
    WHERE genre IS NOT NULL AND genre != '' -- 过滤空值或空字符串记录
    UNION ALL
    -- 递归查询：继续拆分剩余的字符串，直到剩余部分为空
    SELECT
        product_id,
        SUBSTRING_INDEX(remaining_genres, ',', 1) AS type_name,
        SUBSTRING(remaining_genres, LENGTH(SUBSTRING_INDEX(remaining_genres, ',', 1)) + 2) AS remaining_genres
    FROM split_genres
    WHERE remaining_genres IS NOT NULL AND remaining_genres != ''
)
-- 查看拆分后的结果，同时去掉类型前后的空格，排除空字符串
SELECT product_id, TRIM(type_name) AS cleaned_type
FROM split_genres
WHERE TRIM(type_name) != '';

步骤2：将拆分结果插入types表 #

如果验证结果没问题，就可以把查询结果插入到types表中：

WITH RECURSIVE split_genres AS (
    SELECT
        product_id,
        SUBSTRING_INDEX(genre, ',', 1) AS type_name,
        SUBSTRING(genre, LENGTH(SUBSTRING_INDEX(genre, ',', 1)) + 2) AS remaining_genres
    FROM Genre
    WHERE genre IS NOT NULL AND genre != ''
    UNION ALL
    SELECT
        product_id,
        SUBSTRING_INDEX(remaining_genres, ',', 1) AS type_name,
        SUBSTRING(remaining_genres, LENGTH(SUBSTRING_INDEX(remaining_genres, ',', 1)) + 2) AS remaining_genres
    FROM split_genres
    WHERE remaining_genres IS NOT NULL AND remaining_genres != ''
)
INSERT INTO types (product_id, type_name)
SELECT product_id, TRIM(type_name)
FROM split_genres
WHERE TRIM(type_name) != '';

特殊情况处理 #

• 如果你的types表对(product_id, type_name)有唯一约束，不想插入重复数据，可以用ON DUPLICATE KEY UPDATE跳过重复项：

  INSERT INTO types (product_id, type_name)
  SELECT product_id, TRIM(type_name)
  FROM split_genres
  WHERE TRIM(type_name) != ''
  ON DUPLICATE KEY UPDATE id = id; -- 遇到重复时不做任何操作
  

• 如果Genre表没有product_id（只是单纯的类型列表），只需要去掉所有product_id相关的字段即可，调整后的INSERT语句会更简单。

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