difference between UNHEX and X (MySQL)
guid, hex, mysql
Solution
`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.
Problem
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