[大数据]MySQL排序规则混合冲突

"org.springframework.web.util.NestedServletException: Request processing failed; 
nested exception is org.springframework.jdbc.UncategorizedSQLException: ?

### Error querying database. Cause: java.sql.SQLException: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation 'find_in_set'?

### The error may exist in URL [jar:file:/portal/programs/portal-service/portal-service-3.2.7-SNAPSHOT.jar!/BOOT-INF/lib/dao-simple-system-3.2.5-SNAPSHOT.jar!/mapper-simple-system/SysFunctionsMapper.xml]?

### The error may involve com.sitech.cmap.dao.simple.system.mapper.SysFunctionsMapper.queryFunctionsByParentId-Inline?

### The error occurred while setting parameters?

### SQL: SELECT * FROM sys_functions,( SELECT queryChildrenFuncInfo (?) functionIds ) t WHERE FIND_IN_SET(id,functionIds)?

### Cause: java.sql.SQLException: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation 'find_in_set'?;

 uncategorized SQLException for SQL []; SQL state [HY000]; error code [1267]; Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation 'find_in_set'; nested exception is java.sql.SQLException: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation 'find_in_set'"

很不幸，再次遇到了排序规则的冲突。我检查了一遍表排序规则和字段排序规则，都是utf8_general_ci，并没有出现排序规则混合的情况。

有一个值得注意的关键字出现了：FIND_IN_SET。

现在怀疑是需要一个函数去执行存储过程：

DELIMITER $$
CREATE FUNCTION `queryChildrenFuncInfo`(areaId varchar(1000)) RETURNS varchar(4000) CHARSET utf8
BEGIN
DECLARE sTemp VARCHAR(4000);
DECLARE sTempChd VARCHAR(4000);

SET sTemp='$';
SET sTempChd = CAST(areaId AS CHAR);

WHILE sTempChd IS NOT NULL DO
SET sTemp= CONCAT(sTemp,',',sTempChd);
SELECT GROUP_CONCAT(id) INTO sTempChd FROM sys_functions WHERE FIND_IN_SET(parent_id,sTempChd)>0;
END WHILE;
RETURN sTemp;
END $$
DELIMITER ;

果然是这两个字段的排序规则问题。但是大家的规则都是一样的呀，为什么还报错呢?

这时候，我们打开了库的排序规则，发现用的居然是unicode的，和表的general不一样！

于是，把需要find_in_set的两个字段都改成了unicode，就可以正常运行了。

但是不久之后，我部署了另一个系统，排序规则又出现了问题，就一不做二不休，把库、表和字段的所有排序规则都改成了unicode，终于正常了。