MariaDB中CONCAT拼接值引发排序规则冲突的原因排查

问题现象 #

使用CONCAT_WS拼接字段后执行LIKE查询时，出现排序规则不兼容错误：

Illegal mix of collations (utf8mb4_bin,NONE) and (utf8mb4_general_ci,COERCIBLE) for operation 'like'

两台同版本（MariaDB 10.3）的RDS实例，数据库默认字符集均为utf8mb4、默认排序规则为utf8mb4_unicode_ci，表结构完全一致，但一台实例正常运行，另一台触发报错。排查发现：报错实例中CONCAT_WS拼接结果的排序规则为utf8mb4_bin，正常实例则为utf8mb4_unicode_ci。

根因定位 #

问题核心出在CAST(SUM(table1.View) AS CHAR)这部分逻辑：

1. table1.View是bit(1)类型，SUM()计算后得到数值类型，CAST将其转为字符串时，会继承当前会话的collation_connection设置，而非数据库的默认排序规则。
2. 报错实例的会话级collation_connection被设置为utf8mb4_bin（可能由驱动连接参数、全局配置或会话初始化语句导致），导致CAST生成的字符串排序规则为utf8mb4_bin。
3. CONCAT_WS的最终排序规则会取所有参数中优先级最高的排序规则，当存在utf8mb4_bin的参数时，拼接结果会继承该规则，进而与LIKE操作中使用的utf8mb4_general_ci产生冲突。

解决方案 #

方案1：强制指定CAST后的排序规则 #

修改CAST语句，显式指定字符集和排序规则，确保所有拼接参数的排序规则统一：

CONCAT_WS(', '
    ,CASE WHEN SUM((`table1`.`View`)) <> 0 THEN CONCAT('View(', CAST(SUM((`table1`.`View`)) AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_unicode_ci ,')') ELSE NULL END
    ,CASE WHEN SUM((`table1`.`Edit`)) <> 0 THEN CONCAT('Edit(', CAST(SUM((`table1`.`Edit`)) AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_unicode_ci ,')') ELSE NULL END
    ,CASE WHEN SUM((`table1`.`Add`)) <> 0 THEN CONCAT('Add(', CAST(SUM((`table1`.`Add`)) AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_unicode_ci ,')') ELSE NULL END
    , CONCAT('Static string: ',(`table2`.`Name`)))

方案2：统一会话级排序规则 #

在执行查询前，先将会话排序规则设置为与数据库默认一致：

SET collation_connection = 'utf8mb4_unicode_ci';

如果是应用连接数据库，可在连接参数中指定排序规则（如JDBC的connectionCollation参数），确保会话初始化时就使用正确的规则。

方案3：对拼接结果强制指定排序规则 #

直接在CONCAT_WS结果后添加COLLATE子句，覆盖默认规则：

CONCAT_WS(', '
    ,CASE WHEN SUM((`table1`.`View`)) <> 0 THEN CONCAT('View(', CAST(SUM((`table1`.`View`)) AS CHAR) ,')') ELSE NULL END
    ,CASE WHEN SUM((`table1`.`Edit`)) <> 0 THEN CONCAT('Edit(', CAST(SUM((`table1`.`Edit`)) AS CHAR) ,')') ELSE NULL END
    ,CASE WHEN SUM((`table1`.`Add`)) <> 0 THEN CONCAT('Add(', CAST(SUM((`table1`.`Add`)) AS CHAR) ,')') ELSE NULL END
    , CONCAT('Static string: ',(`table2`.`Name`)))
COLLATE utf8mb4_unicode_ci

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