南大通用GBase 8a之listagg/string_agg 函数的反函数实现

GBase8a数据库中 listagg/string_agg 函数的反函数实现

一、业务场景背景
在日常数据开发中，我们经常会遇到这种场景：某张表的字段里存储了用逗号（或其他分隔符）拼接的多个值，比如商品分类、标签、关联系统名称等，业务上需要把这些逗号分隔的字符串拆分成多行数据，方便后续的统计分析、关联查询或ETL处理。
这就是我们常说的“字符串拆分”，也被称为listagg/string_agg的“反函数”操作。

不同数据库生态下，实现方案差异较大：
•Oracle 中常用 CONNECT BY 层级查询配合正则实现拆分；
•GBase 8a MPP 这类国产数据库也支持类似的层级查询语法；
•但在部分环境中，层级查询语法存在兼容性限制，或在数据量较大时性能不够稳定，这时候就需要更通用、可移植的实现方案。

二、问题分析与通用方案选型
1. 原方案（层级查询法）的局限性
你最开始使用的 Oracle/GBase 层级查询方案，核心逻辑是通过 CONNECT BY LEVEL 生成序列，再用 regexp_substr 按位置截取字符串：
 

SELECT
   id,
   regexp_substr(names, '[^,]+', 1, LEVEL) AS name
FROM t_demo
WHERE names IS NOT NULL
CONNECT BY PRIOR id = id
 AND PRIOR rand() IS NOT null
 AND LEVEL <= length(names) - length(replace(names, ',', '')) + 1;

这种方案在小数据量下能快速实现需求，但存在几个明显的短板：
•兼容性差：依赖数据库的层级查询和正则扩展函数，无法直接迁移到不支持该语法的数据库；
•性能瓶颈：当单条记录的分隔项数量极多（比如上千个）时，层级查询会产生大量递归操作，执行效率下降明显；
•特殊字符风险：原方案对中文逗号、全角符号等分隔符的处理不够友好，容易出现拆分失败的情况。

2. 通用方案：数字辅助表法
我们最终选择的“数字辅助表+关联拆分”方案，是一种几乎兼容所有关系型数据库的通用实现，核心思路是：
•先创建一个存储连续整数的“数字辅助表”（numbers），生成从1到N的序列；
•用辅助表的数字，和原表做关联，关联条件为“数字 ≤ 分隔符数量+1”（也就是拆分后的行数）；
•通过两次 SUBSTRING_INDEX 函数，按位置截取第N个分隔项；
•统一分隔符格式，兼容全角逗号、半角逗号等不同写法。

这种方案的优势在于：
•兼容性极强：不依赖任何数据库的特殊语法，MySQL、GBase、PostgreSQL 等主流数据库都支持；
•性能稳定：基于普通的表关联实现，执行计划清晰，大数据量下的表现远优于层级递归；
•扩展性强：可以轻松适配不同分隔符（逗号、分号、竖线等），也支持处理全角/半角混合的场景。

三、完整实现步骤

下面我们一步步实现这个通用的字符串拆分方案。
 

步骤1：创建测试表与初始化数据
先创建一张测试表，模拟业务中常见的场景，同时包含半角逗号、全角逗号两种分隔符：
SQL
-- 先删除旧表（避免报错）
DROP TABLE IF EXISTS t_demo;

CREATE TABLE t_demo (
   id INT ,
   names VARCHAR(255)    
);

-- 插入测试数据，包含全角逗号、半角逗号两种分隔符
INSERT INTO t_demo VALUES
   (101, '苹果，香蕉，橙子'),           -- 全角逗号分隔
   (102, '电脑，手机'),                 -- 全角逗号分隔
   (103, 'GBase,MySQL,Oracle,SQLServer'); -- 半角逗号分隔
步骤2：创建数字辅助表并初始化数据
数字辅助表是整个方案的核心，我们通过存储过程批量生成连续整数，方便后续使用：
SQL
-- 删除旧的辅助表
DROP TABLE IF EXISTS numbers;

-- 创建数字辅助表，存储从1开始的连续整数
CREATE TABLE numbers (
   n INT  
);

-- 如果存储过程已存在，先删除
DROP PROCEDURE IF EXISTS InsertNumbers;

-- 修改语句结束符，避免存储过程内部的分号和外部冲突
DELIMITER $$

CREATE PROCEDURE InsertNumbers(IN max_num INT)
BEGIN
   DECLARE i INT DEFAULT 1;    

   -- 清空辅助表数据
   TRUNCATE TABLE numbers;
   
   -- 处理异常参数：如果传入的max_num为空或小于1，默认生成100条数据
   IF max_num IS NULL OR max_num < 1 THEN
       SET max_num = 100;
   END IF;
   
   -- 循环插入从1到max_num的连续整数
   WHILE i <= max_num DO
       INSERT INTO numbers (n) VALUES (i);
       SET i = i + 1;
   END WHILE;
END$$

DELIMITER ;

-- 调用存储过程，生成1-100的数字序列
CALL InsertNumbers(100);
说明：max_num 的取值建议设置为业务中“单条记录最大分隔项数量”的上限，比如你的业务中最多单条记录有50个标签，设置为100就完全够用了。
步骤3：核心拆分SQL实现
通过子查询统一分隔符，再关联数字辅助表，完成字符串拆分：
SQL
SELECT
   t.id,
   -- 两次SUBSTRING_INDEX截取第n个分隔项，再用TRIM去除首尾空格
   TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(unified_names, ',', n.n), ',', -1)) AS name
FROM (
   -- 子查询：先把所有全角逗号替换为半角逗号，统一分隔符格式
   SELECT 
       id,
       REPLACE(names, '，', ',') AS unified_names    
   FROM t_demo
   WHERE names IS NOT NULL
) t
-- 关联数字辅助表：关联条件为n不超过拆分后的总行数
JOIN numbers n
   ON n.n <= LENGTH(t.unified_names) - LENGTH(REPLACE(t.unified_names, ',', '')) + 1
-- 按原表id和拆分顺序排序，结果更易读
ORDER BY t.id, n.n;

gbase> SELECT
    ->     t.id,
    ->     TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(unified_names, ',', n.n), ',', -1)) AS name
    -> FROM (
    ->     SELECT
    ->         id,
    ->         REPLACE(names, '，', ',') AS unified_names
    ->     FROM t_demo
    ->     WHERE names IS NOT NULL
    -> ) t
    -> JOIN numbers n
    ->     ON n.n <= LENGTH(t.unified_names) - LENGTH(REPLACE(t.unified_names, ',', '')) + 1
    -> ORDER BY t.id, n.n;
+------+-----------+
| id   | name      |
+------+-----------+
|  101 | 苹果      |
|  101 | 香蕉      |
|  101 | 橙子      |
|  102 | 电脑      |
|  102 | 手机      |
|  103 | GBase     |
|  103 | MySQL     |
|  103 | Oracle    |
|  103 | SQLServer |
+------+-----------+
9 rows in set (Elapsed: 00:00:00.12)

 
可以看到，所有逗号分隔的字符串都被拆分成了独立的行，全角/半角逗号也被统一处理，拆分结果完全符合预期。

四、核心原理详解
1. 分隔符统一处理
REPLACE(names, '，', ',') 这一步是为了处理全角逗号和半角逗号混用的场景，把所有全角逗号替换为半角逗号，避免因为分隔符格式不统一导致拆分失败。
2. 计算拆分后的总行数
LENGTH(t.unified_names) - LENGTH(REPLACE(t.unified_names, ',', '')) + 1 这个公式，是计算单条记录需要拆分成多少行：
•原字符串长度：LENGTH(unified_names)
•去掉所有逗号后的字符串长度：LENGTH(REPLACE(unified_names, ',', ''))
•两者的差值，就是字符串中逗号的数量；
•逗号数量 + 1，就是拆分后的总行数（比如a,b,c有2个逗号，拆分后是3行）。
3. 两次 SUBSTRING_INDEX 截取
SUBSTRING_INDEX(str, delim, count) 函数的作用是按分隔符截取字符串：
•SUBSTRING_INDEX(unified_names, ',', n.n)：截取字符串的前n.n个分隔项，比如n=2时，GBase,MySQL,Oracle会被截取为GBase,MySQL；
•再用 SUBSTRING_INDEX(..., ',', -1)：从截取后的字符串中，取最后一个分隔项，也就是MySQL；
•最后用 TRIM() 去除可能存在的首尾空格，得到干净的结果。