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

本文主要是介绍MySQL批量替换数据库字符集的实用方法(附详细代码)，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

《MySQL批量替换数据库字符集的实用方法(附详细代码)》当需要修改数据库编码和字符集时,通常需要对其下属的所有表及表中所有字段进行修改,下面：本文主要介绍MySQL批量替换数据库字符集的实用方法...

前言

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

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

手动逐个表、逐个字段修改字符集不仅耗时，还容易遗漏。本文将通过一段 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_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.chinasem.cna_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）。
仅在字段字符集或排序规则与目标不一致时才生成语句，避免重复修改。

使用步骤

替换数据库名
将脚本中的 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_uni编程code_520_ci NOT NULL 
COMMENT '用户名';

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

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