如何将整个数据库现存有的表统一改成统一编码和排序

以下是数据库统一迁移到 utf8mb4 字符集和 utf8mb4_general_ci 排序规则的专业实施指南：

---

一、预迁移准备清单

1. 兼容性验证

-- 检查MySQL版本（5.5.3+支持完整功能）
SHOW VARIABLES LIKE 'version';

-- 验证默认字符集
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

2. 容量评估公式

预估列长度 = ceil(原始字符数 * 4 / 3)
示例：原VARCHAR(255) latin1 列需修改为VARCHAR(191) utf8mb4

---

二、工业级迁移操作流程

1. 全量数据备份

# 逻辑备份
mysqldump -u root -p --single-transaction --routines --triggers \
--databases your_db > full_backup_$(date +%F).sql

# 物理备份（InnoDB）
sudo systemctl stop mysql
cp -R /var/lib/mysql /backup/mysql_$(date +%F)
sudo systemctl start mysql

2. 配置数据库参数

/etc/my.cnf 关键配置：

[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
innodb_file_per_table=ON
innodb_file_format=Barracuda
innodb_large_prefix=ON

[client]
default-character-set=utf8mb4

3. 动态执行表结构迁移

使用自动化脚本生成修改语句：

SELECT 
  CONCAT(
    'ALTER TABLE ', table_schema, '.', table_name, 
    ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;'
  ) AS alter_sql
FROM information_schema.tables 
WHERE table_schema = 'your_db' 
  AND table_type = 'BASE TABLE';

4. 批量执行列级修改（特殊场景）

SELECT
  CONCAT(
    'ALTER TABLE ', C.TABLE_NAME, 
    ' MODIFY ', C.COLUMN_NAME, ' ', C.COLUMN_TYPE, 
    ' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci',
    IF(C.IS_NULLABLE='NO', ' NOT NULL', ''), ';'
  ) AS ddl
FROM information_schema.COLUMNS C
WHERE C.TABLE_SCHEMA = 'your_db'
  AND C.COLLATION_NAME IS NOT NULL
  AND (C.CHARACTER_SET_NAME != 'utf8mb4' 
       OR C.COLLATION_NAME != 'utf8mb4_general_ci');

---

三、关键优化点

1. 索引长度快速计算法

-- 检查关键索引长度
SELECT 
  TABLE_NAME,
  INDEX_NAME,
  COLUMN_NAME,
  CHARACTER_MAXIMUM_LENGTH,
  SUB_PART
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'your_db'
  AND SUB_PART * 4 > 191;  -- utf8mb4占用因子

2. 在线DDL方案选择

操作类型	推荐工具	适用场景
小型表	原生ALTER TABLE	< 1GB数据
大型表	pt-online-schema-change	千万级表无损修改
批量操作	gh-ost	基于Binlog级联修改

---

四、验证协议

1. 元数据验证矩阵

-- 数据库级验证
SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME 
FROM information_schema.SCHEMATA 
WHERE SCHEMA_NAME = 'your_db';

-- 表级验证
SELECT 
  TABLE_NAME, 
  TABLE_COLLATION 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'your_db';

-- 列级验证
SELECT 
  TABLE_NAME, 
  COLUMN_NAME, 
  CHARACTER_SET_NAME, 
  COLLATION_NAME 
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = 'your_db'
  AND (CHARACTER_SET_NAME != 'utf8mb4' 
       OR COLLATION_NAME != 'utf8mb4_general_ci');

2. 数据完整性校验

# 对比新旧数据校验和
mysqldump --no-data your_db > schema_new.sql
md5sum full_backup.sql schema_new.sql

---

五、容灾回退预案

1. 快速回滚流程

graph LR A[发现异常] --> B{异常类型} B -->|数据损坏| C[恢复逻辑备份] B -->|配置错误| D[回滚配置文件] B -->|性能问题| E[降级字符集]

2. 灰度发布策略

阶段实施顺序：
1. 只读副本 -> 2. 缓存表 -> 3. 日志表 -> 4. 核心事务表

---

六、生产环境建议

1. 低峰期操作：选择业务低峰时段（如02:00-05:00）执行变更

2. 事务分割：每次修改不超过5个表，避免长事务锁表

3. 监控指标：

   - Threads_running > 100 时暂停操作
   - Lock_wait_timeout 设置为60秒
   - Innodb_row_lock_time_avg 超过500ms报警
   

4. 连接池刷新：修改后重启应用连接池强制加载新配置

---

通过该方案可确保字符集迁移零数据丢失，系统平均停机时间从小时级降低到分钟级。建议在执行过程中分批次操作并实时监控SHOW PROCESSLIST状态。