MySQL 8.0.23中LOAD DATA拆分num字段为多行的实现方法

1. 用LOAD DATA LOCAL INFILE导入生成表A #

先创建表A的结构：

CREATE TABLE table_a (
    id CHAR(1) NOT NULL,
    cnt CHAR(2) NOT NULL,
    num VARCHAR(500) NOT NULL
);

通过固定长度参数化截取的方式导入数据，利用变量接收整行内容，再按指定长度拆分字段：

LOAD DATA LOCAL INFILE '/path/to/your/data.txt'
INTO TABLE table_a
FIELDS TERMINATED BY ''  -- 无字段分隔符，按固定位置截取
LINES TERMINATED BY '\n'
(@full_line)
SET
    id = SUBSTRING(@full_line, 1, 1),  -- 截取第1位作为id（长度1）
    cnt = SUBSTRING(@full_line, 2, 2),  -- 截取第2-3位作为cnt（长度2）
    num = SUBSTRING(@full_line, 4, 500);  -- 截取第4-503位作为num（长度500）

2. 将表A的num字段拆分生成表B #

先创建表B的结构：

CREATE TABLE table_b (
    id CHAR(1) NOT NULL,
    cnt CHAR(2) NOT NULL,
    split_num CHAR(6) NOT NULL
);

利用MySQL 8.0支持的递归CTE实现按每6位拆分，自动生成多行数据：

WITH RECURSIVE split_nums AS (
    SELECT
        id,
        cnt,
        num,
        1 AS pos,
        SUBSTRING(num, 1, 6) AS split_num
    FROM table_a
    WHERE LENGTH(num) >= 6
    UNION ALL
    SELECT
        id,
        cnt,
        num,
        pos + 6,
        SUBSTRING(num, pos + 6, 6) AS split_num
    FROM split_nums
    WHERE pos + 6 <= LENGTH(num)
)
INSERT INTO table_b (id, cnt, split_num)
SELECT id, cnt, split_num
FROM split_nums
WHERE split_num IS NOT NULL;

如果需要过滤掉最后不足6位的片段，可在最后SELECT语句中添加WHERE LENGTH(split_num) = 6。

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