如何在MySQL 8.0.33中将多列拆分为多行

针对你遇到的同时拆分MyCode和MyAmount列并保证对应元素匹配的问题，这里提供两种可行的实现方法：

方法一：递归CTE拆分 #

这种方法通过递归公共表达式逐步拆分字符串，适合兼容复杂分隔符的场景（比如你的MyCode同时包含分号和逗号）：

WITH RECURSIVE split_data AS (
    SELECT
        MyID,
        -- 先将MyCode中的逗号替换为分号，统一分隔符
        TRIM(SUBSTRING_INDEX(REPLACE(MyCode, ',', ';'), ';', 1)) AS MyCode,
        TRIM(SUBSTRING_INDEX(MyAmount, ';', 1)) AS MyAmount,
        -- 提取剩余未拆分的字符串部分
        TRIM(SUBSTRING(REPLACE(MyCode, ',', ';'), LENGTH(SUBSTRING_INDEX(REPLACE(MyCode, ',', ';'), ';', 1)) + 2)) AS remaining_code,
        TRIM(SUBSTRING(MyAmount, LENGTH(SUBSTRING_INDEX(MyAmount, ';', 1)) + 2)) AS remaining_amount
    FROM your_table
    WHERE MyCode != '' AND MyAmount != ''

    UNION ALL

    SELECT
        MyID,
        TRIM(SUBSTRING_INDEX(remaining_code, ';', 1)) AS MyCode,
        TRIM(SUBSTRING_INDEX(remaining_amount, ';', 1)) AS MyAmount,
        TRIM(SUBSTRING(remaining_code, LENGTH(SUBSTRING_INDEX(remaining_code, ';', 1)) + 2)) AS remaining_code,
        TRIM(SUBSTRING(remaining_amount, LENGTH(SUBSTRING_INDEX(remaining_amount, ';', 1)) + 2)) AS remaining_amount
    FROM split_data
    WHERE remaining_code != '' AND remaining_amount != ''
)
SELECT MyID, MyCode, MyAmount
FROM split_data
ORDER BY MyID;

逻辑说明 #

1. 初始查询：提取每行MyCode和MyAmount的第一个元素，同时保留剩余未拆分的字符串。
2. 递归查询：持续拆分剩余字符串，直到剩余部分为空。
3. 最终结果：合并所有拆分后的行，按MyID排序。

方法二：JSON_TABLE拆分（更简洁） #

利用MySQL 8.0支持的JSON_TABLE函数，将字符串转换为JSON数组后解析，通过索引保证两列元素的对应关系：

SELECT
    t.MyID,
    jc.MyCode,
    ja.MyAmount
FROM your_table t
-- 解析MyCode为带索引的JSON数组
JOIN JSON_TABLE(
    CONCAT('[', REPLACE(REPLACE(t.MyCode, ',', ';'), ';', '","'), ']'),
    '$[*]' COLUMNS (
        idx INT FOR ORDINALITY,
        MyCode VARCHAR(255) PATH '$'
    )
) jc
-- 解析MyAmount为带索引的JSON数组，并通过索引关联
JOIN JSON_TABLE(
    CONCAT('[', REPLACE(t.MyAmount, ';', '","'), ']'),
    '$[*]' COLUMNS (
        idx INT FOR ORDINALITY,
        MyAmount DECIMAL(10,2) PATH '$'
    )
) ja ON jc.idx = ja.idx
ORDER BY t.MyID, jc.idx;

逻辑说明 #

1. 将MyCode和MyAmount分别转换为JSON数组格式（先统一MyCode的分隔符）。
2. 用JSON_TABLE解析数组，生成带顺序索引的行。
3. 通过索引关联两个解析结果，确保MyCode和MyAmount的元素一一对应。

注意事项 #

• 确保每行的MyCode和MyAmount拆分后的元素数量一致，否则会出现数据不匹配；可以提前用以下语句校验：

  SELECT MyID
  FROM your_table
  WHERE 
      (LENGTH(REPLACE(MyCode, ',', ';')) - LENGTH(REPLACE(REPLACE(MyCode, ',', ';'), ';', '')) + 1)
      !=
      (LENGTH(MyAmount) - LENGTH(REPLACE(MyAmount, ';', '')) + 1);
  

• MyAmount的类型可以根据实际情况调整（比如DECIMAL(10,2)），避免字符串转数值时出错。

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