MyException - 我的异常网
当前位置:我的异常网» MySQL » MySQL数据导入导出乱码有关问题

MySQL数据导入导出乱码有关问题

www.MyException.Cn  网友分享于:2013-12-18  浏览:0次
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 的字符集要和你底层的表的字符集相同,这样就不会出现乱码,前提是需要你使用单字节编码
    • 当然,说这么多,只是为了解释这样存储的可能性,并且解答了疑问,但是并不是代表着推荐这么做,对于数据库,当然还是希望字符集进行统一,这样也就生了很多的麻烦

 

 

点击有惊喜

文章评论

编程语言是女人
编程语言是女人
科技史上最臭名昭著的13大罪犯
科技史上最臭名昭著的13大罪犯
2013年中国软件开发者薪资调查报告
2013年中国软件开发者薪资调查报告
程序猿的崛起——Growth Hacker
程序猿的崛起——Growth Hacker
我是如何打败拖延症的
我是如何打败拖延症的
老程序员的下场
老程序员的下场
每天工作4小时的程序员
每天工作4小时的程序员
程序员应该关注的一些事儿
程序员应该关注的一些事儿
老美怎么看待阿里赴美上市
老美怎么看待阿里赴美上市
程序员的一天:一寸光阴一寸金
程序员的一天:一寸光阴一寸金
程序员眼里IE浏览器是什么样的
程序员眼里IE浏览器是什么样的
Java 与 .NET 的平台发展之争
Java 与 .NET 的平台发展之争
程序员和编码员之间的区别
程序员和编码员之间的区别
鲜为人知的编程真相
鲜为人知的编程真相
为什么程序员都是夜猫子
为什么程序员都是夜猫子
程序员最害怕的5件事 你中招了吗?
程序员最害怕的5件事 你中招了吗?
当下全球最炙手可热的八位少年创业者
当下全球最炙手可热的八位少年创业者
亲爱的项目经理,我恨你
亲爱的项目经理,我恨你
程序员必看的十大电影
程序员必看的十大电影
要嫁就嫁程序猿—钱多话少死的早
要嫁就嫁程序猿—钱多话少死的早
程序员都该阅读的书
程序员都该阅读的书
为啥Android手机总会越用越慢?
为啥Android手机总会越用越慢?
60个开发者不容错过的免费资源库
60个开发者不容错过的免费资源库
那些性感的让人尖叫的程序员
那些性感的让人尖叫的程序员
一个程序员的时间管理
一个程序员的时间管理
Web开发人员为什么越来越懒了?
Web开发人员为什么越来越懒了?
“肮脏的”IT工作排行榜
“肮脏的”IT工作排行榜
初级 vs 高级开发者 哪个性价比更高?
初级 vs 高级开发者 哪个性价比更高?
中美印日四国程序员比较
中美印日四国程序员比较
“懒”出效率是程序员的美德
“懒”出效率是程序员的美德
程序员周末都喜欢做什么?
程序员周末都喜欢做什么?
10个调试和排错的小建议
10个调试和排错的小建议
 程序员的样子
程序员的样子
总结2014中国互联网十大段子
总结2014中国互联网十大段子
漫画:程序员的工作
漫画:程序员的工作
Java程序员必看电影
Java程序员必看电影
代码女神横空出世
代码女神横空出世
Web开发者需具备的8个好习惯
Web开发者需具备的8个好习惯
5款最佳正则表达式编辑调试器
5款最佳正则表达式编辑调试器
Google伦敦新总部 犹如星级庄园
Google伦敦新总部 犹如星级庄园
10个帮程序员减压放松的网站
10个帮程序员减压放松的网站
不懂技术不要对懂技术的人说这很容易实现
不懂技术不要对懂技术的人说这很容易实现
程序员的鄙视链
程序员的鄙视链
聊聊HTTPS和SSL/TLS协议
聊聊HTTPS和SSL/TLS协议
什么才是优秀的用户界面设计
什么才是优秀的用户界面设计
写给自己也写给你 自己到底该何去何从
写给自己也写给你 自己到底该何去何从
做程序猿的老婆应该注意的一些事情
做程序猿的老婆应该注意的一些事情
2013年美国开发者薪资调查报告
2013年美国开发者薪资调查报告
看13位CEO、创始人和高管如何提高工作效率
看13位CEO、创始人和高管如何提高工作效率
软件开发程序错误异常ExceptionCopyright © 2009-2015 MyException 版权所有