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() 去除可能存在的首尾空格，得到干净的结果。