快捷導(dǎo)航

MySQL批量替換數(shù)據(jù)庫字符集的實(shí)用方法(附詳細(xì)代碼)

更新時(shí)間：2025年09月22日 11:26:01 作者：禹跡

當(dāng)需要修改數(shù)據(jù)庫編碼和字符集時(shí),通常需要對其下屬的所有表及表中所有字段進(jìn)行修改,下面這篇文章主要介紹了MySQL批量替換數(shù)據(jù)庫字符集的實(shí)用方法,文中通過代碼介紹的非常詳細(xì),需要的朋友可以參考下

前言

在日常的數(shù)據(jù)庫運(yùn)維或系統(tǒng)遷移過程中，我們經(jīng)常會遇到這樣的問題：

數(shù)據(jù)庫和表的字符集不統(tǒng)一，或者需要統(tǒng)一升級到更合適的字符集（例如 utf8mb4）以支持更多字符。

手動逐個(gè)表、逐個(gè)字段修改字符集不僅耗時(shí)，還容易遺漏。本文將通過一段 SQL 腳本，向大家介紹如何批量替換 MySQL 數(shù)據(jù)庫的字符集，從而簡化操作并降低風(fēng)險(xiǎn)。

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

統(tǒng)一性：確保所有表和字段的字符集一致，避免查詢或插入時(shí)出現(xiàn)亂碼。
兼容性：例如 utf8 在 MySQL 實(shí)際上只支持最多 3 字節(jié)，而 utf8mb4 才是真正的 UTF-8，可以支持 Emoji 等四字節(jié)字符。
可維護(hù)性：統(tǒng)一的標(biāo)準(zhǔn)字符集讓團(tuán)隊(duì)協(xié)作和后期維護(hù)更加方便。

整體腳本

-- 替換為你的數(shù)據(jù)庫名
SET @db_name = '你的數(shù)據(jù)庫名';
SET @charset = 'utf8mb4';
SET @collation = 'utf8mb4_unicode_520_ci';

-- 生成修改表默認(rèn)字符集的語句
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);

腳本邏輯解析

以下腳本分為兩部分，分別用于生成修改 表的默認(rèn)字符集 和 字段字符集 的 SQL 語句。

1. 設(shè)置目標(biāo)參數(shù)

-- 替換為你的數(shù)據(jù)庫名
SET @db_name = '你的數(shù)據(jù)庫名';
SET @charset = 'utf8mb4';
SET @collation = 'utf8mb4_unicode_520_ci';

@db_name：要操作的數(shù)據(jù)庫名。
@charset：目標(biāo)字符集。這里我們指定為 utf8mb4。
@collation：排序規(guī)則，推薦使用 utf8mb4_unicode_520_ci，兼容性和排序效果更好。

2. 生成修改表默認(rèn)字符集的語句

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 中讀取所有用戶表，并生成相應(yīng)的 ALTER TABLE 語句。
作用是修改表的默認(rèn)字符集和排序規(guī)則，這樣以后新建字段時(shí)會自動使用指定的字符集。

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）。
保留了默認(rèn)值（column_default）。
保留了字段注釋（column_comment）。
僅在字段字符集或排序規(guī)則與目標(biāo)不一致時(shí)才生成語句，避免重復(fù)修改。

使用步驟

替換數(shù)據(jù)庫名
將腳本中的 SET @db_name = '你的數(shù)據(jù)庫名'; 修改為實(shí)際要操作的數(shù)據(jù)庫名。
執(zhí)行腳本
在 MySQL 客戶端或工具（如 Navicat、DBeaver）中運(yùn)行以上 SQL。
復(fù)制結(jié)果并執(zhí)行
腳本本身不會直接修改數(shù)據(jù)庫，而是生成一批 ALTER 語句。
你需要將結(jié)果導(dǎo)出或復(fù)制出來，再次執(zhí)行這些 ALTER 語句，才能真正完成修改。

示例輸出

假設(shè)數(shù)據(jù)庫 test_db 有一張 users 表，里面有一個(gè) name 字段：

執(zhí)行腳本后可能會生成如下語句：

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 '用戶名';

結(jié)果1替換表的字符集，結(jié)果2替換字段的字符集

注意事項(xiàng)

備份數(shù)據(jù)：在批量修改前，一定要做好數(shù)據(jù)庫備份，以防萬一。
鎖表風(fēng)險(xiǎn)：ALTER TABLE 會對表加鎖，大表執(zhí)行時(shí)可能會阻塞業(yè)務(wù)，建議在業(yè)務(wù)低峰期操作。
兼容性驗(yàn)證：部分排序規(guī)則在 MySQL 版本之間可能有所差異，請確認(rèn)目標(biāo)環(huán)境支持。