技术分享MySQL命令行一则诡异问题分享
作者:杨涛涛
资深数据库专家,专研 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 等的表数据输出。