MySQL数据导入导出乱码问题

场景

  • 程序使用gbk编码,表使用的是latin1编码,而我再一次倒入数据的操作中使用了utf8的终端,指定--default-character-set='latin1'倒入的数据是乱码,而后来将终端换成gbk之后酒倒入成功了
  • 通过变换插入数据的终端,模拟我们平常需要倒入数据的终端
  • 通过变更查询数据的终端,来模拟我们程序的查询操作
  • default-character-set变更能够正确的读取中文字符

测试环境

  • mysql server和Linux是utf8的字符集
  • 使用xshell作为终端进行输入
  • 建立一张表存储字符集是latin1
  • 使用mysqlclient 进行插入和查询,查看查询到的数据是否正确

实验步骤

  1. 使用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/ |
    
    +--------------------------+----------------------------------------------+
    
  2. 这里可以看出具体的client和connection与results是取决于client的,默认是和文件系统统一的
  3. 创建一张latin1字符集的表进行测试,看看mysql如何对字符集进行转换,老的或者奇葩业务如何使用latin1存储中文
    CREATE TABLE `aa` (
    
    `id` int(11) DEFAULT NULL,
    
    `name` varchar(100) DEFAULT NULL
    
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1
  4. 插入测试数据
    终端切换为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,"啊啊")';
  5. 查询数据
    终端切换为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 | ??             |
    
    +------+----------------+
  6. 排查问题
    查看数据库底层存储的值,执行如下命令
    
    [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码得到的结果也是一样的,这可能就是单字节码的特性。
  7. 既然单字节存在这种转换规律那么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时,表示文件系统字符集只负责将传递二进制数据
  8. 回归正题
    • 既然说到导入导出的乱码问题,说到现在,其实解决这个问题的根源就在于,当你想吧备份文件进行恢复的时候,首先你需要能够正确的识别文件,即你传到服务器上的文件不是乱码,而且要与你程序所使用的字符集一致,而你所使用的倒入mysql 数据库的client 的字符集要和你底层的表的字符集相同,这样就不会出现乱码,前提是需要你使用单字节编码
    • 当然,说这么多,只是为了解释这样存储的可能性,并且解答了疑问,但是并不是代表着推荐这么做,对于数据库,当然还是希望字符集进行统一,这样也就生了很多的麻烦
时间: 2024-10-05 09:25:53

MySQL数据导入导出乱码问题的相关文章

MySQL数据导入导出方法与工具介绍(1- myslqimport utility)

mysql|数据             MySQL数据导入导出方法与工具介绍(1- myslqimport utility)              mysqlimport文本文件导入工具介绍 翻译声明:    本文内容来自Sam's Teach Yourself MySQL in 21 Days一书的部分内容,by Mark Maslakowski      英文原文版权属原作者所有,中文的部分翻译有略有增删;原书讲的过于清楚的地方有删,讲的不清楚的地方有增:如果有翻译的不妥或者不正确的地

MySQL数据导入导出方法与工具介绍(2-import from sql files)

mysql|数据    MySQL数据导入导出方法与工具介绍(2-import from sql files)          批处理导入文件,从sql文件导入数据到数据库中 翻译声明:    本文内容来自Sam's Teach Yourself MySQL in 21 Days一书的部分内容,by Mark Maslakowski    英文原文版权属原作者所有,中文的部分翻译有略有增删;原书讲的过于清楚的地方有删,讲的不清楚的地方有增:如果有翻译的不妥或者不正确的地方,请指正. 翻译者:D

MySQL数据导入导出方法与工具介绍(3-Exporting Data)

mysql|数据                MySQL数据导入导出方法与工具介绍(3-Exporting Data)                  导出数据的方法:Methods of Exporting Data 翻译声明:    本文内容来自Sam's Teach Yourself MySQL in 21 Days一书的部分内容,by Mark Maslakowski    英文原文版权属原作者所有,中文的部分翻译有略有增删;原书讲的过于清楚的地方有删,讲的不清楚的地方有增:如果有翻

MySQL数据导入导出实例教程手册

mysqldump是mysql自带的一个数据导入导出工具,其官方注释为: shell> mysqldump [options] db_name [tbl_name ...] shell> mysqldump [options] –databases db_name - shell> mysqldump [options] –all-databases 使用mysqldump命令将整个数据库导出  代码如下 复制代码 mysqldump -h 127.0.0.1 -P 3306 -u ro

MySQL数据导入导出牛刀小试

最近学习了下MySQL中数据的导入导出,发现功能点真是丰富,很方便很快捷. 这些导入导出的方式还是有不少的细节的,在此先不做扩展和深入分析.--数据导出 方式1 比如要实现数据的导出,直接可以指定生成的文件使用outfile即可.对于空值的处理是"\N" mysql> select * from test into outfile '/u02/mysql/dump/a.sql'; Query OK, 4 rows affected (0.00 sec) 1       aaaa

MySQL数据导入导出工具mysqlimport简介

mysql|数据        1).mysqlimport的语法介绍: mysqlimport位于mysql/bin目录中,是mysql的一个载入(或者说导入)数据的一个非常有效的工具.这是一个命令行工具.有两个参数以及大量的选项可供选择.这个工具把一个文本文件(text file)导入到你指定的数据库和表中.比方说我们要从文件Customers.txt中把数据导入到数据库Meet_A_Geek中的表Custermers中: mysqlimport Meet_A_Geek Customers.

MySQL数据导入导出方法与工具介绍(1)

mysql|数据  翻译声明:     本文内容来自Sam's Teach Yourself MySQL in 21 Days一书的部分内容,by Mark Maslakowski      英文原文版权属原作者所有,中文的部分翻译有略有增删;原书讲的过于清楚的地方有删,讲的不清楚的地方有增:如果有翻译的不妥或者不正确的地方,请指正.-AsobP  翻译者:David Euler,SCU. de_euler-david@www.yahoo.com.cn  时间:2004/04/24于川大-Aso

MySQL数据导入导出方法与工具介绍

翻译声明:本文内容来自Sams Teach Yourself MySQL in 21 Days一书的部分内容,by Mark Maslakowski 英文原文版权属原作者所有,中文的部分翻译有略有增删;原书讲的过于清楚的地方有删,讲的不清楚的地方有增:如果有翻译的不妥或者不正确的地方,请指正. 翻译者:David Euler,SCU. de_euler-david@www.yahoo.com.cn 时间:2004/04/24于川大 1).mysqlimport的语法介绍 mysqlimport位

Mysql数据导入导出

导出导入数据库 导出 mysqldump方法 mysqldump -u用户名 -p密码名 database [table]> 目标文件 导入 mysql -uroot -proot use database source 目标文件: PS: 这种方法是导出整个表数据,并且带着建表信息,假如导入的数据库有同名的表,会被替换 PS: 可以添加条件 mysql -uroot -proot [-n] [-t] [-d] database [table]>name  -t 不包含创建表的信息 -d不包含