MySQL 8.0.23上遇到一个FIND_IN_SET的BUG

老叶茶馆

发布于 2021-10-25 16:35:49

6730

发布于 2021-10-25 16:35:49

文章被收录于专栏：MySQL修行 | 老叶茶馆MySQL修行 | 老叶茶馆

1、背景

在MySQL 8.0.23出现执行SQL超时的报错，而在MySQL8.0.18版本上未出现，故做个记录，以飨读者。

2、故障提要

在客户MySQL版本8.0.23，在部署客户测试环境时出现SQL执行超时报错。通过慢日志查询，找到对应的SQL。SQL如下：

SELECT * FROM t_dict WHERE FIND_IN_SET(id,(SELECT getDictChildrenNew(
        (select id from t_dict where code = 'metadata_classify'
     and root_code = 'metadata_classify' ),
        (select code from t_dict where code = 'metadata_classify'
     and root_code = 'metadata_classify' ),
        (select root_code from t_dict where code = 'metadata_classify'
     and root_code = 'metadata_classify' )
        )))
        order by dict_level,dict_order asc;

这个SQL用到了一个自定义函数getDictChildrenNew。函数内容如下：（真实的账号使用user_name代替了，已脱敏）

CREATE DEFINER=`user_name`@`%` FUNCTION `user_name`.`getDictChildrenNew`(iid varchar(255),icode varchar(255),ircode varchar(255)) RETURNS varchar(5000) CHARSET utf8mb4
    READS SQL DATA
BEGIN
DECLARE oTemp VARCHAR(5000);
DECLARE oTempChild VARCHAR(5000);
DECLARE oTempId VARCHAR(5000);
DECLARE oRootCode VARCHAR(5000);

SET oTemp = '';
SET oTempChild = icode;
SET oTempId = iid;
SET oRootCode = ircode;

WHILE oTempChild IS NOT NULL
DO
SET oTemp = CONCAT(oTemp,',',oTempId);

SELECT GROUP_CONCAT(ID) INTO oTempId
FROM t_dict
WHERE ROOT_CODE = oRootCode AND  FIND_IN_SET(PARENT_CODE,oTempChild);

SELECT GROUP_CONCAT(CODE) INTO oTempChild
FROM t_dict
WHERE ROOT_CODE = oRootCode AND FIND_IN_SET(PARENT_CODE,oTempChild);

END WHILE;
RETURN oTemp;
END

3、测试现象

我单独将where子句中这部分单独执行只需要122ms

SELECT getDictChildrenNew(
        (select id from t_dict where code = 'metadata_classify' #58
     and root_code = 'metadata_classify' ),
        (select code from t_dict where code = 'metadata_classify' #metadata_classify
     and root_code = 'metadata_classify' ),
        (select root_code from t_dict where code = 'metadata_classify' #metadata_classify
     and root_code = 'metadata_classify' )
        )