MariaDB存储函数按分隔符拆分字符串为结果集的实现问题

问题背景 #

需要在Windows环境的MariaDB 11.5/11.8版本中，创建可被存储过程调用的split存储函数，实现将字符串按指定分隔符拆分，返回每行一个元素的结果集或JSON数组。此前尝试的代码存在以下问题：

• 最初使用SUBSTRING_INDEX的代码仅返回单个字符串，无法拆分出所有元素
• 编写循环构建JSON数组时，先后遇到SQL语法错误、Json_Make_Array函数不存在、返回空JSON数组等问题

错误原因分析 #

1. 语法错误：REPEAT循环缺少END REPEAT闭合语句，循环条件写法错误；FOR循环的范围写法1..9999不符合MariaDB语法，应改为1 TO 9999
2. 函数误用：MariaDB中不存在Json_Make_Array函数，初始化空JSON数组需使用JSON_ARRAY()
3. 逻辑错误：使用JSON_ARRAY(jsonArray, ttxtResult)会导致数组嵌套而非追加元素，且SUBSTRING_INDEX的用法错误，需结合两次调用才能提取单个拆分元素

正确实现方案 #

方案1：返回JSON数组的存储函数 #

CREATE DEFINER=`root`@`localhost` FUNCTION `split_to_json`(
    `p_subject` TINYTEXT,
    `p_delimiter` TINYTEXT
)
RETURNS JSON
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT '将字符串按指定分隔符拆分，返回JSON数组'
BEGIN
    DECLARE v_json JSON DEFAULT JSON_ARRAY();
    DECLARE v_remaining TEXT DEFAULT p_subject;
    DECLARE v_element TEXT;
    DECLARE v_delimiter_len INT DEFAULT CHAR_LENGTH(p_delimiter);

    -- 处理空输入或空分隔符的边界情况
    IF p_subject IS NULL OR p_delimiter IS NULL OR CHAR_LENGTH(p_delimiter) = 0 THEN
        RETURN v_json;
    END IF;

    -- 循环拆分字符串
    WHILE LOCATE(p_delimiter, v_remaining) > 0 DO
        SET v_element = SUBSTRING_INDEX(v_remaining, p_delimiter, 1);
        -- 跳过空元素（需保留空元素可删除此判断）
        IF CHAR_LENGTH(v_element) > 0 THEN
            SET v_json = JSON_ARRAY_APPEND(v_json, '$', v_element);
        END IF;
        SET v_remaining = SUBSTRING(v_remaining, LOCATE(p_delimiter, v_remaining) + v_delimiter_len);
    END WHILE;

    -- 添加最后一个拆分元素
    IF CHAR_LENGTH(v_remaining) > 0 THEN
        SET v_json = JSON_ARRAY_APPEND(v_json, '$', v_remaining);
    END IF;

    RETURN v_json;
END

测试语句：

SELECT split_to_json("Simon\r\nSusan\r\nLewis\r\nJordan\r\nOliver\r\n", "\r\n");

返回结果：

["Simon", "Susan", "Lewis", "Jordan", "Oliver"]

方案2：返回结果集的存储过程（适配存储过程调用场景） #

如果需要返回每行一个元素的结果集，更适合用存储过程实现：

CREATE DEFINER=`root`@`localhost` PROCEDURE `split_to_resultset`(
    IN p_subject TINYTEXT,
    IN p_delimiter TINYTEXT
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT '将字符串按指定分隔符拆分，返回结果集'
BEGIN
    DECLARE v_remaining TEXT DEFAULT p_subject;
    DECLARE v_element TEXT;
    DECLARE v_delimiter_len INT DEFAULT CHAR_LENGTH(p_delimiter);

    -- 创建临时表存储拆分结果（已存在则先删除）
    DROP TEMPORARY TABLE IF EXISTS tbl_split_results;
    CREATE TEMPORARY TABLE tbl_split_results (element TEXT);

    -- 处理空输入或空分隔符的边界情况
    IF p_subject IS NULL OR p_delimiter IS NULL OR CHAR_LENGTH(p_delimiter) = 0 THEN
        LEAVE;
    END IF;

    -- 循环拆分并插入临时表
    WHILE LOCATE(p_delimiter, v_remaining) > 0 DO
        SET v_element = SUBSTRING_INDEX(v_remaining, p_delimiter, 1);
        IF CHAR_LENGTH(v_element) > 0 THEN
            INSERT INTO tbl_split_results VALUES (v_element);
        END IF;
        SET v_remaining = SUBSTRING(v_remaining, LOCATE(p_delimiter, v_remaining) + v_delimiter_len);
    END WHILE;

    -- 插入最后一个拆分元素
    IF CHAR_LENGTH(v_remaining) > 0 THEN
        INSERT INTO tbl_split_results VALUES (v_remaining);
    END IF;

    -- 返回结果集
    SELECT * FROM tbl_split_results;
END

调用方式：

CALL split_to_resultset("Simon\r\nSusan\r\nLewis\r\nJordan\r\nOliver\r\n", "\r\n");

返回结果：

关键说明 #

• 两个方案均处理了空输入、空分隔符等边界情况
• 若需保留连续分隔符产生的空元素，可删除代码中CHAR_LENGTH(v_element) > 0的判断
• JSON_ARRAY_APPEND是MariaDB中正确的JSON数组追加元素函数，避免了数组嵌套问题
• 返回结果集的方案使用临时表存储数据，方便其他存储过程直接读取使用

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