MySQL 5.7自定义count_Occurrence函数语法错误排查求助

问题说明 #

编写的用于统计字符出现次数的自定义函数，在MySQL 5.7执行时触发语法错误：You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version.

原函数代码：

CREATE FUNCTION count_Occurrence(str varchar(1000),x varchar(20)) RETURNS INT
BEGIN
SET @cn:=0;
WHILE (INSTR(str,x) <> 0) DO
    SET @cn=@cn+1;
    REPLACE(str,SUBSTRING(str,0,INSTR(str,x)),"");
END WHILE;
RETURN @cn;
END

测试输入：str='abc;sdifh;1231;hsjh;wque'，x=';'，期望输出：4

错误原因分析 #

1. 未指定自定义分隔符：MySQL默认语句分隔符是;，函数体内部包含多个;，会导致MySQL提前终止函数定义，触发语法错误。
2. REPLACE语句未赋值：原代码中REPLACE(str,SUBSTRING(str,0,INSTR(str,x)),"")仅执行替换操作，但未将结果赋值回str变量，既无法更新循环的字符串，也不符合MySQL语法要求。
3. SUBSTRING索引错误：MySQL的SUBSTRING函数起始索引为1（而非0），原代码中SUBSTRING(str,0,INSTR(str,x))会返回空字符串，逻辑完全错误。

修正后的函数代码 #

DELIMITER //
CREATE FUNCTION count_Occurrence(str varchar(1000),x varchar(20)) RETURNS INT
BEGIN
    DECLARE cn INT DEFAULT 0;
    DECLARE pos INT;
    SET pos = INSTR(str, x);
    WHILE pos > 0 DO
        SET cn = cn + 1;
        SET str = SUBSTRING(str, pos + CHAR_LENGTH(x));
        SET pos = INSTR(str, x);
    END WHILE;
    RETURN cn;
END //
DELIMITER ;

测试验证 #

执行以下语句测试：

SELECT count_Occurrence('abc;sdifh;1231;hsjh;wque', ';');

返回结果为4，符合预期。

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