MySQL5.7升级至8.0.3*后非法排序规则匹配问题求助

方案1：查询时显式统一排序规则 #

不需要修改数据库结构或数据，直接在查询语句中指定字符串的排序规则，与字段的utf8mb3_general_ci保持一致：

SELECT mobile_users.* 
FROM mobile_users 
WHERE (first_name = 'John') 
  AND (last_name = 'Rodríguez' COLLATE utf8mb3_general_ci);

也可以反过来将字段转换为查询字符串的排序规则：

SELECT mobile_users.* 
FROM mobile_users 
WHERE (first_name = 'John') 
  AND (last_name COLLATE utf8mb4_0900_ai_ci = 'Rodríguez');

优先选择匹配字段原有排序规则的方式，避免不必要的性能损耗

方案2：调整数据库连接的字符集配置 #

在PHP的数据库连接参数中，指定与旧MySQL 5.7环境一致的字符集和排序规则，让所有通过该连接执行的查询自动适配旧库规则：

• PDO连接示例：

$dsn = 'mysql:host=localhost;dbname=your_db;charset=utf8';
$options = [
    PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8 COLLATE 'utf8mb3_general_ci'"
];
$pdo = new PDO($dsn, $user, $pass, $options);

• MySQLi连接示例：

$mysqli = new mysqli('localhost', $user, $pass, 'your_db');
$mysqli->set_charset('utf8');
$mysqli->query("SET collation_connection = 'utf8mb3_general_ci'");

该方案适配SaaS多库架构，无需逐个修改客户数据库。

方案3：修改字段元数据（不改动实际数据） #

如果允许调整数据库元数据，可批量将字段排序规则改为MySQL 8.0默认的utf8mb4_0900_ai_ci，此操作不会修改存储的实际数据：

-- 修改单个字段
ALTER TABLE mobile_users MODIFY COLUMN last_name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

-- 生成批量修改脚本（针对库中所有旧排序规则的字符串字段）
SELECT CONCAT(
    'ALTER TABLE ', table_name, ' MODIFY COLUMN ', column_name, ' ', column_type, ' CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;'
) AS alter_sql
FROM information_schema.columns
WHERE table_schema = 'your_db'
  AND data_type IN ('varchar', 'char', 'text')
  AND collation_name = 'utf8mb3_general_ci';

执行前需在测试环境验证，SaaS架构下可按客户库分批执行

问题根源 #

MySQL 5.7默认的utf8实际是utf8mb3，常用排序规则为utf8mb3_general_ci；升级到8.0后，默认字符集改为utf8mb4，排序规则为utf8mb4_0900_ai_ci。当查询字符串与字段的排序规则不匹配时，会触发Illegal mix of collations错误，特殊字符会加剧该冲突。

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