【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
