MySQL：批量修改表及表内字段排序规则

'# MySQL：批量修改表及表内字段排序规则

一、背景与问题

在数据库运维和开发中，经常会遇到需要批量调整表结构的场景。例如：

• 数据库字符集升级（如从utf8升级到utf8mb4）
• 多语言支持场景下的排序规则调整（如utf8mb4_unicode_ci vs utf8mb4_unicode_ci）
• 某些字段的排序规则需要统一（如统一使用utf8mb4_bin进行严格区分）

这类场景通常需要修改表的字符集或字段的排序规则（collation）。但直接使用ALTER TABLE语句时，可能会遇到以下问题：

1. 锁表风险：批量修改可能锁表导致业务中断
2. 性能损耗：大规模表结构变更可能消耗大量系统资源
3. 兼容性问题：不同字符集/排序规则的转换可能引发数据不一致
4. 维护困难：多表多字段的批量修改容易遗漏

本文将深入探讨如何安全、高效地实现批量修改表及字段排序规则的操作。

二、基本原理

MySQL的字符集（character set）和排序规则（collation）是两个密切相关的概念：

• 字符集：定义字符的编码规则（如utf8mb4）
• 排序规则：定义字符的比较规则（如utf8mb4_unicode_ci）

每个表和字段都必须指定字符集和排序规则。在MySQL中，排序规则的命名格式为：

<字符集>_<排序规则类型>

例如：

• utf8mb4_unicode_ci（通用排序规则）
• utf8mb4_bin（二进制排序规则，区分大小写）

修改表结构的核心原理

当执行ALTER TABLE语句修改字符集/排序规则时，MySQL会：

1. 创建临时表（如果使用CONVERT TO）
2. 将原表数据复制到临时表
3. 修改临时表的字符集/排序规则
4. 重命名临时表为原表名

这个过程会锁表，导致业务不可用。因此需要特别注意操作时机。

三、环境准备

确保你的环境满足以下要求：

• MySQL 5.6+（推荐8.0）
• 数据库中有需要修改的表结构
• 已知要修改的字符集/排序规则（如utf8mb4_unicode_ci）

-- 查询当前数据库的字符集和排序规则
SHOW VARIABLES LIKE 'character_set_database';
SHOW VARIABLES LIKE 'collation_database';

四、核心实现

1. 修改表的字符集/排序规则

-- 修改整个表的字符集和排序规则（推荐方式）
ALTER TABLE your_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

关键代码解释：

• CONVERT TO语法会创建临时表进行数据迁移
• 该操作会锁表，建议在业务低峰期执行
• 操作完成后，原表会自动使用新字符集/排序规则

2. 修改单个字段的排序规则

-- 修改特定字段的排序规则
ALTER TABLE your_table
MODIFY column_name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

关键代码解释：

• MODIFY语句会重建该字段的索引
• 如果该字段有索引，重建索引过程会锁表
• 修改后的字段将使用新排序规则

3. 批量修改多个字段的排序规则

-- 批量修改多个字段的排序规则
ALTER TABLE your_table
MODIFY column1 VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
MODIFY column2 TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

关键代码解释：

• 多字段修改会依次执行，每个字段都会重建索引
• 如果字段较多，建议分批处理以降低锁表时间

五、完整案例

场景：数据库字符集升级

假设需要将所有表的字符集从utf8升级到utf8mb4，并统一使用utf8mb4_unicode_ci排序规则。

1. 前期准备

-- 查询所有表的字符集和排序规则
SELECT 
  table_name, 
  table_collation 
FROM 
  information_schema.tables 
WHERE 
  table_schema = 'your_database';

2. 编写批量修改脚本

-- 批量修改所有表的字符集和排序规则
SET @charset = 'utf8mb4';
SET @collation = 'utf8mb4_unicode_ci';

SELECT 
  CONCAT('ALTER TABLE ', table_name, ' CONVERT TO CHARACTER SET ', @charset, ' COLLATE ', @collation, ';') AS alter_sql
FROM 
  information_schema.tables 
WHERE 
  table_schema = 'your_database'
  AND table_collation != @collation;

3. 执行修改（需在业务低峰期）

-- 执行生成的SQL语句
-- 注意：需确保已备份数据，并在测试环境验证

4. 验证修改结果

-- 验证表的字符集和排序规则
SELECT 
  table_name, 
  table_collation 
FROM 
  information_schema.tables 
WHERE 
  table_schema = 'your_database';

六、源码解析

MySQL的ALTER TABLE语句处理逻辑主要在sql/sql_table.cc中实现。关键流程如下：

1. 解析ALTER TABLE语句的类型（如CONVERT TO、MODIFY等）
2. 创建临时表（CREATE TABLE ... AS SELECT）
3. 执行数据迁移（INSERT INTO temp_table SELECT * FROM original_table）
4. 重建索引（ALTER TABLE temp_table ENGINE=InnoDB）
5. 重命名临时表为原表名（RENAME TABLE temp_table TO original_table）

对于MODIFY操作，MySQL会执行：

// 修改字段的排序规则（伪代码）
if (new_collation != old_collation) {
    // 重建字段索引
    rebuild_index(field);
    // 修改字段的排序规则
    set_field_collation(field, new_collation);
}

七、进阶使用

1. 使用pt-online-schema-change工具

对于大表的结构变更，推荐使用Percona的pt-online-schema-change工具：

pt-online-schema-change h=localhost,u=root,p= --alter "CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci" D=your_database,t=your_table

优势：

• 允许在线变更，不锁表
• 自动处理索引重建
• 支持事务和回滚

2. 分批处理策略

对于包含大量字段的表，建议分批处理：

-- 分批修改字段排序规则
SET @batch_size = 100;

WHILE (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema='your_database' AND table_name='your_table' AND collation != 'utf8mb4_unicode_ci') > 0 DO
    START TRANSACTION;
    SET @sql = CONCAT('ALTER TABLE your_table ',
                      'MODIFY column1 VARCHAR(255) COLLATE utf8mb4_unicode_ci, ',
                      'MODIFY column2 TEXT COLLATE utf8mb4_unicode_ci, ',
                      '... (其他字段) ...');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    COMMIT;
    -- 等待一段时间以减少锁表时间
    SELECT SLEEP(1);
END WHILE;

八、性能与工程实践

1. 性能优化策略

2. 安全风险分析

九、常见问题与踩坑

1. 错误示例：直接修改字段排序规则

-- 错误：未指定字符集，可能导致排序规则不一致
ALTER TABLE your_table MODIFY column1 VARCHAR(255) COLLATE utf8mb4_unicode_ci;

问题分析：

• 必须同时指定字符集和排序规则
• 如果字段类型不支持指定字符集（如BLOB类型），会报错

2. 错误示例：忘记处理索引

-- 错误：未处理索引导致查询性能下降
ALTER TABLE your_table MODIFY column1 VARCHAR(255) COLLATE utf8mb4_unicode_ci;

问题分析：

• 修改字段类型会重建索引
• 如果字段有索引，会导致短暂性能下降

3. 错误示例：忽略兼容性

-- 错误：直接从utf8升级到utf8mb4可能丢失数据
ALTER TABLE your_table CONVERT TO CHARACTER SET utf8mb4;

问题分析：

• utf8不支持0x00、0x01等特殊字符
• 必须显式指定排序规则utf8mb4_unicode_ci

十、最佳实践

1. 推荐方案

1. 预演变更：使用--dry-run参数预演变更
2. 分批处理：避免一次性修改大量字段
3. 监控资源：监控CPU、内存、I/O使用情况
4. 备份机制：操作前进行全量备份
5. 文档记录：记录变更的字段、排序规则和时间

2. 推荐工具

十一、总结

批量修改表及字段排序规则是数据库运维中的常见需求，但需要特别注意以下几点：

• 性能风险：大规模变更可能导致锁表，需选择合适的时间窗口
• 兼容性问题：字符集/排序规则转换可能引发数据不一致
• 安全风险：需严格控制权限，防止误操作
• 维护难度：多表多字段的批量修改容易遗漏

通过合理使用ALTER TABLE语句、工具辅助和分批处理策略，可以安全高效地完成这些操作。在实际项目中，建议优先考虑在线变更工具（如pt-online-schema-change），以减少对业务的影响。同时，务必在操作前进行充分的测试和备份，确保变更的可逆性。