MySQL中按块大小重新拆分VARCHAR列值（不使用函数）

需求说明 #

现有表 input_table，原表结构如下：

CREATE TABLE input_table
(
    CHUNK_ID     BIGINT        NOT NULL
        PRIMARY KEY,
    VALUE        VARCHAR(5000) NULL,
    GROUPING_ID BIGINT         NULL
);

需要将 VALUE 列类型从 VARCHAR(5000) 调整为 VARCHAR(4000)，同时需先按 GROUPING_ID 合并同组的拆分字符串，再按4000字符长度重新拆分存储。

实现步骤 #

1. 合并同组字符串到临时表

通过 GROUP_CONCAT 按 GROUPING_ID 合并字符串，存入临时表。注意先调整 GROUP_CONCAT 的最大长度，避免长字符串被截断：

-- 临时设置GROUP_CONCAT的最大长度，确保能容纳完整合并后的字符串
SET SESSION group_concat_max_len = 1000000;

-- 创建临时表存储每个分组的完整字符串
CREATE TEMPORARY TABLE temp_full_strings (
    GROUPING_ID BIGINT NOT NULL,
    FULL_VALUE TEXT NOT NULL,
    PRIMARY KEY (GROUPING_ID)
);

-- 插入合并后的数据（按CHUNK_ID排序保证拼接顺序正确）
INSERT INTO temp_full_strings (GROUPING_ID, FULL_VALUE)
SELECT 
    GROUPING_ID, 
    GROUP_CONCAT(VALUE ORDER BY CHUNK_ID SEPARATOR '')
FROM input_table
WHERE VALUE IS NOT NULL  -- 排除空值，避免拼接无效内容
GROUP BY GROUPING_ID;

2. 递归拆分完整字符串为4000字符片段

利用MySQL的递归CTE（需MySQL 8.0及以上版本支持），无需自定义函数即可完成拆分：

-- 若存在超过1000段的超长字符串，需调整递归深度限制
SET SESSION cte_max_recursion_depth = 10000;

WITH RECURSIVE split_strings AS (
    -- 初始查询：截取第一段4000字符
    SELECT 
        GROUPING_ID,
        FULL_VALUE,
        1 AS new_chunk_seq,
        SUBSTRING(FULL_VALUE, 1, 4000) AS new_value,
        LENGTH(FULL_VALUE) AS total_len,
        4000 AS current_pos
    FROM temp_full_strings
    WHERE LENGTH(FULL_VALUE) > 0

    UNION ALL

    -- 递归查询：依次截取后续片段
    SELECT 
        GROUPING_ID,
        FULL_VALUE,
        new_chunk_seq + 1,
        SUBSTRING(FULL_VALUE, current_pos + 1, 4000),
        total_len,
        current_pos + 4000
    FROM split_strings
    WHERE current_pos < total_len
)
-- 查看拆分结果（可选，用于验证）
SELECT GROUPING_ID, new_chunk_seq, new_value FROM split_strings;

3. 迁移拆分后数据回原表

先备份原表防止数据丢失，再修改列类型并插入拆分后的数据：

-- 备份原表（可选但建议执行）
CREATE TABLE input_table_backup LIKE input_table;
INSERT INTO input_table_backup SELECT * FROM input_table;

-- 清空原表数据
TRUNCATE TABLE input_table;

-- 修改VALUE列类型为VARCHAR(4000)
ALTER TABLE input_table MODIFY COLUMN VALUE VARCHAR(4000) NULL;

-- 插入拆分后的数据，重新生成唯一的CHUNK_ID
INSERT INTO input_table (CHUNK_ID, VALUE, GROUPING_ID)
SELECT 
    ROW_NUMBER() OVER (ORDER BY GROUPING_ID, new_chunk_seq) AS CHUNK_ID,
    new_value,
    GROUPING_ID
FROM split_strings;

注意事项 #

• 若你的MySQL版本低于8.0，递归CTE不可用，需改用数字辅助表实现，但建议升级版本简化操作。
• 需根据实际最大字符串长度调整 group_concat_max_len 和 cte_max_recursion_depth 的值，避免截断或递归中断。
• 若原表中存在 GROUPING_ID 为NULL的记录，需单独处理（比如归为同一组或直接保留），上述示例未包含此类场景，可按需调整。

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