Hex String Literal Evaluation Examples

MySQL Tutorials - Herong's Tutorial Examples

∟Hex String Literal Evaluation Examples

This section provides tutorial examples on how hex string literals are evaluated to values of different datatypes.

Hex string literal example - HexStringLiterals.sql:

-- HexStringLiterals.sql
-- Copyright (c) 1999 HerongYang.com. All Rights Reserved.
--
SELECT x'41424344' AS LINE_1;
SELECT x'31323334' AS LINE_2;
SELECT x'31323334' + 0 AS LINE_3;
SELECT x'01' + 0 AS LINE_4;
SELECT x'0001' + 0 AS LINE_5;
SELECT x'ff' + 0 AS LINE_6;
SELECT x'ffffffff' + 0 AS LINE_7;
SELECT x'ffffffffffffffff' + 0 AS LINE_8;

Run HexStringLiterals.sql on MySQL 8.0 and 5.7 servers, you will get:

herong> %mysql%\bin\mysql --user=root --password=TopSecret \
   < HexStringLiterals.sql

LINE_1
ABCD
LINE_2
1234
LINE_3
825373492
LINE_4
1
LINE_5
1
LINE_6
255
LINE_7
4294967295
LINE_8
18446744073709551615

Note that:

Line 1 and 2 tell us that hex string literals are evaluated into character strings in a string expression.
Line 3, 4, 5, 6 and 7 tell us that hex string literals are evaluated into integers in numeric expression.
Line 8 tells us that if the resulting integer value from a hex string literal is too big, an approximation will be generated. In the 8-byte non-singed integer format, x'ffffffffffffffff' should be evaluated to 2**64 = 18446744073709551616.

Run HexStringLiterals.sql on MySQL 5.6 and 5.0 servers, you will get:

LINE_1
ABCD
LINE_2
1234
LINE_3
825373492
LINE_4
1
LINE_5
1
LINE_6
255
LINE_7
4294967295
LINE_8
18446744073709552000

Note that:

Line 8 tells us that if the resulting integer value from a hex string literal is too big, an approximation will be generated. In the 8-byte non-singed integer format, x'ffffffffffffffff' should be evaluated to 2**64 = 18446744073709551616.
MySQL server 5.0 is no longer using the 8-byte singed integer format to evaluate hex string literals in numeric expressions. This could be a problem, if we want to specify a negative integer with a hex string literal.

Run HexStringLiterals.sql on MySQL 4.0 server, you will get:

LINE_1
ABCD
LINE_2
1234
LINE_3
825373492
LINE_4
1
LINE_5
1
LINE_6
255
LINE_7
4294967295
LINE_8
-1

Note that:

Line 8 tells us that a 8-byte hex string literal is evaluated to a integer in a numeric expression using the 8-byte signed integer format. In the 8-byte singed integer format, x'ffffffffffffffff' represents -1.