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

最新推荐文章于 2024-09-15 09:47:04 发布

weixin_33725239

最新推荐文章于 2024-09-15 09:47:04 发布

阅读量174

点赞数

文章标签： java 操作系统数据库

原文链接：https://my.oschina.net/zhuguowei/blog/414476

版权

2019独角兽企业重金招聘Python工程师标准>>>

起初发现了如下的现象:

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编码后的二进制流。

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

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程序模拟出实际数据库操作情形总结：

若client、connection、filed字符集均一致，直接保存的就是用操作系统默认字符集编码后的二进制流。
若client与connection一致，但field不同，当由connection转为field的字符集时，使用操作系统的字符集。
若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

转载于:https://my.oschina.net/zhuguowei/blog/414476