Mysql function 自定义函数，查找子节点

This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

MySQL 创建函数报错 This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators

 

CREATE FUNCTION demo.queryChildren(nodeId VARCHAR(50))
RETURNS VARCHAR(4000)
BEGIN
    DECLARE sTemp VARCHAR(4000);
    DECLARE sTempChd VARCHAR(4000);
    SET sTemp='';
    SET sTempChd = nodeId;
    WHILE sTempChd IS NOT NULL DO
        SET sTemp= CONCAT(sTemp,',',sTempChd);
        SELECT GROUP_CONCAT(glid) INTO sTempChd FROM model_type WHERE FIND_IN_SET(pGlid,sTempChd)>0;
    END WHILE;
    RETURN sTemp;
END

SELECT demo.queryChildren('a02aee69-1183-44cb-a354-4e51caa50de8');

执行查询出现错误：  Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation 'find_in_set'

 

查询字符集

SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';

查询结果

character_set_client    utf8mb4
character_set_connection    utf8mb4
character_set_database    utf8mb4
character_set_filesystem    binary
character_set_results    
character_set_server    utf8mb4
character_set_system    utf8
collation_connection    utf8mb4_0900_ai_ci
collation_database    utf8mb4_0900_ai_ci
collation_server    utf8mb4_0900_ai_ci

 mysql设置了utf8mb4，为什么还有utf8mb4_general_ci和utf8mb4_0900_ai_ci？ - SeasonHu - 博客园 (cnblogs.com)

在Find_in_set 函数指定参数的collate，这样就可以了。

CREATE DEFINER=`root`@`localhost` FUNCTION `demo`.`queryChildren`(nodeId VARCHAR(50)) RETURNS varchar(4000) CHARSET utf8mb4
BEGIN

    DECLARE sTemp VARCHAR(4000)  CHARSET utf8mb4 collate utf8mb4_0900_ai_ci;
    DECLARE sTempChd VARCHAR(4000)  CHARSET utf8mb4 collate utf8mb4_0900_ai_ci;
    SET sTemp='';
    SET sTempChd = nodeId;
    WHILE sTempChd IS NOT NULL DO
        SET sTemp= CONCAT(sTemp,",",sTempChd);
        SELECT GROUP_CONCAT(glid) INTO sTempChd FROM model_type WHERE FIND_IN_SET(pGlid collate utf8mb4_0900_ai_ci,sTempChd collate utf8mb4_0900_ai_ci)>0;
                
    END WHILE;
 
    RETURN substr(sTemp,2);

END