MySQL修改字符集

MySQL迁移乱码处理记录过程

 


MySQL迁移乱码

一、字符集

1.1字符集常识

  • 查看目前使用字符集
mysql> show variables like 'character%';
+--------------------------+----------------------------+
| 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       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

mysql> 
  • 查看mysql支持的所有字符集
mysql> show character set;
+----------+-----------------------------+---------------------+--------+
| Charset  | Description                 | Default collation   | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese    | big5_chinese_ci     |      2 |
| dec8     | DEC West European           | dec8_swedish_ci     |      1 |
| cp850    | DOS West European           | cp850_general_ci    |      1 |
| hp8      | HP West European            | hp8_english_ci      |      1 |
| koi8r    | KOI8-R Relcom Russian       | koi8r_general_ci    |      1 |
| latin1   | cp1252 West European        | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European | latin2_general_ci   |      1 |
| swe7     | 7bit Swedish                | swe7_swedish_ci     |      1 |
| ascii    | US ASCII                    | ascii_general_ci    |      1 |
| ujis     | EUC-JP Japanese             | ujis_japanese_ci    |      3 |
| sjis     | Shift-JIS Japanese          | sjis_japanese_ci    |      2 |
| hebrew   | ISO 8859-8 Hebrew           | hebrew_general_ci   |      1 |
| tis620   | TIS620 Thai                 | tis620_thai_ci      |      1 |
| euckr    | EUC-KR Korean               | euckr_korean_ci     |      2 |
| koi8u    | KOI8-U Ukrainian            | koi8u_general_ci    |      1 |
| gb2312   | GB2312 Simplified Chinese   | gb2312_chinese_ci   |      2 |
| greek    | ISO 8859-7 Greek            | greek_general_ci    |      1 |
| cp1250   | Windows Central European    | cp1250_general_ci   |      1 |
| gbk      | GBK Simplified Chinese      | gbk_chinese_ci      |      2 |
| latin5   | ISO 8859-9 Turkish          | latin5_turkish_ci   |      1 |
| armscii8 | ARMSCII-8 Armenian          | armscii8_general_ci |      1 |
| utf8     | UTF-8 Unicode               | utf8_general_ci     |      3 |
| ucs2     | UCS-2 Unicode               | ucs2_general_ci     |      2 |
| cp866    | DOS Russian                 | cp866_general_ci    |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak  | keybcs2_general_ci  |      1 |
| macce    | Mac Central European        | macce_general_ci    |      1 |
| macroman | Mac West European           | macroman_general_ci |      1 |
| cp852    | DOS Central European        | cp852_general_ci    |      1 |
| latin7   | ISO 8859-13 Baltic          | latin7_general_ci   |      1 |
| utf8mb4  | UTF-8 Unicode               | utf8mb4_general_ci  |      4 |
| cp1251   | Windows Cyrillic            | cp1251_general_ci   |      1 |
| utf16    | UTF-16 Unicode              | utf16_general_ci    |      4 |
| utf16le  | UTF-16LE Unicode            | utf16le_general_ci  |      4 |
| cp1256   | Windows Arabic              | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic              | cp1257_general_ci   |      1 |
| utf32    | UTF-32 Unicode              | utf32_general_ci    |      4 |
| binary   | Binary pseudo charset       | binary              |      1 |
| geostd8  | GEOSTD8 Georgian            | geostd8_general_ci  |      1 |
| cp932    | SJIS for Windows Japanese   | cp932_japanese_ci   |      2 |
| eucjpms  | UJIS for Windows Japanese   | eucjpms_japanese_ci |      3 |
| gb18030  |                             | gb18030_chinese_ci  |      4 |
+----------+-----------------------------+---------------------+--------+
41 rows in set (0.00 sec)

mysql> 
  • 查看数据库表所有字段的状态
mysql> show full columns from test01;
+-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type        | Collation       | Null | Key | Default | Extra | Privileges                      | Comment |
+-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| id    | int(10)     | NULL            | YES  |     | NULL    |       | select,insert,update,references |         |
| city  | varchar(20) | utf8_general_ci | YES  |     | NULL    |       | select,insert,update,references |         |
+-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
2 rows in set (0.00 sec)

mysql> 

1.2常见字符集

  1. utf8 --中英文混合环境      长度:3(每个汉字三字节)
  2. utf8mb4 --UTF-8 uniconde   长度:4
  3. gbk --不是国际标准        长度:2
  4. latin1(默认字符集)            长度:1      [ˈlætɪn]

1.3utf8和utf8mb4区别

       MySQL在5.5.3之后增加了这个utf8mb4的编码,mb4就是most bytes 4的意思,专门用来兼容四字节的unicode。好在utf8mb4是utf8的超集,除了将编码改为utf8mb4外不需要做其他转换。为了节省空间,一般情况下使用utf8也就够了。

1.4如何选择字符集

  1. 如果处理各种各样的文字,发布到不同语言国家地区,应选Unicode字符集,对MySQL来说就是utf8(每个汉字三个字节)
  2. 如果只是需要支持中文,并且数据量很大,性能要求也高,可选GBK(定长,每个汉字占双字节,英文也占双字节),如果是大量运算,比较排序等,定长字符集更快,性能也高
  3. 处理移动互联网业务,可能需要使用utf8mb4字符集

字符集错乱-数据迁移的过程 

2.1解决迁移乱码方案

  1. 备份数据库
  2. 导出表结构,sed将字符集批量修改为utf8  
  3. 导出表数据
  4. 删除之前的库、表以及数据
  5. 将服务器和客户端编码修改为utf8,重启数据库
  6. 导入建库和建表语句
  7. 导入数据库数据

2.2部分参数说明

  1. --quick:用于转储大的表,强制mysqldump从服务器一次一行的检索数据而不是检索所有行,并输出当前cache到内存中
  2. --no-create-info:不要创建create table语句
  3. --extended-insert:使用包括几个values列表的多行insert语法,这样文件更小,IO也小,导入数据时会非常快
  4. --default-character-set=latin1:按照原有字符集导出数据,这样导出的文件中,所有中文都是可见的,不会保存成乱码
  5. -d表示只导出表结构,不会导出表数据
  6. -B表示将整个库导出,没法精确到表

       注意:选择目标字符集时,要注意最好大于等于原字符集(子库更大),否则可能会丢失不被支持的数据

三、生产案例:修正字符集

3.1创建模拟数据

  • 前期准备:安装数据库,建库建表
[root@localhost ~]# mysql -uroot -p123456
mysql> create database test01;
mysql> show create database test01;
mysql> use test01;
mysql> create table test01( id int(10), city varchar(20) );

3.1.1模拟生产错误案例:

  • 生产系统已经在运行,对于系统注册页面,一开始注册信息为拼音字母,新功能上线后,可以使用汉字,但是在使用过程中却因为报错无法进行。

插入英文字符(显示成功):

mysql> insert into test01 values (1,'gz'), (2,'nn'), (3,'sh'), (4,'bj');

插入中文字符(报错HY000):

mysql> insert into test01 values (1,'广州'), (2,'南宁'), (3,'上海'), (4,'北京'); 
ERROR 1366 (HY000): 
mysql> select * from test01;
+------+------+
| id   | city |
+------+------+
|    1 | gz   |
|    2 | nn   |
|    3 | sh   |
|    4 | bj   |
+------+------+

3.1.2分析错因

  • 报错原因:因为MySQL默认字符集为latin1,不支持中文编码,所以此时需要进行转换字符集

3.1.2创建对比表:

mysql>  create table test02( id int(10), city varchar(20) )engine=innodb default charset=utf8;  
mysql>  insert into test02 values (1,'广州'), (2,'南宁'), (3,'上海'), (4,'北京');
mysql>  select * from test02;
+------+--------+
| id   | city   |
+------+--------+
|    1 | 广州   |
|    2 | 南宁   |
|    3 | 上海   |
|    4 | 北京   |
+------+--------+
4 rows in set (0.00 sec)

3.2解决乱码

  • 迁移工具:mysqldump
  • 语法格式(八大部分组成):

mysqldump  -h localhost  -uroot  -p123456  -[option]  db_name  table_name >backup.sql

注意:如果不加上table_name会默认将整个库的表导出来,以下实验是针对库进行,所以涉及的是整个库(所有的表)

3.2.1备份数据

  • (备份当前准备修改的库,全库备份)--最重要的一步!!!
[root@localhost ~]# mysqldump -uroot -p123456 test01 >test01_backup0224.sql

3.2.2导出表结构,并修正字符集

  • -d表示只导出表结构,不会导出表数据,常用
[root@localhost ~]# mysqldump -uroot -p123456 -hlocalhost  --default-character-set=latin1 -d test01 >test01_table.sql 
[root@localhost ~]# sed -i 's/latin1/utf8/g' test01_table.sql  

3.2.3导出表数据

  • 数据库不在更新的情况下
[root@localhost ~]# mysqldump -uroot -p123456 -hlocalhost --quick --no-create-info --extended-insert --default-character-set=latin1 test01 >test01_data.sql

补充:同时导出表结构和表数据:

  • -B表示将整个库导出,没法精确到表
mysqldump -uroot -p123456 -hlocalhost -B test01>xxx.sql
  • 如果不加-B选项也是默认导出表结构和数据,但是不加选项可以选择到表
mysqldump -h localhost -uroot -p123456 test01 test01 >xxx.sql

3.2.4修改mysql服务和client的字符集

[root@localhost ~]# vim /etc/my.cnf
在[mysqld]下加入代码:character_set_server=utf8
在[client]下加入代码:default-character-set=UTF8
[root@localhost ~]# systemctl restart mysqld

3.2.5删除原数据库

[root@localhost ~]# mysql -uroot -p123456
mysql> drop database test01;

3.2.6建库、导入表结构和表数据

依下顺序

  • a.建库:
mysql> create database test01; --已经设置默认为utf-8
或是:
mysql> create database test01 default character set=utf8;
或是:
mysql> create database test01 default charset=utf8;
mysql> show create database test01;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| test01   | CREATE DATABASE `test01` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-----------------------------------------------------------------+
mysql> exit
  • b.导入表结构:
[root@localhost ~]# mysql  -uroot -p123456 test01<test01_table.sql 
  • c.导入表数据:
[root@localhost ~]# mysql  -uroot -p123456 test01<test01_data.sql

3.3登录验证test02

3.3.1test02乱码

mysql> select * from test02;
+------+------+
| id   | city |
+------+------+
|    1 | ??   |
|    2 | ??   |
|    3 | ??   |
|    4 | ??   |
+------+------+

反思:为什么test02反而乱码了呢???

3.3.2分析test02乱码

  • 插入中文试验:
mysql> mysql>  insert into test02 values (1,'广州'), (2,'南宁'), (3,'上海'), (4,' 北京');   
mysql> select * from test02;
+------+--------+
| id   | city   |
+------+--------+
|    1 | ??     |
|    2 | ??     |
|    3 | ??     |
|    4 | ??     |
|    1 | 广州   |
|    2 | 南宁   |
|    3 | 上海   |
|    4 | 北京   |
+------+--------+

3.3.3test02乱码原因

在导出数据的时候指定了字符集为Latin1,而test02本身是utf-8的所以导出后再导入就乱码了。

检查导出文本:

MySQL修改字符集

小结:所以综合考虑,一开始进行全库备份是非常有必要的。另外,防止出现这种情况,在导出数据的时候可以单表导出(只导出乱码的表),单表修改,尽量不做全库导出。

3.4登录验证test01

3.4.1在test01插入中文验证

mysql>  insert into test01 values (1,'广州'), (2,'南宁'), (3,'上海'), (4,'北京'); Query OK, 4 rows affected (0.00 sec)
mysql> select * from test01;
+------+--------+
| id   | city   |
+------+--------+
|    1 | gz     |
|    2 | nn     |
|    3 | sh     |
|    4 | bj     |
|    1 | 广州   |
|    2 | 南宁   |
|    3 | 上海   |
|    4 | 北京   |
+------+--------+

综上,test01已经正常显示中文,至此,MySQL迁移中文乱码问题得以解决


 

原创帖,转载需注明出处

 

上一篇:启用mysql日志,记录执行过的sql


下一篇:mysql日志