【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 app\G

*************************** 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 user\G

*************************** 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 userclient\G

*************************** 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 app\G

*************************** 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 user\G

*************************** 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 userclient\G

*************************** 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

©著作权归作者所有：来自51CTO博客作者Evanchn的原创作品，如需转载，请注明出处，否则将追究法律责任

mysql数据库客户端