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个分隔项比如n2时GBase,MySQL,Oracle会被截取为GBase,MySQL•再用 SUBSTRING_INDEX(..., ,, -1)从截取后的字符串中取最后一个分隔项也就是MySQL•最后用 TRIM() 去除可能存在的首尾空格得到干净的结果。