MySQL数据库表批量转换为utf8mb4字符集完整指南

一、为什么需要迁移到utf8mb4？

1.1 四字节字符支持

utf8mb4字符集完整支持四字节Unicode编码（如emoji表情符号），而传统的utf8仅支持三字节。在移动互联网时代，用户内容中包含表情符号的比例超过73%（根据2023年数据库统计报告）。

1.2 数据存储完整性

当使用utf8字符集时，插入四字节字符会导致：

ERROR 1366 (HY000): Incorrect string value

采用utf8mb4可避免数据截断，保障存储内容的完整性。

1.3 兼容性优势

主流框架（如Spring Boot 3+、Laravel 10+）已默认使用utf8mb4，MySQL 8.0+也将它作为默认字符集。

二、详细操作步骤

2.1 环境预检

-- 查看数据库字符集
SELECT schema_name, default_character_set_name 
FROM information_schema.schemata;

-- 查看表字符集
SELECT table_schema, table_name, table_collation 
FROM information_schema.tables 
WHERE table_schema NOT IN ('information_schema','mysql','performance_schema');

-- 查看列字符集
SELECT table_schema, table_name, column_name, character_set_name, collation_name
FROM information_schema.columns
WHERE data_type IN ('varchar','text','mediumtext','longtext');

2.2 生成转换脚本

SELECT 
  CONCAT(
    'ALTER TABLE `', table_schema, '`.`', table_name, '` ',
    'CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, ',
    'MODIFY `', column_name, '` ', column_type, ' CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci',
    (CASE WHEN is_nullable = 'NO' THEN ' NOT NULL' ELSE '' END),
    (CASE WHEN column_default IS NOT NULL THEN CONCAT(' DEFAULT ', QUOTE(column_default)) ELSE '' END),
    ';'
  ) AS alter_sql
FROM information_schema.columns
WHERE table_schema = 'your_database'
AND data_type IN ('char','varchar','text','tinytext','mediumtext','longtext');

2.3 索引优化处理

-- 检查可能超限的索引
SELECT 
  table_name,
  index_name,
  column_name,
  character_maximum_length,
  CASE 
    WHEN character_maximum_length * 4 > 767 THEN '需要调整'
    ELSE '正常'
  END AS status
FROM information_schema.statistics
WHERE table_schema = 'your_database';

2.4 执行批量转换

推荐使用Python自动化脚本：

import pymysql

config = {
    'host': 'localhost',
    'user': 'root',
    'password': 'your_password',
    'database': 'your_db',
    'charset': 'utf8mb4'
}

conn = pymysql.connect(**config)

with conn.cursor() as cursor:
    # 生成修改语句
    cursor.execute("""
        SELECT CONCAT(
            'ALTER TABLE `', table_schema, '`.`', table_name, '` ',
            'CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;'
        ) 
        FROM information_schema.tables 
        WHERE table_schema = %s
    """, (config['database'],))
    
    # 分批次执行
    batch_size = 10
    alters = [row[0] for row in cursor.fetchall()]
    for i in range(0, len(alters), batch_size):
        batch = alters[i:i+batch_size]
        try:
            for sql in batch:
                cursor.execute(sql)
            conn.commit()
        except Exception as e:
            print(f"Error executing: {sql}")
            print(e)
            conn.rollback()

conn.close()

三、高级配置参数

3.1 my.cnf优化

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
innodb_file_format = Barracuda
innodb_file_per_table = 1
innodb_large_prefix = 1