如何用MySQL查询将含换行的大文本字段拆分为多行？

可以通过MySQL查询实现需求，下面分两种场景给出具体方案：

方法一：递归CTE（MySQL 8.0+ 适用） #

递归CTE是MySQL 8.0及以上版本的原生特性，无需额外创建辅助表，适合处理100-500行的拆分需求。

示例表结构与测试数据 #

假设你的表名为your_table，存储目标文本的字段为text_field，并包含唯一标识字段id：

CREATE TABLE your_table (
    id INT PRIMARY KEY AUTO_INCREMENT,
    text_field TEXT NOT NULL
);

-- 插入示例换行分隔数据
INSERT INTO your_table (text_field) VALUES (
    "'2022-06-19': no_capacity\n'2022-06-20': no_capacity\n'2022-06-21': available\n'2022-06-22': available"
);

拆分查询语句 #

WITH RECURSIVE split_lines AS (
    -- 初始步骤：提取每条记录的第一行，同时计算剩余未拆分的文本
    SELECT
        id,
        TRIM(SUBSTRING_INDEX(text_field, '\n', 1)) AS line_content,
        TRIM(SUBSTRING(text_field, LENGTH(SUBSTRING_INDEX(text_field, '\n', 1)) + 1)) AS remaining_text
    FROM your_table
    WHERE text_field != ''

    UNION ALL

    -- 递归步骤：持续拆分剩余文本，直到剩余内容为空
    SELECT
        id,
        TRIM(SUBSTRING_INDEX(remaining_text, '\n', 1)) AS line_content,
        TRIM(SUBSTRING(remaining_text, LENGTH(SUBSTRING_INDEX(remaining_text, '\n', 1)) + 1)) AS remaining_text
    FROM split_lines
    WHERE remaining_text != ''
)
-- 最终结果：过滤空行，按原记录ID排序
SELECT id, line_content
FROM split_lines
WHERE line_content != ''
ORDER BY id;

方法二：数字辅助表（MySQL 5.7及以下适用） #

如果你的MySQL版本不支持递归CTE，可以创建一个数字辅助表，生成覆盖最大行数（500行）的数字序列来拆分文本。

创建数字辅助表 #

CREATE TABLE numbers (n INT PRIMARY KEY);

-- 插入1到500的数字序列
INSERT INTO numbers (n)
SELECT 1 + a.i + b.i*10 + c.i*100
FROM (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a,
     (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b,
     (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) c
WHERE 1 + a.i + b.i*10 + c.i*100 <= 500;

拆分查询语句 #

SELECT
    t.id,
    TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(t.text_field, '\n', n.n), '\n', -1)) AS line_content
FROM your_table t
-- 关联数字表，仅取不超过总行数的数字
JOIN numbers n ON n.n <= LENGTH(t.text_field) - LENGTH(REPLACE(t.text_field, '\n', '')) + 1
-- 过滤空行
WHERE TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(t.text_field, '\n', n.n), '\n', -1)) != ''
ORDER BY t.id, n.n;

额外说明 #

• 换行符兼容：如果字段中是Windows格式的\r\n换行，需要将查询中的'\n'替换为'\r\n'，或者先用REPLACE(text_field, '\r', '')统一转换为\n格式。
• 进一步拆分字段：如果需要将每行按冒号拆分为日期和状态，可以在查询中扩展处理：

  -- 以递归CTE为例，添加拆分后的字段
  SELECT
      id,
      line_content,
      SUBSTRING_INDEX(line_content, ':', 1) AS date_str,
      TRIM(SUBSTRING_INDEX(line_content, ':', -1)) AS status
  FROM split_lines
  WHERE line_content != ''
  ORDER BY id;
  

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