作者：胡呈清

整理 MySQL 8.0 文檔時(shí)發(fā)現(xiàn)一個(gè)變更：

默認(rèn)字符集由 latin1 變?yōu)?utf8mb4。想起以前整理過字符集轉(zhuǎn)換文檔，升級到 MySQL 8.0 后大概率會有字符集轉(zhuǎn)換的需求，在此正好分享一下。

當(dāng)時(shí)的需求背景是：

部分系統(tǒng)使用的字符集是 utf8，但 utf8 最多只能存 3 字節(jié)長度的字符，不能存放 4 字節(jié)的生僻字或者表情符號，因此打算遷移到 utf8mb4。

遷移方案一

1. 準(zhǔn)備新的數(shù)據(jù)庫實(shí)例，修改以下參數(shù)：

[mysqld]## Character Settingsinit_connect='SET NAMES utf8mb4'#連接建立時(shí)執(zhí)行設(shè)置的語句，對super權(quán)限用戶無效character-set-server = utf8mb4collation-server = utf8mb4_general_ci#設(shè)置服務(wù)端校驗(yàn)規(guī)則，如果字符串需要區(qū)分大小寫，設(shè)置為utf8mb4_binskip-character-set-client-handshake#忽略應(yīng)用連接自己設(shè)置的字符編碼，保持與全局設(shè)置一致## Innodb Settingsinnodb_file_format = Barracudainnodb_file_format_max = Barracudainnodb_file_per_table = 1innodb_large_prefix = ON#允許索引的最大字節(jié)數(shù)為3072（不開啟則最大為767字節(jié)，對于類似varchar(255)字段的索引會有問題，因?yàn)?55*4大于767）

2. 停止應(yīng)用，觀察，確認(rèn)不再有數(shù)據(jù)寫入

可通過 show master status 觀察 GTID 或者 binlog position，沒有變化則沒有寫入。

3. 導(dǎo)出數(shù)據(jù)

先導(dǎo)出表結(jié)構(gòu)：

mysqldump -u -p --no-data --default-character-set=utf8mb4 --single-transaction --set-gtid-purged=OFF --databases testdb > /backup/testdb.sql

后導(dǎo)出數(shù)據(jù)：

mysqldump -u -p --no-create-info --master-data=2 --flush-logs --routines --events --triggers --default-character-set=utf8mb4 --single-transaction --set-gtid-purged=OFF --database testdb > /backup/testdata.sql

4. 修改建表語句

修改導(dǎo)出的表結(jié)構(gòu)文件，將表、列定義中的 utf8 改為 utf8mb4

5. 導(dǎo)入數(shù)據(jù)

先導(dǎo)入表結(jié)構(gòu)：

mysql -u -p testdb < /backup/testdb.sql

后導(dǎo)入數(shù)據(jù)：

mysql -u -p testdb < /backup/testdata.sql

6. 建用戶

查出舊環(huán)境的數(shù)據(jù)庫用戶，在新數(shù)據(jù)庫中創(chuàng)建

7. 修改新數(shù)據(jù)庫端口，啟動應(yīng)用進(jìn)行測試

關(guān)閉舊數(shù)據(jù)庫，修改新數(shù)據(jù)庫端口重啟，啟動應(yīng)用

遷移方案二

1. 修改表的字符編碼會鎖表，建議先停止應(yīng)用

2. 停止 mysql，備份數(shù)據(jù)目錄（也可以其他方式進(jìn)行全備）

3. 修改配置文件，重啟數(shù)據(jù)庫

[mysqld]## Character Settingsinit_connect='SET NAMES utf8mb4'#連接建立時(shí)執(zhí)行設(shè)置的語句，對super權(quán)限用戶無效character-set-server = utf8mb4collation-server = utf8mb4_general_ci#設(shè)置服務(wù)端校驗(yàn)規(guī)則，如果字符串需要區(qū)分大小寫，設(shè)置為utf8mb4_binskip-character-set-client-handshake#忽略應(yīng)用連接自己設(shè)置的字符編碼，保持與全局設(shè)置一致## Innodb Settingsinnodb_file_format = Barracudainnodb_file_format_max = Barracudainnodb_file_per_table = 1innodb_large_prefix = ON#允許索引的最大字節(jié)數(shù)為3072（不開啟則最大為767字節(jié)，對于類似varchar(255) 字段的索引會有問題，因?yàn)?55*4大于767）

4. 查看所有表結(jié)構(gòu)，包括字段、修改庫和表結(jié)構(gòu)，如果字段有定義字符編碼，也需要修改字段屬性，sql 語句如下：

修改表的校對規(guī)則：

alter table t convert to character set utf8mb4;

影響：拷貝全表，速度慢，會加鎖，阻塞寫操作

修改字段的校對規(guī)則（utfmb4 每字符占 4 字節(jié)，注意字段類型的最大字節(jié)數(shù)與字符長度關(guān)系）：

alter table t modify a char CHARACTER SET utf8mb4;

影響：拷貝全表，速度慢，會加鎖，阻塞寫操作

修改 database 的校對規(guī)則：

alter database sbtest CHARACTER SET utf8mb4;

影響：只需修改元數(shù)據(jù)，速度很快

5. 修改 JDBC url haracterEncoding=utf-8