mysql 错误码: 1267

关注

原创最新推荐文章于 2026-10-23 09:48:30 发布 1.1w 阅读 AI 写同款· GEO 优化 ›

【异常】MySQL 字符集排序规则冲突：Illegal mix of collations 问题解决 MySQL出现Illegal mix of collations错误是由于字符串比较时排序规则不一致导致的。常见于MySQL 5.7升级到8.0后新旧表使用不同排序规则（如utf8mb4_general_ci和utf8mb4_0900_ai_ci）。解决方案包括：1）修改表字段统一排序规则（推荐）；2）在SQL中强制指定COLLATE；3）使用CONVERT函数转换。建议新项目统一使用utf8mb4_0900_ai_ci，升级前检查所有表排序规则一致性。阅读详情

mysql在调用存储过程时报以下错误：
错误码: 1267
Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation ‘locate’
集中有两种字符集

执行：SHOW VARIABLES LIKE ‘character_set_%’查看所有字符集
结果如下：
Variable_name Value
character_set_client utf8
character_set_connection utf8
character_set_database utf8
character_set_filesystem binary
character_set_results utf8
character_set_server utf8
character_set_system utf8
character_sets_dir D:\Wnmp\mariadb\share\charsets\

执行：SHOW VARIABLES LIKE ‘character_set_%’
结果如下：
Variable_name Value
collation_connection utf8_general_ci
collation_database utf8_general_ci
collation_server utf8_general_ci

解决办法：
SET character_set_database =utf8;
SET character_set_results =utf8;
SET character_set_server =utf8;
– SET character_set_system =utf8; /此处utf-8也可以，只读，不能修改/
SET collation_server = utf8_general_ci;
SET collation_database = utf8_general_ci;

SET collation_connection = ‘utf8_general_ci’

收录于