MySQL批量替換數據庫字符集的實用方法(附詳細代碼)

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

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

前言

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

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

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

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

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

整體腳本

-- 替換為你的數據庫名
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 語句。

1. 設置目標參數

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

@db_name：要操作的數據庫名。
@charset：目標字符集。這里我們指定為 utf8mb4。
@collation：排序規(guī)則，推薦使用 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 語句。
作用是修改表的默認字符集和排序規(guī)則，這樣以后新建字段時會自動使用指定的字符集。

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）。
僅在字段字符集或排序規(guī)則與目標不一致時才生成語句，避免重復修改。

使用步驟

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

示例輸出

假設數據庫 test_db 有一張 users 表，里面有一個 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 '用戶名';

結果1替換表的字符集，結果2替換字段的字符集

注意事項

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

總結

到此這篇關于MySQL批量替換數據庫字符集的實用方法的文章就介紹到這了,更多相關MySQL批量替換數據庫字符集內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

Linux系統(tǒng)利用crontab定時備份Mysql數據庫方法
本文教你如果快速利用系統(tǒng)crontab來定時執(zhí)行備份文件，按日期對備份結果進行保存
2021-09-09
MySql子查詢IN的執(zhí)行和優(yōu)化的實現(xiàn)
本文主要介紹了MySql子查詢IN的執(zhí)行和優(yōu)化的實現(xiàn)，詳細的介紹了為什么IN這么慢以及如何優(yōu)化，具有一定的參考價值，感興趣的可以了解一下
2021-07-07
MySQL5.7并行復制原理及實現(xiàn)
MySQL 5.7并行復制的思想簡單易懂，本文就詳細的介紹了MySQL5.7并行復制原理及實現(xiàn)，需要的朋友們下面隨著小編來一起學習學習吧
2021-06-06
MySQL 回表,覆蓋索引,索引下推
這篇文章主要介紹了MySQL 回表,覆蓋索引,索引下推,就是我們需要查詢的數據都在二級索引樹中，直接返回這種情況就叫做覆蓋索引
2022-07-07
linux Xtrabackup安裝及使用方法
Xtrabackup是一個對InnoDB做數據備份的工具，支持在線熱備份（備份時不影響數據讀寫），是商業(yè)備份工具InnoDB Hotbackup的一個很好的替代品
2013-04-04
幾個常見的MySQL的可優(yōu)化點歸納總結
這篇文章主要介紹了幾個常見的MySQL的可優(yōu)化點歸納總結,包括在編程時處理索引、分頁以及數據類型時可用到的地方,需要的朋友可以參考下
2015-05-05
MySql中怎樣查詢表是否被鎖
這篇文章主要介紹了MySql中怎樣查詢表是否被鎖問題，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教
2023-07-07
MySQL索引總結(Index?Type)
本文主要介紹了MySQL索引總結(Index?Type)，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧
2023-02-02
解決mysql數據庫導入sql文件不成功的問題
這篇文章主要介紹了解決mysql數據庫導入sql文件不成功的問題,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教
2023-11-11
MySQL回表的性能傷害程度有多大
這篇文章主要介紹了MySQL回表的性能傷害程度有多大?下面我們就帶著疑問進入下面文章了解詳細內容，需要的小伙伴可以參考一下，希望對你的學習有所幫助
2022-02-02