MySQL supports hexadecimal values, written using
X'
,
val
'x'
, or
val
'0x
format,
where val
val
contains hexadecimal digits
(0..9
, A..F
). Lettercase
of the digits does not matter. For values written using
X'
or
val
'x'
format,
val
'val
must contain an even number of
digits. For values written using
0x
,
values that contain an odd number of digits are treated as
having an extra leading val
syntax0
. For example,
0x0a
and 0xaaa
are
interpreted as 0x0a
and
0x0aaa
.
In numeric contexts, hexadecimal values act like integers (64-bit precision). In string contexts, they act like binary strings, where each pair of hex digits is converted to a character:
mysql>SELECT X'4D7953514C';
-> 'MySQL' mysql>SELECT 0x0a+0;
-> 10 mysql>SELECT 0x5061756c;
-> 'Paul'
The default type of a hexadecimal value is a string. If you want
to ensure that the value is treated as a number, you can use
CAST(... AS UNSIGNED)
:
mysql> SELECT 0x41, CAST(0x41 AS UNSIGNED);
-> 'A', 65
The X'
syntax is based on standard SQL. The hexstring
'0x
syntax is based on ODBC. Hexadecimal strings are often used by
ODBC to supply values for BLOB
columns.
You can convert a string or a number to a string in hexadecimal
format with the HEX()
function:
mysql>SELECT HEX('cat');
-> '636174' mysql>SELECT 0x636174;
-> 'cat'
User Comments
Why does "select * from t where md5 like
0xad65.....;" return rows while "select * from t
where md5 = 0xad65...;" not return rows?
The table 't' was declared
create table t (md5 char(16))
and row was inserted as
insert into t (md5) values (0xad65....);
If you've got the iso-8859-15 (aka 'Latin-9')
character set installed, 0xA4. I assume that
you'd have to recompile MySQL for UTF character
sets. [21-Nov-2002]
To compare a column to a hex value, cast the hex value to the column type. For example, to compare a column of type BINARY(8) with the hex value of 0xa0f44ef7a52411de, you could use:
SELECT * FROM SomeTable WHERE BinaryColumn = CAST( x'a0f44ef7a52411de' AS BINARY )
OR (see first comment posted by Case Larsen)
SELECT * FROM t WHERE md5 = CAST( 0xad65... AS CHAR )
Warning x'00...' is converted in null value.
select
x'000bdddc0e9153f5a93447fc3310f710',
x'0bdddc0e9153f5a93447fc3310f710'
first column is null, second column is correct.
As of release 4.1.2 (28 May 2004) MySQL implemented the UNHEX() function which undoes HEX() obviously...thought I would mention as the HEX documentation doesn't specify this and I needed a method of UNHEX'ing a value from a column
I used the two with AES_ENCRYPT/AES_DECRYPT to store and retrieve hex strings once they had been encrypted/decrypted
HEX(AES_ENCRYPT(columnName, 'password'));
AES_DECRYPT(UNHEX(columnName), 'password');
If you use MD5, it returns to you a 32-bit char string
1 row in set (0.00 sec)(for example):
>SELECT MD5(a) FROM t WHERE cond;
c2268fbe786c2718cd5c4d26ce09cb26
If you want to get 16-bit binary of MD5 output you can type:
>SELECT UNHEX(MD5(a)) FROM t WHERE cond;
it is 16-byte output
it looks strage because it contain special characters
Add your own comment.