MYSQL8.0 存储过程变量COLLATION编码排序字符集问题

[三年丿]

这两天搞了个存储过程跑一些业务数据，定义的某个VARCHAR类型变量，在WHERE条件和表字段比较的时候报错了

Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='

看起来像是COLLATION不一致的问题，数据库表用的都是utf8mb4_unicode_ci，变量的类型不知怎么地，变成utf8mb4_0900_ai_ci了

然后折磨就开始了，逐一排查各个方面，最后的配置定义如下

collation_connection	utf8mb4_unicode_ci
collation_database	utf8mb4_unicode_ci
collation_server	utf8mb4_unicode_ci
default_collation_for_utf8mb4	utf8mb4_0900_ai_ci

 

最蛋疼的地方来了，发现能影响变量字符集的参数，是default_collation_for_utf8mb4，偏偏这个参数，只能0900_ai和general两个值，虽然0900_ai也是unicode，但是数据库一直统一用的utf8mb4_unicode_ci，兼容性感觉更好，低版本也都支持，那么这个问题就开始无解了

现在找到两个方案：

一：强行修改数据库全表编码排序字符集=utf8mb4_0900_ai_ci

二：在存储过程里面使用变量和表字段对比的时候 加COLLATE utf8mb4_unicode_ci

方案一感觉太暴力，而且生产环境，讲道理不是很想搞这种全库影响的操作，万一出问题呢，前面也提到了，还是想继续使用utf8mb4_unicode_ci

方案二感觉太费劲，有点死板，不够灵活，只能勉强解决问题

 

各位探讨下，有没有更优质的方案，可以一键解决所有存储所有变量的类似这种问题

（PS：存储过程定义的时候指定了character SET utf8mb4 collate utf8mb4_unicode_ci）但是没屁用，和变量各玩各的 

 

新发现，存储过程里面DECLARE的变量是utf8mb4_0900_ai_ci，SET @VAR_Key=‘value’，@VAR_Key这种变量是utf8mb4_unicode_ci

[三年丿]

@_Onelone丶 表字段定义的utf8mb4_unicode_ci，现在问题是存储过程的变量，没法定义成utf8mb4_unicode_ci，就有冲突

[_Onelone丶]

定义表或者地段的字符集

[zjcxc]

@三年丿 测试了一下，存储过程的排序规则是创建时的collation_database，而这个的值，受当前数据库的影响，所以你先use到一个和你期望排序规则的库，再创建存储过程即可。数据库的默认排序规则可以通过Show create database 确认

[三年丿]

@zjcxc 第二个我好像也看明白了，这里的修改数据库默认排序规则，应该就是default_collation_for_utf8mb4这个参数了，不过上文提到过，值有限制，不好灵活控制，没法用

[三年丿]

@zjcxc 1.第一个我突然悟了，昨天一直设置不了，今天发现是DEFAULT语法的问题，能用 2.没明白，存储过程、数据库、表，都是统一的utf8mb4_unicode_ci，只是变量不跟着走，超然在外

[zjcxc]

For character data types, if CHARACTER SET is includedd in the declaration, the specified character set and its default collation is used. If the COLLATE attribute is also present, that collation is used rather than the default collation.

If CHARACTER SET and COLLATE are not present, the database character set and collation in effect at routine creation time are used. To avoid having the server use the database character set and collation, provide an explicit CHARACTER SET and a COLLATE attribute for character data parameters.

If you alter the database default character set or collation, stored routines that are to use the new database defaults must be dropped and recreated.

The database character set and collation are given by the value of the character_set_database and collation_database system variables. For more information, see Section 10.3.3, “Database Character Set and Collation”.
------------- 参考官网文档上的说明

1. 定义变量的时候指定，这个比在使用的时候指定方便了
2. 修改数据库的默认排序规则与你的表保持一致，然后创建存储过程，（数据库的排序规则通常是和表保持一致的，如果你要继续保持不一致，那么最后再改回去）