Insert hex values into MySql
Solution 1
You can use UNHEX()
function to convert a hexstring with hex pairs to binary and HEX()
to do the other way round.
I.e.
INSERT INTO tbl (col) VALUES (UNHEX('4D2AFF'))
and
SELECT HEX(col) FROM tbl
Solution 2
Or you can do even this:
INSERT INTO tbl (col) VALUES (X'4D2AFF')
See this for some more info.
Solution 3
The Hex() and Unhex() functions were already mentioned, but I'd like to weigh in as well on an alternative pattern.
How are you using the strings before and after? If you can avoid coversion until the last possible minute, that is highly preferential. That way you don't risk something going wrong, or forgetting whether or not the object you've extracted from the DB has already been converted or not.
Solution 4
Here's a great blog post I always refer to to remind myself of the proper handling of hex values and binary fields, and lays out some performance implications.
http://www.xaprb.com/blog/2009/02/12/5-ways-to-make-hexadecimal-identifiers-perform-better-on-mysql/
Related videos on Youtube
Tim
Updated on July 09, 2022Comments
-
Tim almost 2 years
I have a table with a VARBINARY column. I need to insert a string such as '4D2AFF' which represents the hex values 0x4D, 0x2A and 0xFF respectively. How do I construct this statement?
-
cowbert over 7 yearsNote that even in MySQL 5.6, the X'' notation has a length limit in the reference mysql client and UNHEX() does not (appear to). I do not know what the limit is for X'', as it is not officially documented but I have encountered it when trying to INSERT a BLOB. With X'' literal, mysql client threw a syntax error with a sufficiently long hex sequence while UNHEX() of the same sequence did not.