difference between UNHEX and X (MySQL)

Question

What really is the difference between MySQL UNHEX and X when dealing with hexadecimal values in a database?

Eg.

SELECT * FROM test WHERE guidCol IN (UNHEX('hexadecimalstring'));

SELECT * FROM test WHERE guidCol IN (X'hexadecimalstring');

Both gives me exact result set. So is there any difference? Performance implications?

Edit: the underlying type of guidCol is binary of course

Answer 1

UNHEX() is a function, therefore you can do something like

SET @var = '41';
SELECT UNHEX(@var);
SELECT UNHEX(hex_column) FROM my_table;

X, on the other hand, is the syntax for a hexadecimal litteral. You cannot do this:

SET @var = '41';
SELECT X@var; -- error (string litteral expected)
SELECT X'@var'; -- error (`@` is not a hexadecimal digit)
SELECT X(@var); -- returns NULL, not too sure about the reason... [edit: but this is probably why you are inserting NULL values]
SELECT X(hex_column) FROM my_table; -- returns NULL as well

This explains why you always get better performance with X: you are using a language construct instead of a function call. X does not need to evaluate a variable, since it expects a litteral string.

Answer 2

Note 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. Obviously, length is not an issue when it comes to an actual GUID, but I figured this is useful for anyone else using this question to answer mysql insertion of binary data in the general case.