linux mysql导出乱码问题_MySQL数据导入导出乱码问题-阿里云开发者社区

使用mysqlclient，--default-character-set='latin1' 这个选测进行测试，看看他到底改变了那些字符集，如下图所示

[root@5kh4z42 goufu]# mysql -u superdba -padmin -S /tmp/mysql3443.sock -e 'show variables like "%char%"';

+--------------------------+----------------------------------------------+

| 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/local/xywy/mysql-5.5.38/share/charsets/ |

+--------------------------+----------------------------------------------+

[root@5kh4z42 goufu]# mysql -u superdba -padmin -S /tmp/mysql3443.sock --default-character-set='latin1' -e 'show variables like "%char%"';

+--------------------------+----------------------------------------------+

| Variable_name | Value |

+--------------------------+----------------------------------------------+

| character_set_client | latin1 |

| character_set_connection | latin1 |

| character_set_database | utf8 |

| character_set_filesystem | binary |

| character_set_results | latin1 |

| character_set_server | utf8 |

| character_set_system | utf8 |

| character_sets_dir | /usr/local/xywy/mysql-5.5.38/share/charsets/ |

+--------------------------+----------------------------------------------+

这里可以看出具体的client和connection与results是取决于client的，默认是和文件系统统一的

创建一张latin1字符集的表进行测试，看看mysql如何对字符集进行转换，老的或者奇葩业务如何使用latin1存储中文

CREATE TABLE `aa` (

`id` int(11) DEFAULT NULL,

`name` varchar(100) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1

插入测试数据

终端切换为UTF8字符集，执行命令

mysql -u superdba -padmin -S /tmp/mysql.sock --default-character-set='latin1' -e 'insert into test.aa values(1,"啊啊")';

终端切换为GBK字符集，执行命令

mysql -u superdba -padmin -S /tmp/mysql.sock --default-character-set='latin1' -e 'insert into test.aa values(2,"啊啊")';

终端切换为UTF8字符集，执行命令

mysql -u superdba -padmin -S /tmp/mysql.sock -e 'insert into test.aa values(3,"啊啊")';

终端切换为GBK字符集，执行命令

mysql -u superdba -padmin -S /tmp/mysql.sock -e 'insert into test.aa values(4,"啊啊")';

查询数据

终端切换为UTF8字符集，执行命令

[root@5kh4z42 goufu]# mysql -u superdba -padmin -S /tmp/mysql3443.sock --default-character-set='latin1' -e 'select * from test.aa';

+------+--------+

| id | name |

+------+--------+

| 1 | 啊啊 |

| 2 | °¡°¡ |

| 3 | ???? |

| 4 | ?? |

+------+--------+

终端切换为GBK字符集，执行命令

[root@5kh4z42 goufu]# mysql -u superdba -padmin -S /tmp/mysql3443.sock --default-character-set='latin1' -e 'select * from test.aa';

+------+--------+

| id | name |

+------+--------+

| 1 | 鍟婂晩 |

| 2 | 啊啊 |

| 3 | ???? |

| 4 | ?? |

+------+--------+

终端切换为UTF8字符集，执行命令

[root@5kh4z42 goufu]# mysql -u superdba -padmin -S /tmp/mysql3443.sock -e 'select * from test.aa';

+------+----------------+

| id | name |

+------+----------------+

| 1 | å•Šå•Š |

| 2 | °¡°¡ |

| 3 | ???? |

| 4 | ?? |

+------+----------------+

终端切换为GBK字符集，执行命令

[root@5kh4z42 goufu]# mysql -u superdba -padmin -S /tmp/mysql3443.sock -e 'select * from test.aa';

+------+----------------+

| id | name |

+------+----------------+

| 1 | 氓鈥⑴犆モ€⑴? |

| 2 | 掳隆掳隆 |

| 3 | ???? |

| 4 | ?? |

+------+----------------+

排查问题

查看数据库底层存储的值，执行如下命令

[root@5kh4z42 goufu]# mysql -u superdba -padmin -S /tmp/mysql3443.sock -e 'select id,hex(name) from test.aa';

+------+--------------+

| id | hex(name) |

+------+--------------+

| 1 | E5958AE5958A |

| 2 | B0A1B0A1 |

| 3 | 3F3F3F3F |

| 4 | 3F3F |

+------+--------------+

问题到这里其实有一些清晰了，结合我们之前的只是utf8存储汉字是3字节存储，即2^8^3，换算成十六进制就是2^4^2^3即6个16位数表示，所以'啊' 对应的编码是%E5%95%8A，同理算出并验证GBK编码的'啊' 的编码是%B0%A1，如我们所看到的3F其实就是?的编码，urf8和gbk是一样的

这里结合之前的只是，得知latin1是单字节编码，如果在存储的时候他识别不了会按照单自己存储，所以将他存储的二进制码交给其他的字符集就可以进行复原，试验中换成了ascii码得到的结果也是一样的，这可能就是单字节码的特性。

既然单字节存在这种转换规律那么gbk和utf8 之间是怎样进行转换的呢

首先，创建一张表结构如下

create table cc (

id int ,

name varchar(100),

terminal varchar(100),

`client` varchar(100)

) charset=gbk;

然后，我们分别使用utf8终端执行如下命令

mysql -u superdba -pguzaneyR@cj7M6m -S /tmp/mysql3443.sock --default-character-set='gbk' -e 'insert into test_liuyaxin.cc values(1,"啊啊","utf8","gbk")';

mysql -u superdba -pguzaneyR@cj7M6m -S /tmp/mysql3443.sock -e 'insert into test_liuyaxin.cc values(1,"啊啊","utf8","utf8")';

然后在gbk终端下执行命令

mysql -u superdba -pguzaneyR@cj7M6m -S /tmp/mysql3443.sock --default-character-set='gbk' -e 'insert into test_liuyaxin.cc values(2,"啊啊","gbk","gbk")';

mysql -u superdba -pguzaneyR@cj7M6m -S /tmp/mysql3443.sock -e 'insert into test_liuyaxin.cc values(2,"啊啊","gbk","utf8")';

最后使用utf8终端查询结果

mysql -u superdba -pguzaneyR@cj7M6m -S /tmp/mysql3443.sock -e 'select *,hex(name) from test_liuyaxin.cc';

+------+-----------+----------+--------+--------------+

| id | name | terminal | client | hex(name) |

+------+-----------+----------+--------+--------------+

| 1 | 鍟婂晩 | utf8 | gbk | E5958AE5958A |

| 1 | 啊啊 | utf8 | utf8 | B0A1B0A1 |

| 2 | 啊啊 | gbk | gbk | B0A1B0A1 |

| 2 | ???? | gbk | utf8 | 3F3F3F3F |

+------+-----------+----------+--------+--------------+

发现了当终端-->client-->egine这个过程中，如果进行多次编码转换，最后就会是乱码，而且无药可救，因为在第二次编码转换的时候就已经失去了原来的字符的含义，而当这个过程中只有一次转换的时候就算是乱码，他仍然可以存储下来，这样在反向解码的时候就可以还原，而单字节码如果出现不识别字符则会按照传递给他的编码进行存储，这样在反向解码的时候就可以识别出来

这样也可以看出来文件系统应该是不参与解码工作的，而是让两个进程(shell session和mysqlclient)进行编码转换，自己只是在中间将二进制码进行传递，其实mysql变量character_set_filesystem的值为binary时，表示文件系统字符集只负责将传递二进制数据

回归正题

既然说到导入导出的乱码问题，说到现在，其实解决这个问题的根源就在于，当你想吧备份文件进行恢复的时候，首先你需要能够正确的识别文件，即你传到服务器上的文件不是乱码，而且要与你程序所使用的字符集一致，而你所使用的倒入mysql 数据库的client 的字符集要和你底层的表的字符集相同，这样就不会出现乱码，前提是需要你使用单字节编码

当然，说这么多，只是为了解释这样存储的可能性，并且解答了疑问，但是并不是代表着推荐这么做，对于数据库，当然还是希望字符集进行统一，这样也就生了很多的麻烦