MySQL修改字符集

2022-11-11 10:24:46

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常见字符集

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

1.3utf8和utf8mb4区别

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

1.4如何选择字符集

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

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

2.1解决迁移乱码方案

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

2.2部分参数说明

--quick:用于转储大的表，强制mysqldump从服务器一次一行的检索数据而不是检索所有行，并输出当前cache到内存中
--no-create-info:不要创建create table语句
--extended-insert:使用包括几个values列表的多行insert语法，这样文件更小，IO也小，导入数据时会非常快
--default-character-set=latin1:按照原有字符集导出数据，这样导出的文件中，所有中文都是可见的，不会保存成乱码
-d表示只导出表结构，不会导出表数据
-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的所以导出后再导入就乱码了。

检查导出文本：

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

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修改字符集

一、字符集

1.1字符集常识

1.2常见字符集

1.3utf8和utf8mb4区别

1.4如何选择字符集

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

2.1解决迁移乱码方案

2.2部分参数说明

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

3.1创建模拟数据

3.2解决乱码

3.3登录验证test02

3.4登录验证test01

原创帖，转载需注明出处

码农公寓

一、字符集

1.1字符集常识

1.2常见字符集

1.3utf8和utf8mb4区别

1.4如何选择字符集

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

2.1解决迁移乱码方案

2.2部分参数说明

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

3.1创建模拟数据

3.2解决乱码

3.3登录验证test02

3.4登录验证test01

原创帖，转载需注明出处

相关文章