在 MySQL 多表关联查询时，经常会遇到类似这样的错误：
(1267, "Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='")
这是由于不同表或字段使用了不一致的字符集排序规则导致的冲突。本文提供一套完整的解决方案，帮助你将整个数据库的排序规则统一，彻底解决此类问题。
一、问题原因分析
MySQL 中，字符集（如 utf8mb4）定义了如何存储字符，而排序规则（如 utf8mb4_0900_ai_ci）定义了字符的比较和排序方式。当执行 JOIN、WHERE 等操作时，如果涉及的字段排序规则不一致，就会触发此错误。
常见原因包括：
数据库创建时使用了默认排序规则（如 latin1_swedish_ci）
不同时期创建的表使用了不同的排序规则
导入数据时未指定正确的字符集
二、解决方案概述
我们将通过以下步骤解决问题：
禁用外键约束（避免修改表结构时受限制）
修改数据库默认排序规则
修改所有表的排序规则
修改所有字段的排序规则
恢复外键约束
验证修改结果
三、详细操作步骤
1. 备份数据库
重要提示：在执行任何结构修改前，务必备份整个数据库！可使用以下命令：
mysqldump -u username -p --databases luck > luck_backup.sql
替换 username 为你的数据库用户名，luck 为你的数据库名。
2. 查看当前排序规则
首先查看数据库、表和字段的当前排序规则，以便了解问题所在：
-- 查看数据库默认排序规则
SELECT DEFAULT_COLLATION_NAME
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME = 'luck';
-- 查看所有表的排序规则
SELECT TABLE_NAME, TABLE_COLLATION
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'luck';
-- 查看所有字段的排序规则（重点关注外键关联字段）
SELECT COLUMN_NAME, COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'luck';
3. 禁用外键约束
在修改表结构前，临时禁用外键约束检查：
SET FOREIGN_KEY_CHECKS = 0;
4. 修改数据库默认排序规则
将整个数据库的默认排序规则设置为 utf8mb4_0900_ai_ci（MySQL 8.0 默认）：
ALTER DATABASE luck CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
5. 修改所有表的排序规则
生成修改所有表的 SQL 语句：
SELECT CONCAT('ALTER TABLE `', TABLE_NAME, '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'luck';
执行上述查询后，会输出类似以下的多条 ALTER TABLE 语句：
ALTER TABLE `table1` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE `table2` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
...
复制这些语句并逐个执行。
6. 修改所有字段的排序规则
生成修改所有字段的 SQL 语句（注意字符集和排序规则的位置）：
SELECT CONCAT(
'ALTER TABLE `', TABLE_NAME, '` MODIFY `', COLUMN_NAME, '` ',
COLUMN_TYPE,
' CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci',
IF(IS_NULLABLE = 'NO', ' NOT NULL', ''),
IF(COLUMN_DEFAULT IS NOT NULL, CONCAT(' DEFAULT ''', COLUMN_DEFAULT, ''''), ''),
';')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'luck'
AND COLLATION_NAME IS NOT NULL
AND COLLATION_NAME != 'utf8mb4_0900_ai_ci';
执行上述查询后，会输出类似以下的多条 ALTER TABLE 语句：
ALTER TABLE `users` MODIFY `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL;
ALTER TABLE `products` MODIFY `description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
...
复制这些语句并逐个执行。
7. 处理外键关联字段的特殊情况
如果修改外键关联字段时遇到错误（如 3780 - 字段不兼容），需按以下步骤处理：
情况 1：先修改被引用表的字段 如果 table1.id 被 table2.ref_id 引用，先修改 table2.ref_id：
ALTER TABLE `table2` MODIFY `ref_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL;
ALTER TABLE `table1` MODIFY `id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL;
情况 2：临时删除外键约束
-- 查看外键约束名
SHOW CREATE TABLE table2;
-- 删除外键约束
ALTER TABLE table2 DROP FOREIGN KEY fk_table2_ref_id;
-- 修改字段
ALTER TABLE `table1` MODIFY `id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL;
ALTER TABLE `table2` MODIFY `ref_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL;
-- 重新添加外键约束
ALTER TABLE table2 ADD CONSTRAINT fk_table2_ref_id FOREIGN KEY (`ref_id`) REFERENCES `table1` (`id`);
8. 恢复外键约束
完成所有修改后，恢复外键约束检查：
SET FOREIGN_KEY_CHECKS = 1;
9. 验证修改结果
确认所有表和字段的排序规则已统一：
SELECT TABLE_NAME, COLUMN_NAME, COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'luck'
AND COLLATION_NAME != 'utf8mb4_0900_ai_ci';
如果查询结果为空，则表示所有字段已成功统一。
四、自动化脚本（Python）
如果你不想手动执行上述步骤，可以使用以下 Python 脚本自动完成：
import pymysql
db_config = {
'host': 'localhost',
'user': 'your_username',
'password': 'your_password',
'database': 'luck',
'charset': 'utf8mb4'
}
try:
conn = pymysql.connect(**db_config)
cursor = conn.cursor()
# 禁用外键约束
cursor.execute("SET FOREIGN_KEY_CHECKS = 0;")
print("已禁用外键约束检查")
# 修改数据库排序规则
cursor.execute("ALTER DATABASE luck CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;")
print("已修改数据库默认排序规则")
# 修改所有表的排序规则
cursor.execute("""
SELECT CONCAT('ALTER TABLE `', TABLE_NAME, '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'luck';
""")
alter_table_queries = [row[0] for row in cursor.fetchall()]
for query in alter_table_queries:
cursor.execute(query)
print(f"执行: {query}")
# 修改所有字段的排序规则
cursor.execute("""
SELECT CONCAT(
'ALTER TABLE `', TABLE_NAME, '` MODIFY `', COLUMN_NAME, '` ',
COLUMN_TYPE,
' CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci',
IF(IS_NULLABLE = 'NO', ' NOT NULL', ''),
IF(COLUMN_DEFAULT IS NOT NULL, CONCAT(' DEFAULT ''', COLUMN_DEFAULT, ''''), ''),
';')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'luck'
AND COLLATION_NAME IS NOT NULL
AND COLLATION_NAME != 'utf8mb4_0900_ai_ci';
""")
alter_column_queries = [row[0] for row in cursor.fetchall()]
for query in alter_column_queries:
cursor.execute(query)
print(f"执行: {query}")
# 恢复外键约束
cursor.execute("SET FOREIGN_KEY_CHECKS = 1;")
print("已恢复外键约束检查")
# 提交事务
conn.commit()
print("所有表和字段的排序规则已统一!")
except Exception as e:
print(f"错误: {e}")
conn.rollback()
finally:
cursor.close()
conn.close()
五、注意事项
备份的重要性：操作前一定要备份数据库，防止数据丢失
锁表问题：大表的 ALTER TABLE 操作可能会导致长时间锁表，建议在业务低峰期执行
版本兼容性：utf8mb4_0900_ai_ci 是 MySQL 8.0+ 的默认排序规则，如果使用 MySQL 5.7，建议使用 utf8mb4_unicode_ci
应用代码检查：修改后需确认应用代码中的 SQL 查询和比较逻辑仍然正常工作
性能影响：统一排序规则可能会影响部分查询的性能，建议在修改后进行性能测试
通过以上步骤，你可以彻底解决 MySQL 数据库中的排序规则冲突问题，避免 "Illegal mix of collations" 错误，提高数据库的稳定性和查询效率。
