Understanding MySQL HEX/UNHEX Discrepancies and Disabling Binary‑as‑Hex Output

A netizen reported that the results of MySQL's HEX and UNHEX functions differed from the official manual examples.

The manual shows:

mysql> SELECT X'616263', HEX('abc'), UNHEX(HEX('abc'));
--> 'abc', 616263, 'abc'
mysql> SELECT HEX(255), CONV(HEX(255),16,10);
--> 'FF', 255

But the user observed:

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)

The first query’s X'616263' and UNHEX(HEX('abc')) results differ from the manual, while the second query matches.

Key function meanings:

HEX converts a string or number to its hexadecimal representation.

UNHEX performs the reverse conversion.

CONV converts numbers between different bases.

The X'…' literal (or 0x… ) denotes a hexadecimal string, case‑insensitive.

Running the same queries on the author's VM produced the same unexpected hexadecimal output:

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)

The MySQL client status shows Binary data as: Hexadecimal , meaning binary data is printed as hex by default.

Both values have the binary charset, so converting them to UTF‑8 yields the correct string:

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)

The underlying cause is the MySQL client option --binary-as-hex , which is enabled for interactive terminals and prints binary data as hex.

Disabling the option either in the my.cnf under the [mysql] section or via the command line fixes the output:

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)

The equivalent shortcut --skip-binary-as-hex produces the same result.

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)

Note that this option also affects the display of BLOB and BINARY column data.