ing in all contexts in MariaDB since MariaDB 10.0.3 and MariaDB
5.5.31 (fixing MDEV-4489). The latter syntax, 0xvalue, is a MySQL/MariaDB
extension for hex hybrids and behaves as a string or as a number depending on
context. MySQL treats all syntaxes the same, so there may be different results
in MariaDB and MySQL (see below).

Examples
--------

Representing the a character with the three syntaxes explained above:

SELECT x'61', X'61', 0x61;
+-------+-------+------+
| x'61' | X'61' | 0x61 |
+-------+-------+------+
| a     | a     | a    |
+-------+-------+------+

Hexadecimal literals in a numeric context:

SELECT 0 + 0xF, -0xF;
+---------+------+
| 0 + 0xF | -0xF |
+---------+------+
|      15 |  -15 |
+---------+------+

Fun with Types
--------------

CREATE TABLE t1 (a INT, b VARCHAR(10));
INSERT INTO t1 VALUES (0x31, 0x61),(COALESCE(0x31), COALESCE(0x61));

SELECT * FROM t1;
+------+------+
| a    | b    |
+------+------+
|   49 | a    |
|    1 | a    |
+------+------+

The reason for the differing results above is that when 0x31 is inserted
directly to the column, it's treated as a number, while when 0x31 is passed to
COALESCE(), it's treated as a string, because:

* HEX values have a string data type by default.
* COALESCE() has the same data type as the argument.

Differences Between MariaDB and MySQL
-------------------------------------

SELECT x'0a'+0;
+---------+
| x'0a'+0 |
+---------+
|       0 |
+---------+
1 row in set, 1 warning (0.00 sec)

Warning (Code 1292): Truncated incorrect DOUBLE value: '\x0A'

SELECT X'0a'+0;
+---------+
| X'0a'+0 |
+---------+
|       0 |
+---------+
1 row in set, 1 warning (0.00 sec)

Warning (Code 1292): Truncated incorrect DOUBLE value: '\x0A'

SELECT 0x0a+0;
+--------+
| 0x0a+0 |
+--------+
|     10 |
+--------+

In MySQL (up until at least MySQL 8.0.26):

SELECT x'0a'+0;
+---------+
| x'0a'+0 |
+---------+
|      10 |
+---------+

SELECT X'0a'+0;
+---------+
| X'0a'+0 |
+---------+
|      10 |
+---------+

SELECT 0x0a+0;
+--------+
| 0x0a+0 |
+--------+
|     10 |
+--------+

URL: https://mariadb.com/kb/en/hexadecimal-literals/https://mariadb.com/kb/en/hexadecimal-literals/