MYSQL错误1267：illegal mix of collations

MySQL错误1267（Illegal mix of collations）通常发生在比较、连接或操作不同字符集（collation）的字符串时。以下是解决该问题的详细步骤：

1. 理解错误原因

当两个字符串的排序规则（collation）不同时，MySQL无法直接比较或连接它们。
例如：utf8_general_ci 和 latin1_swedish_ci 就是不同的排序规则。

2. 常见解决方法

方法1：修改表字段的排序规则

-- 修改单个字段
ALTER TABLE 表名 MODIFY 字段名 VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci;

-- 修改整个表的默认排序规则
ALTER TABLE 表名 CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

方法2：在查询时使用CAST或CONVERT函数

-- 示例1：使用CONVERT转换排序规则
SELECT * FROM table1 t1
JOIN table2 t2 ON CONVERT(t1.column USING utf8) = t2.column;

-- 示例2：使用CAST
SELECT * FROM table1 t1
JOIN table2 t2 ON CAST(t1.column AS CHAR CHARACTER SET utf8) = t2.column;

方法3：在WHERE或JOIN子句中强制统一排序规则

-- 使用COLLATE关键字
SELECT * FROM table1 t1
JOIN table2 t2 ON t1.column COLLATE utf8_general_ci = t2.column COLLATE utf8_general_ci;

方法4：修改数据库默认排序规则

-- 修改数据库默认排序规则
ALTER DATABASE 数据库名 CHARACTER SET utf8 COLLATE utf8_general_ci;

-- 查看当前数据库排序规则
SELECT @@character_set_database, @@collation_database;

3. 查看当前的排序规则

-- 查看数据库排序规则
SHOW CREATE DATABASE 数据库名;

-- 查看表排序规则
SHOW CREATE TABLE 表名;

-- 查看字段排序规则
SHOW FULL COLUMNS FROM 表名;

4. 检查连接的排序规则

-- 查看连接相关变量
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

-- 常用的设置（可在my.cnf或会话中设置）
SET NAMES 'utf8';
SET CHARACTER SET utf8;
SET collation_connection = 'utf8_general_ci';

5. 推荐的最佳实践

创建数据库时统一设置

CREATE DATABASE mydb
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_unicode_ci;

创建表时指定排序规则

CREATE TABLE mytable (
    id INT PRIMARY KEY,
    name VARCHAR(100)
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

6. 针对utf8mb4和utf8的区别

MySQL 5.5.3+建议使用utf8mb4（支持4字节字符，如emoji）
utf8在MySQL中是3字节编码

-- 将数据库升级到utf8mb4
ALTER DATABASE 数据库名 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE 表名 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

7. 快速诊断脚本

-- 检查所有表中排序规则不一致的情况
SELECT 
    TABLE_SCHEMA,
    TABLE_NAME,
    COLUMN_NAME,
    COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = '你的数据库名'
ORDER BY COLLATION_NAME;

8. 应用层面解决

在连接数据库后立即执行：

SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';

9. 配置文件修改（my.cnf/my.ini）

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci