将SQLite3迁移至MariaDB时遇未知排序规则错误求助

问题根源 #

utf8mb4_0900_ai_ci是MySQL 8.0专属的排序规则，MariaDB 11.4默认使用utf8mb4_uca1400_ai_ci（或其他兼容规则），两者不兼容，导致Python连接时触发未知排序规则错误。

解决方案 #

1. 修正Python数据库连接参数 #

检查你的Python代码中mysql.connector的连接配置，确保没有指定MariaDB不支持的排序规则：

import mysql.connector

# 正确的连接配置示例
cnx = mysql.connector.connect(
    user='swimuser',
    password='your_password',
    host='localhost',
    database='swimDB',
    charset='utf8mb4',
    # 使用MariaDB支持的排序规则，或省略让它继承数据库默认值
    collation='utf8mb4_uca1400_ai_ci'
)

如果之前显式设置了collation='utf8mb4_0900_ai_ci'，直接替换为MariaDB支持的规则即可。

2. 检查并修正导入的SQL文件 #

打开导出的schema.sql，全局搜索utf8mb4_0900_ai_ci，将所有匹配项替换为MariaDB支持的排序规则（比如utf8mb4_uca1400_ai_ci或utf8mb4_unicode_520_ci）。完成后重新导入：

mariadb -u swimuser -p swimDB < schema.sql
mariadb -u swimuser -p swimDB < data.sql

3. 统一MariaDB连接字符集配置 #

从你的变量查询结果看，客户端/连接/结果字符集为utf8mb3，可能导致连接时字符集不匹配。修改MariaDB配置文件（比如/etc/mysql/mariadb.conf.d/50-server.cnf或/etc/my.cnf）：

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

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

修改后重启MariaDB服务：

sudo systemctl restart mariadb

4. 确认并修正表的排序规则 #

执行以下SQL语句，检查swimDB中所有表的排序规则：

SELECT table_name, table_collation 
FROM information_schema.tables 
WHERE table_schema = 'swimDB';

如果发现有表使用utf8mb4_0900_ai_ci，执行ALTER语句修正：

ALTER TABLE swimmers CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci;
ALTER TABLE events CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci;
ALTER TABLE times CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci;

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