技术分享 | MySQL 命令行一则诡异问题分享

爱可生开源社区

发布于 2022-04-06 21:35:23

2770

发布于 2022-04-06 21:35:23

作者：杨涛涛

资深数据库专家，专研 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 等的表数据输出。