【MySQL】一次擴充套件平臺引發的字符集調整薦

公司app（安卓）應用擴充套件ios平臺（安卓客戶端已經執行一年），由於ios自帶emoji表情字符集，api會出現問題，mysql資料庫更換utf8mb4，原字符集utf8。

utf8mb4和utf8到底有什麼區別呢？原來以往的mysql的utf8一個字元最多3位元組，而utf8mb4則擴充套件到一個字元最多能有4位元組，所以能支援更多的字符集。

主要思想匯出資料，重新建庫插入資料

1.檢視當前資料庫是否支援utf8mb4（貌似版本低於5.5.3的不能用，沒測試）

mysql> show charset like `utf8mb4`;
+---------+---------------+--------------------+--------+
| Charset | Description   | Default collation  | Maxlen |
+---------+---------------+--------------------+--------+
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci |      4 |  #<--支援utf8mb4字符集
+---------+---------------+--------------------+--------+
1 row in set (0.00 sec)

2.停庫方案（無法停庫可以鎖庫只讀）

PS：無法提供真實資料，以下只做模擬資料

當前mysql字符集

mysql> show variables like `character_set%`;
+--------------------------+-------------------------------------------+
| Variable_name        | Value                         |
+--------------------------+-------------------------------------------+
| character_set_client     | utf8                        |     #<--客戶端字符集
| character_set_connection  | utf8                        |     #<--連結字符集
| character_set_database   | utf8                         |     #<--資料庫字符集
| character_set_filesystem  | binary                       |
| character_set_results    | utf8                         |    #<--返回字符集
| character_set_server     | utf8                         |    #<--服務端字符集
| character_set_system     | utf8                         |
| character_sets_dir      | /application/mysql-5.5.32/share/charsets/ |
+--------------------------+-------------------------------------------+
8 rows in set (0.00 sec)

模擬庫和表

建立庫

mysql> create database app character set utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.00 sec)

mysql> show databases like `app`;
+----------------+
| Database (app) |
+----------------+
| app            |
+----------------+
1 row in set (0.00 sec)

mysql> show create database appG
*************************** 1. row ***************************
       Database: app
Create Database: CREATE DATABASE `app` /*!40100 DEFAULT CHARACTER SET utf8 */
1 row in set (0.00 sec)

建立表

隨意模擬兩張表

mysql> show create table userG
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `uid` int(4) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> show create table userclientG
*************************** 1. row ***************************
       Table: userclient
Create Table: CREATE TABLE `userclient` (
  `uid` int(4) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

模擬資料

mysql> insert into user(name) values(`evan`),(`cker`),(`niuniu`);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into userclient(name) values(`c_埃文`),(`c_瑟可`),(`c_牛牛`);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

3.匯出表結構並修改字符集

[root@oserr ~]# mysqldump -uroot -p --default-character-set=utf8 -d app >/opt/app_utf8.sql

–dafault-character-set=utf8 以utf8字符集匯出表結構，防止亂碼

修改表結構的字符集

[root@oserr ~]# sed -i `s@utf8@utf8mb4@g` /opt/app_utf8.sql

4.確保資料不更新，匯出所有資料

[root@oserr ~]# mysqldump -uroot -p --quick --no-create-info --extended-insert --default-character-set=utf8 app >/opt/appdata.sql

–quit           用於轉存大表使用

–no-create-info    不建立create table語句

–extended-insert    使用多行insert語法，減少io讀寫，和速度

–default-character-set=utf8 按照原有字符集匯出，防止亂碼

5.重新建庫

mysql> drop database app;
Query OK, 2 rows affected (0.05 sec)

mysql> show databases like `app`;
Empty set (0.00 sec)

mysql> create database app character set=utf8mb4 collate=utf8mb4_unicode_ci;
Query OK, 1 row affected (0.00 sec)

mysql> show create database appG
*************************** 1. row ***************************
       Database: app
Create Database: CREATE DATABASE `app` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */
1 row in set (0.00 sec)

6.匯入表結構和資料

修改客戶端字符集和庫一致

mysql> set names utf8mb4;       
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like `character_set%`;
+--------------------------+-------------------------------------------+
| Variable_name            | Value                                     |
+--------------------------+-------------------------------------------+
| character_set_client     | utf8mb4                                   |
| character_set_connection | utf8mb4                                   |
| character_set_database   | utf8                                      |
| character_set_filesystem | binary                                    |
| character_set_results    | utf8mb4                                   |
| character_set_server     | utf8                                      |
| character_set_system     | utf8                                      |
| character_sets_dir       | /application/mysql-5.5.32/share/charsets/ |
+--------------------------+-------------------------------------------+
8 rows in set (0.00 sec)

匯入表結構

mysql> use app;    #<--進入庫

mysql> source /opt/app_utf8.sql;  #<--執行備份的sql檔案

mysql> show tables;
+---------------+
| Tables_in_app |
+---------------+
| user          |
| userclient    |
+---------------+
2 rows in set (0.00 sec)

mysql> show create table userG
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `uid` int(4) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4       #<--字符集為utf8mb4
1 row in set (0.00 sec)
mysql> show create table userclientG
*************************** 1. row ***************************
       Table: userclient
Create Table: CREATE TABLE `userclient` (
  `uid` int(4) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

匯入資料

mysql> source /opt/appdata.sql;
......

查詢資料是否正常

mysql> select * from userclient;
+-----+----------+
| uid | name     |
+-----+----------+
|   1 | c_埃文   |
|   2 | c_瑟可   |
|   3 | c_牛牛   |
+-----+----------+
3 rows in set (0.00 sec)

mysql> select * from user;
+-----+--------+
| uid | name   |
+-----+--------+
|   1 | evan   |
|   2 | cker   |
|   3 | niuniu |
+-----+--------+
3 rows in set (0.00 sec)

匯入資料亂碼錯誤方法

mysql> set names gbk;
Query OK, 0 rows affected (0.00 sec)

mysql> use app;
Database changed

mysql> select * from userclient;
+-----+--------+
| uid | name   |
+-----+--------+
|   1 | c_°￡τ     |
|   2 | c_   |
|   3 | c_   |
+-----+--------+
3 rows in set (0.00 sec)

上述原因客戶端和庫表字符集不一樣

mysql> show variables like `character_set%`;
+--------------------------+-------------------------------------------+
| Variable_name            | Value                                     |
+--------------------------+-------------------------------------------+
| character_set_client     | gbk                                       |
| character_set_connection | gbk                                       |
| character_set_database   | utf8mb4                                   |
| character_set_filesystem | binary                                    |
| character_set_results    | gbk                                       |
| character_set_server     | utf8                                      |
| character_set_system     | utf8                                      |
| character_sets_dir       | /application/mysql-5.5.32/share/charsets/ |
+--------------------------+-------------------------------------------+

7.服務端預設字元處理（如非必要可以不用處理，只要保證【客戶端，和庫表】字元一致即可）

客戶端 

臨時生效 （set names 字符集）

永久生效 更改配置檔案my.cnf的[client]模組

[client]

default-character-set=字符集     #<–新增這一條語句，無需重啟服務即可

服務端

更改配置檔案my.cnf的[mysqld]模組

[mysqld]

default-character-set=字符集  適合5.1及以前版本

character-set-server=字符集適合5.5