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

IT 文章5分钟前更新小编

0 0 0

本文主要讲解关于MySQL批量替换数据库字符集的实用方法(附详细代码)相关内容，由优网导航（www.uonce.com）提供，欢迎关注收藏本站！

前言

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

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

程序员导航

优网导航旗下整合全网优质开发资源，一站式IT编程学习与工具大全网站

立即访问

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

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

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

整体脚本

-- 替换为你的数据库名
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_table_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.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 语句。

AI 工具导航

优网导航旗下AI工具导航，精选全球千款优质 AI 工具集

立即访问

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 TABLE `', 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）。
仅在字段字符集或排序规则与目标不一致时才生成语句，避免重复修改。

使用步骤

替换数据库名将脚本中的 SET @db_name = '你的数据库名'; 修改为实际要操作的数据库名。
执行脚本在 MySQL 客户端或工具（如 Navicat、DBeaver）中运行以上 SQL。
免费在线工具导航
优网导航旗下整合全网优质免费、免注册的在线工具导航大全
立即访问
复制结果并执行脚本本身不会直接修改数据库，而是生成一批 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_unicode_520_ci NOT NULL 
COMMENT '用户名';

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

注意事项

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

总结

到此这篇关于MySQL批量替换数据库字符集的实用方法的文章就介绍到这了,更多相关MySQL批量替换数据库字符集内容请搜索优网导航以前的文章或继续浏览下面的相关文章希望大家以后多多支持优网导航！

您可能感兴趣的文章:

PHP 设置MySQL连接字符集的方法
修改mysql默认字符集的两种方法详细解析
深入Mysql字符集设置分析
深入Mysql字符集设置[精华结合]
带你5分钟读懂MySQL字符集设置
Mysql5 字符集编码问题解决
mysql字符集乱码问题解决方法介绍

相关推荐: Prometheus运维实战：如何接入AlertManager配置邮件告警

如果大家还没搞定AlertManager与Prometheus的对接，要么看看其他公开教程，要么参考我之前写的部署文档——先把基础环境搭好，后面的配置才好推进。另外，要是这篇文章帮到你了，麻烦点个赞、转个发，多谢老铁们支持！ 1 配置Prometheus监控告…

# IT 文章