1273 database encoding_error ai_generated true

错误 1273 (HY000): 未知的排序规则：'utf8mb4_0900_ai_ci'

ERROR 1273 (HY000): Unknown collation: 'utf8mb4_0900_ai_ci'

ID: database/mysql-unknown-collation

其他格式: JSON · Markdown 中文 · English
83%修复率
86%置信度
1证据数
2024-03-18首次发现

版本兼容性

版本状态引入弃用备注
MySQL 5.7.44 active — — —
MySQL 5.6.51 active — — —
MariaDB 10.11.8 active — — —

根因分析

MySQL 数据库或表使用了当前服务器版本不支持的排序规则（utf8mb4_0900_ai_ci），通常是因为该排序规则是在 MySQL 8.0 中引入的，但服务器运行在较旧版本（例如 5.7 或 5.6）上。

English

A MySQL database or table uses a collation (utf8mb4_0900_ai_ci) that is not supported by the current server version, typically because the collation was introduced in MySQL 8.0 but the server is on an older version (e.g., 5.7 or 5.6).

generic

官方文档

https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8mb4.html

解决方案

  1. Replace the collation in the SQL dump file using sed: sed -i 's/utf8mb4_0900_ai_ci/utf8mb4_general_ci/g' dump.sql, then import the modified dump: mysql -u root -p mydb < dump.sql.
  2. If the dump is from a MySQL 8.0 database, use mysqldump with the --compatible=mysql56 flag to generate a dump compatible with older versions: mysqldump --compatible=mysql56 -u root -p mydb > dump.sql.
  3. Manually alter the collation of the affected tables after import (if the import partially succeeds): ALTER TABLE my_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

无效尝试

常见但无效的做法:

  1. Changing the collation to a random one like 'utf8mb4_general_ci' without checking table definitions 80% 失败

    This may work for some tables but can cause data inconsistency if the original collation had specific requirements (e.g., case sensitivity); also, it requires altering each table individually.

  2. Upgrading MySQL to 8.0 without testing compatibility 75% 失败

    While upgrading fixes the collation issue, it may introduce other incompatibilities (e.g., SQL syntax changes, deprecated features) that break the application.

  3. Ignoring the error and continuing with the import 100% 失败

    The error stops the import process; ignoring it will result in an incomplete or failed database restore.