如何修改变量/常量默认字符集与排序规则？解决LIKE操作冲突

问题原因 #

用户定义变量（如@searchTerm）的字符集与排序规则由会话级的collation_connection参数决定，而非数据库级设置。即便你修改了数据库的默认排序规则，若当前会话的collation_connection仍为utf8mb4_0900_ai_ci，变量会直接继承该规则，与表中FirstName列的utf8mb4_unicode_520_ci产生冲突，导致like操作触发排序规则不兼容异常。

解决办法 #

1. 存储过程内显式指定变量排序规则 #

赋值时直接指定变量的排序规则，确保与目标列一致：

CREATE PROCEDURE myProc ()
BEGIN
   SET @searchTerm = '%' COLLATE utf8mb4_unicode_520_ci;
   SELECT * FROM user WHERE FirstName like @searchTerm;
END 

2. 查询时统一双方排序规则 #

在like操作中，将变量或列的排序规则转换为另一方的规则：

-- 转换变量的排序规则（推荐，不影响索引使用）
SELECT * FROM user WHERE FirstName like @searchTerm COLLATE utf8mb4_unicode_520_ci;

-- 转换列的排序规则（不推荐，会导致索引失效）
SELECT * FROM user WHERE FirstName COLLATE utf8mb4_0900_ai_ci like @searchTerm;

3. 修改会话级连接排序规则 #

执行存储过程前，先调整当前会话的collation_connection参数，使其与数据库/表规则匹配：

SET collation_connection = 'utf8mb4_unicode_520_ci';
CALL myProc();

若需永久生效，可修改MySQL配置文件（my.cnf/my.ini），添加或修改：

[mysqld]
collation_connection = utf8mb4_unicode_520_ci

修改后重启MySQL服务即可。

4. 用存储过程局部变量替代用户定义变量 #

用户定义变量（@xxx）是会话级的，而存储过程内通过DECLARE声明的局部变量，会继承存储过程创建时的数据库默认排序规则，也可显式指定：

CREATE PROCEDURE myProc ()
BEGIN
   DECLARE searchTerm VARCHAR(64) COLLATE utf8mb4_unicode_520_ci DEFAULT '%';
   SELECT * FROM user WHERE FirstName like searchTerm;
END 

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