MySQL 多表关联查询排序规则冲突完整解决方案

问题描述

在 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. 备份数据库

重要提示：在执行任何结构修改前，务必备份整个数据库！

sql
mysqldump -u username -p --databases luck > luck_backup.sql

替换 username 为你的数据库用户名，luck 为你的数据库名。

2. 查看当前排序规则

首先查看数据库、表和字段的当前排序规则，以便了解问题所在：

sql
-- 查看数据库默认排序规则
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. 禁用外键约束

在修改表结构前，临时禁用外键约束检查：

sql
SET FOREIGN_KEY_CHECKS = 0;

4. 修改数据库默认排序规则

将整个数据库的默认排序规则设置为 utf8mb4_0900_ai_ci（MySQL 8.0 默认）：

sql
ALTER DATABASE luck CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

5. 修改所有表的排序规则

生成修改所有表的 SQL 语句：

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 语句：

sql
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 语句：

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 语句：

sql
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：

sql
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：临时删除外键约束

sql
-- 查看外键约束名
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. 恢复外键约束

完成所有修改后，恢复外键约束检查：

sql
SET FOREIGN_KEY_CHECKS = 1;

9. 验证修改结果

确认所有表和字段的排序规则已统一：

sql
SELECT TABLE_NAME, COLUMN_NAME, COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'luck'
  AND COLLATION_NAME != 'utf8mb4_0900_ai_ci';

如果查询结果为空，则表示所有字段已成功统一。

四、自动化脚本（Python）

如果你不想手动执行上述步骤，可以使用以下 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数据库表排序规则批量统一解决 https://www.bilibili.com/opus/1067757616035266560

目录