MySQL批量替换数据库字符集的实用方法(附详细代码)

目录

• 前言
• 为什么要批量修改字符集？
• 整体脚本
• 脚本逻辑解析
• 1. 设置目标参数
• 2. 生成修改表默认字符集的语句
• 3. 生成修改所有字符串字段的语句
• 使用步骤
• 示例输出
• 注意事项
• 总结 

前言

在日常的数据库运维或系统迁移过程中，我们经常会遇到这样的问题：

数据库和表的字符集不统一，或者需要统一升级到更合适的字符集（例如 utf8mb4）以支持更多字符。

手动逐个表、逐个字段修改字符集不仅耗时，还容易遗漏。本文将通过一段 SQL 脚本，向大家介绍如何批量替换 mysql 数据库的字符集，从而简化操作并降低风险。

为什么要批量修改字符集？

1. 统一性：确保所有表和字段的字符集一致，避免查询或插入时出现乱码。
2. 兼容性：例如 utf8 在 MySQL 实际上只支持最多 3 字节，而 utf8mb4 才是真正的 UTF-8，可以支持 Emoji 等四字节字符。
3. 可维护性：统一的标准字符集让团队协作和后期维护更加方便。

整体脚本

-- 替换为你的数据库名
SET @db_name = '你的数据库名';
SET @charset = 'utf8mb4';
SET @collation = 'utf8mb4_unicode_520_ci';

-- 生成修改表默认字符集的语句
SELECT CONCAT(
    'ALTER TABLE `', table_name, '` DEFAULT CHARACTER SET ', @charset, ' COLLATE ', @collation, ';'
) AS alter_taandroidble_sql
FROM information_schema.tables 
WHERE table_schema = @db_name 
  AND table_type = 'BASE TABLE'; -- 只处理用户表，排除视图等

-- 生成修改所有字符串字段的语句
SELECT CONCAT(
    'ALTER TABLE `', c.table_name, '` MODIFY COLUMN `', c.column_name, '` ',
    c.data_type, 
    IF(c.character_maximum_length IS NOT NULL, CONCAT('(', c.character_maximum_length, ')'), ''),
    ' CHARACTER SET ', @charset, ' COLLATE ', @collation,
    IF(c.is_nullable = 'NO', ' NOT NULL', ' NULL'),
    IF(c.column_default IS NOT NULL, CONCAT(' DEFAULT ', QUOTE(c.column_default)), ''),
    ' COMMENT ', QUOTE(c.column_comment), ';'
) AS alter_column_sql
FROM information_schema.columns c
JOIN information_schema.tables t ON c.table_name = t.table_name AND c.table_schema = t.table_schema
WHERE c.table_schema = @db_name
  AND t.table_type = 'BASE TABLE'
  AND c.dathttp://www.devze.coma_type IN ('varchar', 'char', 'text', 'tinytext', 'mediumtext', 'longtext') -- 所有字符串类型
  AND (c.character_set_name IS NULL OR c.character_set_name != @charset OR c.collation_name != @collation);

脚本逻辑解析

以下脚本分为两部分，分别用于生成修改 表的默认字符集 和 字段字符集 的 SQL 语句。

1. 设置目标参数

-- 替换为你的数据库名
SET @db_name = '你的数据库名';
SET @charset = 'utf8mb4';
SET @collation = 'utf8mb4_unicode_520_ci';

• @db_name：要操作的数据库名。
• @charset：目标字符集。这里我们指定为 utf8mb4。
• @collation：排序规则，推荐使用 utf8mb4_unicode_520_ci，兼容性和排序效果更好。

2. 生成修改表默认字符集的语句

SELECT CONCAT(
    'ALTER TABLnmKiWsLPlE `', table_name, '` DEFAULT CHARACTER SET ', @charset, ' COLLATE ', @collation, ';'
) AS alter_table_sql
FROM information_schema.tables 
WHERE table_schema = @db_name 
  AND table_type = 'BASE TABLE'; -- 只处理用户表，排除视图等

这段 SQL 会从 information_schema.tables 中读取所有用户表，并生成相应的 ALTER TABLE 语句。

作用是修改表的默认字符集和排序规则，这样以后新建字段时会自动使用指定的字符集。

3. 生成修改所有字符串字段的语句

SELECT CONCAT(
    'ALTER TABLE `', c.table_name, '` MODIFY COLUMN `', c.column_name, '` ',
    c.data_type, 
    IF(c.character_maximum_length IS NOT NULL, CONCAT('(', c.character_maximum_length, ')'), ''),
    ' CHARACTER SET ', @charset, ' COLLATE ', @collation,
    IF(c.is_nullable = 'NO', ' NOT NULL', ' NULL'),
    IF(c.column_default IS NOT NULL, CONCAT(' DEFAULT ', QUOTE(c.column_default)), ''),
    ' COMMENT ', QUOTE(c.column_comment), ';'
) AS alter_column_sql
FROM information_schema.columns c
JOIN information_schema.tables t ON c.table_name = t.table_name AND c.table_schema = t.table_schema
WHERE c.table_schema = @db_name
  AND t.table_type = 'BASE TABLE'
  AND c.data_type IN ('varchar', 'char', 'text', 'tinytext', 'mediumtext', 'longtext') -- 所有字符串类型
  AND (c.character_set_name IS NULL OR c.character_set_name != @charset OR c.collation_name != @collation);

这段 SQL 主要针对已有的字符串字段，逐一生成 ALTER TABLE ... MODIFY COLUMN 语句：

• 只选择了 字符串类型字段（varchar, char, text 等）。
• 保留了原有的字段长度（character_maximum_length）。
• 保留了字段是否可为空（is_nullable）。
• 保留了默认值（column_default）。
• 保留了字段注释（column_comment）。
• 仅在字段字符集或排序规则与目标不一致时才生成语句，避免重复修改。

使用步骤

1. 替换数据库名

   将脚本中的 SET @db_name = '你的数据库名'; 修改为实际要操作的数据库名。

2. 执行脚本

   在 MySQL 客户端或工具（如 Navicat、DBeaver）中运行以上 SQL。

3. 复制结果并执行

   脚本本身不会直接修改数据库，而是生成一批 ALTER 语句。

   你需要将结果导出或复制出来，再次执行这些 ALTER 语句，才能真正完成修改。

示例输出

假设数据库 test_db 有一张 users 表，里面有一个 name 字段：

执行脚本后可能会生成如下语句：

ALTER TABLE `users` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;

ALTER TABLE `users` MODIFY COLUMN `name` varchar(255) 
CHARACTER SET utf8mb4 COLLATE utf8mb4_uni编程code_520_ci NOT NULL 
COMMENT '用户名';

结果1替换表的字符集，结果2替换字段的字符集

注意事项

1. 备份数据：在批量修改前，一定要做好数据库备份，以防万一。
2. 锁表风险：ALTER TABLE 会对表加锁，大表执行时可能会阻塞业务，建议在业务低峰期操作。
3. 兼容性验证：部分排序规则在 MySQL 版本之间可能有所差异，请确认目标环境支php持。

总结 

到此这篇关于MySQL批量替换数据库字符集的实用方法的文章就介绍到这了,更多相关MySQL批量替换数据库字符集内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！