文档章节

Mysql latin1也支持emoji字符的错觉分析

zgw06629
 zgw06629
发布于 2015/05/13 18:00
字数 1709
阅读 282
收藏 2

起初发现了如下的现象:

mysql> show variables like 'character%';
+--------------------------+---------------------------------------+
| Variable_name            | Value                                 |
+--------------------------+---------------------------------------+
| character_set_client     | latin1                                |
| character_set_connection | latin1                                |
| character_set_database   | latin1                                |
| character_set_filesystem | binary                                |
| character_set_results    | latin1                                |
| character_set_server     | utf8mb4                               |
| character_set_system     | utf8                                  |
| character_sets_dir       | /opt/mysql/server-5.6/share/charsets/ |
+--------------------------+---------------------------------------+
mysql> show create table t4\G
*************************** 1. row ***************************
   Table: t4
Create Table: CREATE TABLE `t4` (
  `data` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
mysql> insert into t4 select '\U+1F600';

觉得很奇怪怎么latin1也支持emoji字符了呢?不是只有utf8mb4才支持吗? 于是在StackOverFlow上提问,一个网友的回答觉得有道理,回答如下:

I think you saved into and retrieved from the database a string of bytes that is interpreted by the terminal as an Unicode character. Check the output of SELECT LENGTH(data), CHAR_LENGTH(data) FROM t4 to see what's happening. They should return different values for multi-byte characters and the same value forlatin1. –  axiac 19 hours ago

在加上无意中看到了一篇博客, 其中说道:

抛一个问题,latin1字符集的表,用户写入和读取汉字是否有问题?答案是只要合理设置,没有问题。假设SecureCRT为UTF8,character_set_client和表字符集均设置为latin1,参考第3节的分析,那么用户读取和写入数据的过程中,并不涉及字符集编码转换的问题,将UTF8的汉字字符转为二进制流写入database,提取出来后,secureCRT再将对应的二进制解码为对应的汉字,所以不影响用户的使用。

于是现在觉得上述现象很正常。

因为操作系统默认的字符集为utf8(LANG=en_US.UTF-8), 而client、connection、database均为latin1, 于是这一路(从终端界面执行insert到保存数据到表中)都没有编码转换,直接传输的是utf8编码后的二进制流。

怎么验证上述结论呢? 于是决定修改中间环节的字符集,看会发生什么?

  1.  

mysql> set names gbk;
mysql> show variables like 'character%';
+--------------------------+---------------------------------------+
| Variable_name            | Value                                 |
+--------------------------+---------------------------------------+
| character_set_client     | gbk                                   |
| character_set_connection | gbk                                   |
| character_set_database   | latin1                                |
| character_set_filesystem | binary                                |
| character_set_results    | gbk                                   |
| character_set_server     | utf8mb4                               |
| character_set_system     | utf8                                  |
| character_sets_dir       | /opt/mysql/server-5.6/share/charsets/ |
+--------------------------+---------------------------------------+
mysql> insert into t4 select '\U+1F600';
ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x98\x80' for column 'data' at row 1

分析:

现在操作系统是utf8, client、connection是gbk, 字段是latin1, 因为一开始是utf8二进制流,且client和connection均为gbk,无需转码,故只在最后当保存到表字段中时需要由utf8转为latin1,由于latin1不能解码该utf8二进制流故导致了上述报错。

若将字符集不一致的情况再往前挪一步会怎样呢? 如下所示:

mysql> set character_set_connection = latin1;
mysql> show variables like 'character%';
+--------------------------+---------------------------------------+
| Variable_name            | Value                                 |
+--------------------------+---------------------------------------+
| character_set_client     | gbk                                   |
| character_set_connection | latin1                                |
| character_set_database   | latin1                                |
| character_set_filesystem | binary                                |
| character_set_results    | gbk                                   |
| character_set_server     | utf8mb4                               |
| character_set_system     | utf8                                  |
| character_sets_dir       | /opt/mysql/server-5.6/share/charsets/ |
+--------------------------+---------------------------------------+

现在client和connection就不一致了,就是说需要先将utf8-->gbk-->latin1, 那么现在能成功插入emoji字符吗? 

mysql> insert into t4 select '\U+1F600';

可以插入,查询结果如下:

mysql> select data,hex(data) from t4;
+------+-----------+
| data | hex(data) |
+------+-----------+
| ??   | 3F3F      |
+------+-----------+

似乎在utf8-->gbk的过程中,将utf8编码后的二进制流(f0 9f 98 80)解码成了‘??’,而‘??’能被latin1成功解析。但如何通过java程序模拟上述的转换呢?

经过尝试发现下面的代码可以模拟数据库操作情形,如下所示:

/**
 * os utf-8
 * character_set_client gbk
 * character_set_connection latin1
 * field latin1
 * 
 * @throws UnsupportedEncodingException 
 */
@Test
public void test_os_utf8_to_client_gbk_to_connection_latin1() throws UnsupportedEncodingException{
	String emoji = ...; //因该博客系统不支持Emoji字符 故用省略号表示
	String receivedStr = new String(emoji.getBytes("utf-8"),"gbk"); //os(utf-8)-->client(gbk)
	System.out.println(receivedStr);//馃榾
	/**
	 *  若client与connection不一致 转换时统一使用connection的字符集
	 */
	String convertedStr = new String(receivedStr.getBytes("latin1"),"latin1"); //client(gbk) --> connection(latin1)
	System.out.println(convertedStr);//??
	printHexString(convertedStr.getBytes("latin1")); //3f 3f
}

那假如将上例中的client与connection交换一下位置呢,如下所示:

mysql> show variables like 'character%';
+--------------------------+---------------------------------------+
| Variable_name            | Value                                 |
+--------------------------+---------------------------------------+
| character_set_client     | latin1                                |
| character_set_connection | gbk                                   |
| character_set_database   | latin1                                |
| character_set_filesystem | binary                                |
| character_set_results    | gbk                                   |
| character_set_server     | utf8mb4                               |
| character_set_system     | utf8                                  |
| character_sets_dir       | /opt/mysql/server-5.6/share/charsets/ |
+--------------------------+---------------------------------------+

现在的转化流变成这样了:utf8-->latin1-->gbk-->latin1, 从之前的经验似乎可以预测进行第一步转化时就应该报错(Incorrect string value: '\xF0\x9F\x98\x80' for column 'data' at row 1),但实际情况是:

mysql> insert into t4 select '\U+1F600';
Query OK, 1 row affected (0.01 sec)
mysql> select data,hex(data) from t4;
+------+-----------+
| data | hex(data) |
+------+-----------+
| ??   | 3F3F      |
| ???? | 3F3F3F3F  |
+------+-----------+

并未报错仍能成功插入, 似乎只要不是最后一步往表里插入记录就不会报错,但这次变成4个问号了。

这次对应的java模拟程序如下所示:

/**
 * os utf-8
 * character_set_client latin1
 * character_set_connection gbk
 * field latin1
 * @throws UnsupportedEncodingException 
 */
@Test
public void test_os_utf8_to_client_latin1_to_connection_gbk_to_field_latin1() throws UnsupportedEncodingException{

	String emoji = ...;
	String receivedStr = new String(emoji.getBytes("utf-8"),"latin1"); //os(utf-8)-->client(latin1)
	System.out.println(receivedStr); Ÿ
	// 若client与connection不一致 统一使用connection字符集
	String convertedStr = new String(receivedStr.getBytes("gbk"),"gbk"); //client(latin1) --> connection(gbk)
	System.out.println(convertedStr);//????
	String savedStr = new String(convertedStr.getBytes("gbk"),"latin1"); // connection(gbk) --> field(latin1)
	System.out.println(savedStr);//????
	printHexString(savedStr.getBytes("latin1")); //3f 3f 3f 3f 
}


再看一种情况,如果字段的字符集为utf8呢? 如下所示:

mysql> show create table t6\G
*************************** 1. row ***************************
       Table: t6
Create Table: CREATE TABLE `t6` (
  `data` varchar(100) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
mysql> show variables like 'character%';
+--------------------------+---------------------------------------+
| Variable_name            | Value                                 |
+--------------------------+---------------------------------------+
| character_set_client     | gbk                                   |
| character_set_connection | gbk                                   |
| character_set_database   | latin1                                |
| character_set_filesystem | binary                                |
| character_set_results    | gbk                                   |
| character_set_server     | utf8mb4                               |
| character_set_system     | utf8                                  |
| character_sets_dir       | /opt/mysql/server-5.6/share/charsets/ |
+--------------------------+---------------------------------------+

能否成功插入呢?是否也会报如上情形中的Incorrect String value错误呢?

mysql> insert into t6 select '\U+1F600';
Query OK, 1 row affected (0.00 sec)

这次可以成功插入,但字节流不在是f09f9880,而是e9a683e6a6be. 对应的java模拟程序为:

/**
 * os utf-8
 * character_set_client gbk
 * character_set_connection gbk
 * filed utf8
 * @throws UnsupportedEncodingException 
 */
@Test
public void test_os_utf8_to_gbk_to_field_utf8() throws UnsupportedEncodingException{
	String emoji = ...;
	String receivedStr = new String(emoji.getBytes("utf-8"),"gbk"); //os(utf-8)-->client(gbk)
	System.out.println(receivedStr); //馃榾
	/**
	 *  若client与connection一致时 使用默认的字符集
	 */
	String savedStr = new String(receivedStr.getBytes(),"utf-8"); // connection(gbk) --> field(utf8)
	System.out.println(savedStr);//馃榾
	printHexString(savedStr.getBytes("utf-8")); //e9 a6 83 e6 a6 be 
}

再用此模拟程序模拟field为latin1时报错时的情形,

/**
 * os utf-8
 * character_set_client gbk
 * character_set_connection gbk
 * filed latin1
 * @throws UnsupportedEncodingException 
 */
@Test
public void test_os_utf8_to_gbk_to_field_latin1() throws UnsupportedEncodingException{
	String emoji = ...;
	String receivedStr = new String(emoji.getBytes("utf-8"),"gbk"); //os(utf-8)-->client(gbk)
	System.out.println(receivedStr); //馃榾
	/**
	 *  若client与connection一致时 使用默认的字符集
	 */
	String savedStr = new String(receivedStr.getBytes(),"latin1"); // connection(gbk) --> field(latin1)
	System.out.println(savedStr);//
	printHexString(savedStr.getBytes("latin1")); //e9 a6 83 e6 a6 be 
}

发现最后保存到字段中的字节流是一样的 均是e9a683e6a6be, 为什么只有字段字符集为latin1时才报错呢?且报错的信息是:

ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x98\x80' for column 'data' at row 1

而不是

Incorrect string value: '\xE9\xA6\x83\xE6\xA6\xBE'

呢?

补充:

java程序模拟出实际数据库操作情形总结:

  1. 若client、connection、filed字符集均一致,直接保存的就是用操作系统默认字符集编码后的二进制流。

  2. 若client与connection一致,但field不同,当由connection转为field的字符集时,使用操作系统的字符集。

  3. 若client与connection不一致,使用connection字符集。

单元测试代码补充:

private void printHexString(byte[] bytes)
		throws UnsupportedEncodingException {
	for(byte b : bytes)
		System.out.print(byteToHexStr(b)+" ");
	System.out.println("\n");
}
private String byteToHexStr(byte b){
    int i = b;
    if(i<0)
	i = 256 - (i*-1);
    String hex = Integer.toHexString(i);
    if(hex.length()==1)
	return "0"+hex;
    else {
		return hex;
	}
}

参考文档:

https://dev.mysql.com/doc/refman/5.0/en/charset-connection.html

http://www.cnblogs.com/cchust/p/4327019.html

http://mysql.rjweb.org/doc.php/charcoll




© 著作权归作者所有

zgw06629

zgw06629

粉丝 18
博文 54
码字总数 30471
作品 0
海淀
程序员
私信 提问
mysql更改character_set_server后终端执行status看到的结果咨询

开始时[mysqld] character_set_server=utf8 在终端执行status看到结果如下: Server characterset: utf8 Db characterset: utf8 Client characterset: utf8 Conn. characterset: utf8 现在因......

zgw06629
2015/04/16
1K
0
Jade插入emoji字符总结

不修改Mysql 服务器字符集(charactersetserver=utf8mb4)的前提下,使用Jade插入Emoji字符. Mysql服务器字符集设置: mysql> show variables like 'character%';+-------------------------......

zgw06629
2015/05/05
1K
0
mysql 插入emoji字符的相关问题咨询

如下所示: 我的问题是,不是已经正确解析出是4个字节的utf8mb4字符(f0 9f 98 80)了吗? 为什么还需要显式执行set names utf8mb4, 才能成功插入呢? 另外在下面字符集环境会话中的操作,让我彻底的...

zgw06629
2015/05/02
1K
1
MySQL怎么存文本不乱码?

导读 MySQL里怎么存储那些看起来会乱码的字符? 我在“UTF8字符集的表怎么直接转UTF8MB4”一文中介绍了如何把表字符集由UTF8直接转换成UTF8MB4的几种方法。 1、只修改字符集(使用默认校验集...

n88lpo
2017/12/06
0
0
FaceBook专家:10分钟彻底解决MySQL乱码问题?

本文由【DBA+社群】原创专家卢钧轶为大家较为详细的介绍字符集,字符编码, MySQL乱码的成因和具体的解决方案。 目录 Part 1 字符集和字符编码 · 什么是字符集 · 什么是字符编码 · UTF-8和...

卢钧轶
2016/01/08
0
0

没有更多内容

加载失败,请刷新页面

加载更多

插入排序算法

《算法总纲目录》 1、定义     元素被分为有序区和无序区两部分。最初有序区只有一个元素。每次从无序区中选择一个元素,插入到有序区的位置,直到无序区变空。 2、代码 public class...

木九天
16分钟前
2
0
ApacheCN 翻译/校对/笔记整理活动进度公告 2019.10.18

注意 请贡献者查看参与方式,然后直接在 ISSUE 中认领。 翻译/校对三个文档就可以申请当负责人,我们会把你拉进合伙人群。翻译/校对五个文档的贡献者,可以申请实习证明。 请私聊片刻(52981...

ApacheCN_飞龙
18分钟前
2
0
Hands-on! 如何给 TiDB 添加新系统表

作者:黄东旭 “TiDB,你已经是一个成熟的数据库了,该学会用自己的 SQL 查自己的状态了。” 对于一个成熟的数据库来说,通过 SQL 来查询系统本身的状态再正常不过,对于 MySQL 来说 INFOMA...

TiDB
24分钟前
2
0
SpringBoot admin+Eureka+钉钉通知

SpringBoot admin+Eureka+钉钉通知 一、效果 登录账号+密码 监控服务 查看实时日志 钉钉通知 二、什么是Spring Boot Admin ? Spring Boot Admin是一个开源社区项目,用于管理和监控SpringB...

小白的成长
27分钟前
9
0
docker-rabbitmq

docker pull rabbitmqmkdir -p /rabbitmqdocker run -d \--name rabbitmq \--hostname rabbitmq \-v /rabbitmq:/var/lib/rabbitmq \-e RABBITMQ_DEFAULT_USER=root \-e RA......

李琼涛
29分钟前
4
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部