作者:杨涛涛
资深数据库专家,专研 MySQL 十余年。善于 MySQL、PostgreSQL、MongoDB 等开源数据库相干的备份复原、SQL 调优、监控运维、高可用架构设计等。目前任职于爱可生,为各大运营商及银行金融企业提供 MySQL 相干技术支持、MySQL 相干课程培训等工作。
本文起源:原创投稿
* 爱可生开源社区出品,原创内容未经受权不得随便应用,转载请分割小编并注明起源。
昨天一位网友的问题共享进去,以防大家入坑。
这位网友对 MySQL 官网手册里的 hex 和 unhex 函数有些疑难:本人试验后果和手册中给的后果有些差别。
手册上是这样写的:
mysql> SELECT X'616263', HEX('abc'), UNHEX(HEX('abc'));
-> 'abc', 616263, 'abc'
mysql> SELECT HEX(255), CONV(HEX(255),16,10);
-> 'FF', 255
完后本人试验后果反而这样:
mysql>SELECT X'616263', HEX('abc'), UNHEX(HEX('abc'));
+----------------------+------------+--------------------------------------+
| X'616263' | HEX('abc') | UNHEX(HEX('abc')) |
+----------------------+------------+--------------------------------------+
| 0x616263 | 616263 | 0x616263 |
+----------------------+------------+--------------------------------------+
1 row in set (0.00 sec)
mysql>SELECT HEX(255), CONV(HEX(255),16,10);
+----------+----------------------+
| HEX(255) | CONV(HEX(255),16,10) |
+----------+----------------------+
| FF | 255 |
+----------+----------------------+
1 row in set (0.00 sec)
第一条语句里 X’616263′ 和UNHEX(HEX(‘abc’)) 的执行后果和手册不统一,第二条语句执行后果和手册完全一致。
要找到这个问题的起因,得先明确这几个函数的含意。
- hex 函数用来把字符串或者数字转换为十六进制;
- unhex 函数则相同;
- conv 函数用来在各种不同进制之间互转;
- X 特殊字符用来示意十六进制的字符串(不辨别大小写),也能够用 0x(小写 x,比方 X ’616263′ 可写为 0x616263)。
我本人虚拟机上的试验:后果也不正确。
mysql:ytt:8.0.28>select X'616263',UNHEX(HEX('abc'));
+----------------------+--------------------------------------+
| X'616263' | UNHEX(HEX('abc')) |
+----------------------+--------------------------------------+
| 0x616263 | 0x616263 |
+----------------------+--------------------------------------+
1 row in set (0.00 sec)
我猜测:函数的执行后果必定是对的(大厂不可能犯这样的低级谬误,有错也是我本身的问题),然而此处进制没做失常转换,肯定是客户端在后果转码上有哪些地方默认有变。为此查问 MySQL 命令行的 status 后果:
mysql:(none):8.0.28>status
--------------
mysql Ver 8.0.28 for Linux on x86_64 (MySQL Community Server - GPL)
...
UNIX socket: /var/run/mysqld/mysqld.sock
Binary data as: Hexadecimal
--------------
下面蕴含一行:Binary data as: Hexadecimal 这一行意思是说 MySQL 命令行把二进制字符打印为十六进制字符。
到这里应该判断下这两个十六进制字符的编码:后果都为 binary。
mysql:(none):8.0.28>select charset(X'616263'),charset(UNHEX(HEX('abc')));
+--------------------+----------------------------+
| charset(X'616263') | charset(UNHEX(HEX('abc'))) |
+--------------------+----------------------------+
| binary | binary |
+--------------------+----------------------------+
1 row in set (0.00 sec)
既然编码为 binary,间接用 convert 函数转码即可:转码后的后果正确。
mysql:(none):8.0.28>select convert(X'616263' using utf8mb4),convert(UNHEX(HEX('abc')) using utf8mb4);
+----------------------------------+------------------------------------------+
| convert(X'616263' using utf8mb4) | convert(UNHEX(HEX('abc')) using utf8mb4) |
+----------------------------------+------------------------------------------+
| abc | abc |
+----------------------------------+------------------------------------------+
1 row in set (0.00 sec)
到这里,问题是解释通了,不过能不能每次不进行转码而间接查呢?当然能够。MySQL 命令行客户端有个选项 (–binary-as-hex) 用来管制是否打印二进制数据为十六进制,MySQL 命令行默认开启。
--binary-as-hex Print binary data as hex. Enabled by default for
interactive terminals.
只需敞开这个选项即可,写入配置文件(my.cnf 里 [mysql] 段下)或者是命令行带入都能够:后果正确。
root@ytt-ubuntu:~# mysql --binary-as-hex=false
..
mysql:(none):8.0.28>select X'616263',UNHEX(HEX('abc'));
+-----------+-------------------+
| X'616263' | UNHEX(HEX('abc')) |
+-----------+-------------------+
| abc | abc |
+-----------+-------------------+
1 row in set (0.00 sec)
还有一个通用选项:–skip-binary-as-hex,带上后成果一样。
root@ytt-ubuntu:~# mysql --skip-binary-as-hex
...
mysql:(none):8.0.28>select X'616263',UNHEX(HEX('abc'));
+-----------+-------------------+
| X'616263' | UNHEX(HEX('abc')) |
+-----------+-------------------+
| abc | abc |
+-----------+-------------------+
1 row in set (0.00 sec)
须要留神的是:这个选项也同时影响字段类型为 BLOB,BINARY 等的表数据输入。