如何更简便修改MariaDB字符集与排序规则？含大库及外键问题

针对650GB大库、2500个外键的场景，提供两个高效方案，无需逐个修改列/表，也不用花费72小时删重建外键：

方案1：逻辑导出+重新导入（最省心的无外键处理方案） #

这个方案完全绕开外键约束问题，通过导出原库数据并重新导入到目标字符集环境实现转换：

1. 导出原库：用mysqldump带字符集参数导出，保证数据完整性（适合InnoDB引擎）：

   mysqldump --default-character-set=latin1 --single-transaction --quick --databases your_db_name | gzip > db_dump.sql.gz
   

   --single-transaction避免锁表，--quick降低内存占用，压缩导出节省磁盘空间。
2. 批量替换字符集配置：解压后用sed批量修改导出文件中的字符集定义：

   gzip -dc db_dump.sql.gz > db_dump.sql
   sed -i 's/CHARACTER SET latin1/CHARACTER SET utf8mb4/g' db_dump.sql
   sed -i 's/COLLATE latin1_general_ci/COLLATE utf8mb4_general_ci/g' db_dump.sql
   

3. 导入到新环境：创建空库（指定utf8mb4）后导入，导入时指定目标字符集：

   mysql --default-character-set=utf8mb4 -e "CREATE DATABASE your_db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;"
   mysql --default-character-set=utf8mb4 your_db_name < db_dump.sql
   

   若业务不能停，可在从库执行转换，完成后切换主从，大幅减少 downtime。

方案2：批量ALTER脚本+临时禁用外键约束（在线转换方案） #

如果不想做全量导出导入，可通过临时禁用外键检查，批量执行表转换语句：

1. 生成批量ALTER脚本：执行以下SQL生成所有表的转换语句（CONVERT TO会自动转换表内所有字符列的字符集）：

   SELECT CONCAT(
       'ALTER TABLE `', TABLE_NAME, '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;'
   ) AS alter_stmt
   FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_SCHEMA = 'your_db_name'
     AND TABLE_TYPE = 'BASE TABLE';
   

   将结果导出为alter_scripts.sql。
2. 临时禁用外键与自动提交：登录数据库执行：

   SET FOREIGN_KEY_CHECKS = 0;
   SET AUTOCOMMIT = 0;
   

3. 批量执行转换：运行生成的脚本，完成后恢复约束：

   SOURCE alter_scripts.sql;
   COMMIT;
   SET FOREIGN_KEY_CHECKS = 1;
   SET AUTOCOMMIT = 1;
   

   注意：MariaDB 10.2+支持InnoDB的Online DDL，可在ALTER TABLE后加ALGORITHM=INPLACE, LOCK=NONE减少锁表影响，适合业务低峰期执行。

后续配置 #

转换完成后，修改my.cnf（或my.ini）的默认字符集，避免新对象沿用旧配置：

[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
[client]
default-character-set=utf8mb4

最后验证转换结果，确保所有列都已切换至utf8mb4：

SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'your_db_name'
  AND CHARACTER_SET_NAME != 'utf8mb4';

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