mysql十六进制结果与shell和java不同的原因

Question

我想得到汉字的unicode表示，例如'京' --> 4eac

Shell

➜  ~ printf "%x\n" \'京
4eac

Java

jshell> Integer.toHexString('京');
$14 ==> "4eac"

为什么在mysql中它有不同的结果？

select hex('京');
+------------+
| hex('京')  |
+------------+
| E4BAAC     |
+------------+

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                                                 |

在mysql中，它必须使用下面的方式来获得与上面相同的结果。

select hex(convert('京' using ucs2));
+--------------------------------+
| hex(convert('京' using ucs2))  |
+--------------------------------+
| 4EAC                           |
+--------------------------------+

那么，为什么mysql中的hex与其他的不同呢？

此外，从unicode到字符

壳

➜  ~ echo '\u4eac'
京

Java

jshell> String s = "\u4eac";
s ==> "京"

Mysql

select char(0x4eac using ucs2);
+-------------------------+
| char(0x4eac using ucs2) |
+-------------------------+
| 京                      |
+-------------------------+

Answer 1

UTF-8 (MySQL的utf8或utf8mb4)是与UCS2 (MySQL: ucs2)不同的编码方式。

'京' =
Unicode "codepoint" (in hex) '4eac' =
UCS2 encoding (2 bytes, in hex) '4EAC' =
UTF-8 encoding (3 bytes, in hex) 'E4BAAC' =
html entity '京' (hex) or '京' (decimal)

参考资料：http://unicode.scarfboy.com/?s=U%2B4eac