在最近的用户签到系统项目中，我遇到了一系列数据库迁移相关的问题——从导表时的排序规则错误，到权限不足导致的表结构修改失败，再到最终后台报“字符集混合”错误。整个解决过程踩了不少坑，也积累了完整的数据库兼容处理经验，特此整理成博客，希望能帮到有类似需求的开发者。

一、问题背景：从MySQL 8.0迁移到MariaDB的“水土不服”

项目最初使用的是MySQL 8.0数据库，为了优化服务器资源占用，计划迁移到轻量且兼容的MariaDB 10.5。迁移步骤很常规：用Navicat导出MySQL的业务库（存储签到用户数据、签到记录等核心表）脚本，再到MariaDB中执行导入。但第一次执行脚本就报错了，由此开启了漫长的排坑之路。

二、第一个坑：导表时“Unknown collation: ‘utf8mb4_0900_ai_ci’”

1. 错误现象

执行导出的SQL脚本时，MariaDB直接抛出排序规则不识别错误：

[ERR] 1273 - Unknown collation: 'utf8mb4_0900_ai_ci'

2. 原因分析

utf8mb4_0900_ai_ci是MySQL 8.0及以上版本专属的排序规则，基于Unicode 9.0标准，支持更多语言的精准排序；而MariaDB 10.5及以下版本并不支持该规则，仅能识别utf8mb4_unicode_ci（兼容性强，覆盖多数场景）或utf8mb4_general_ci（排序速度快，适合简单场景）这类通用规则。

3. 解决方法

将导出的SQL脚本中所有utf8mb4_0900_ai_ci批量替换为utf8mb4_unicode_ci（签到系统需支持不同用户的姓名排序，优先选兼容性更强的规则）。以核心表user_profiles（存储签到用户资料）为例，修改前后对比：

修改前：

CREATE TABLE `user_profiles`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '关联的用户名（关联签到记录）',
  `real_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '用户真实姓名',
  -- 其他字段（头像、联系方式等签到用户信息）...
) ENGINE = InnoDB AUTO_INCREMENT = 31 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = DYNAMIC;

修改后：

CREATE TABLE `user_profiles`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '关联的用户名（关联签到记录）',
  `real_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '用户真实姓名',
  -- 其他字段（头像、联系方式等签到用户信息）...
) ENGINE = InnoDB AUTO_INCREMENT = 31 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = DYNAMIC;

替换完成后重新执行脚本，导表成功，checkins（签到记录表）、users（用户账号表）、user_profiles（用户资料表）三大核心表均正常创建。

三、第二个坑：修改表结构时“ALTER command denied”与“外键约束拦截”

导表成功后，测试签到功能时发现：checkins表的user_name字段字符集未同步修改（仍为旧规则），导致用户签到记录无法关联用户信息，需执行ALTER TABLE调整，但又遇到了新问题。

1. 子问题1：权限不足“1142 - ALTER command denied”

执行修改语句时，MariaDB提示当前业务用户没有表结构修改权限：

MariaDB [sign_system]> ALTER TABLE checkins MODIFY COLUMN user_name VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ERROR 1142 (28000): ALTER command denied to user 'sign_user'@'%' for table 'checkins'

解决：用root高权限用户登录，给业务用户（sign_user，仅用于签到系统业务操作）授予目标库的ALTER权限，避免直接使用高权限用户操作业务：

-- 授予对签到业务库所有表的ALTER权限（仅允许修改表结构，无删库等高危权限）
GRANT ALTER ON sign_system.* TO 'sign_user'@'%';
-- 刷新权限使配置生效
FLUSH PRIVILEGES;

如果执行时提示“1133 - Can’t find any matching row in the user table”，说明业务用户尚未创建，需先创建用户并设置密码：

CREATE USER 'sign_user'@'%' IDENTIFIED BY '你的业务用户密码'; -- %表示允许从任意地址连接（按实际需求调整）

2. 子问题2：外键约束“1833 - Cannot change column ‘user_name’”

权限解决后，修改users（用户账号表）和user_profiles（用户资料表）的user_name字段时又报错——user_name是外键关联字段（user_profiles.user_name关联users.user_name，确保签到用户资料与账号一一对应），直接修改会触发外键约束校验：

MariaDB [sign_system]> ALTER TABLE users MODIFY COLUMN user_name VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ERROR 1833 (HY000): Cannot change column 'user_name': used in a foreign key constraint 'FK_sign_user_profile' of table 'sign_system.user_profiles'

解决思路：外键约束需先暂时移除，待字段修改完成后重新建立（保证数据关联一致性）：

-- 1. 查看外键详情（确认约束名、关联表和字段，避免删错）
SELECT 
  CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME, 
  REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME 
FROM 
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE 
  CONSTRAINT_NAME = 'FK_sign_user_profile' -- 外键约束名（报错信息中可获取）
  AND TABLE_SCHEMA = 'sign_system'; -- 签到业务库名

-- 2. 删除外键（从“引用表”user_profiles删除，主表users无需操作）
ALTER TABLE user_profiles DROP FOREIGN KEY FK_sign_user_profile;

-- 3. 依次修改主表和引用表的user_name字段（确保字符集/排序规则完全一致）
ALTER TABLE users MODIFY COLUMN user_name VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL UNIQUE; -- 主表（用户账号唯一）
ALTER TABLE user_profiles MODIFY COLUMN user_name VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL UNIQUE; -- 引用表（关联账号）

-- 4. 重建外键（恢复用户资料与账号的关联，避免签到数据错乱）
ALTER TABLE user_profiles 
ADD CONSTRAINT FK_sign_user_profile -- 恢复原约束名（便于后续维护识别）
FOREIGN KEY (user_name) 
REFERENCES users(user_name) 
ON DELETE CASCADE; -- 主表用户账号删除时，自动删除关联的用户资料（按签到系统业务逻辑调整）

四、第三个坑：后台报“Illegal mix of collations”查询错误

表结构修改完成后，启动签到系统后台（基于NestJS+TypeORM，负责处理用户签到请求、查询签到记录），发现日志中频繁出现排序规则混合错误，导致用户无法查询自己的历史签到记录：

QueryFailedError: Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '='

1. 错误原因

后台执行关联查询时（如“查询用户近7天签到记录”：checkins LEFT JOIN users ON checkins.user_name = users.user_name），checkins表的user_name字段用了utf8mb4_general_ci，而users表用了utf8mb4_unicode_ci——排序规则不同，数据库无法判断两个字段的“等值关系”（如同样的用户名“test123”，在不同规则下编码逻辑有差异），导致查询失败。

2. 解决方法：统一所有核心表的排序规则

用CONVERT TO CHARACTER SET语句批量统一表和字段的排序规则（一步到位，避免遗漏字段）：

-- 1. 统一签到记录表（checkins）：含user_name（关联用户）、content（签到内容）等字符字段
ALTER TABLE checkins CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 2. 统一用户账号表（users）：含user_name（账号）、password（加密密码，虽非查询关联字段，但统一规则更规范）
ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 3. 统一用户资料表（user_profiles）：含user_name（关联账号）、real_name（姓名）等字段
ALTER TABLE user_profiles CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

3. 验证结果

执行以下SQL确认所有核心表和字段的排序规则已统一，避免后续隐患：

-- 查看表级排序规则（确认三张核心表的默认规则一致）
SELECT TABLE_NAME, TABLE_COLLATION 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = 'sign_system' 
AND TABLE_NAME IN ('checkins', 'users', 'user_profiles');

-- 查看字段级排序规则（重点确认关联字段user_name）
SELECT TABLE_NAME, COLUMN_NAME, COLLATION_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'sign_system' 
AND COLUMN_NAME = 'user_name';

当所有结果均显示utf8mb4_unicode_ci时，重启签到系统后台，用户查询历史签到记录的功能恢复正常，报错彻底消失。

五、总结：签到系统数据库迁移避坑指南

1. 排序规则优先“向下兼容”：跨MySQL/MariaDB版本迁移时，避免使用高版本专属规则（如utf8mb4_0900_ai_ci），优先选择utf8mb4_unicode_ci——既能满足签到系统的用户信息排序需求，又能兼容主流数据库版本，减少导表报错。

2. 业务用户权限“最小化”：不给业务用户（如签到系统的sign_user）授予root级权限，仅按需开放ALTER（改表）、SELECT（查签到记录）、INSERT（新增签到）等必要权限，降低服务器安全风险。

3. 外键操作“三步走”：修改关联字段时，严格遵循“删外键→改字段→重建外键”流程——尤其签到系统需保证用户账号、资料、签到记录的关联性，避免因外键缺失导致数据错乱。

4. 迁移后“全量验证”：导表和改表完成后，务必验证“表级规则+字段级规则”的一致性，同时测试核心业务（如用户签到、记录查询），避免线上环境出现隐性报错。

这次迁移虽然踩了不少坑，但也让我对数据库字符集、权限、外键的底层逻辑有了更深入的理解。如果你的签到系统、用户管理系统也涉及跨版本数据库迁移，希望这篇博客能帮你少走弯路～