Why isn't this string to binary conversion working?

Question

I am storing IPv6 addresses as BINARY(16) and trying to search through them using the hexadecimal representation, eg: FFFFFFFF000000000000000000000000.

This works:

SELECT * FROM ipv6 WHERE HEX(address) = 'FFFFFFFF000000000000000000000000'

However, this does not:

SELECT * FROM ipv6 WHERE address = CONV('FFFFFFFF000000000000000000000000', 16, 2)

No error messages, just doesn't return any results. Is MySQL unable to handle the conversion using CONV()?

I could use the first option, but I imagine the second option is much faster in a large database as it isn't having to convert every address in the table.

Update:

UNHEX() seems to work great as pointed out by Jack. Standard conversions (as of 5.6) using CONV() seems to max out at 8 bytes (64-bit) or in hex form FFFFFFFFFFFFFFFF so it can't handle a full 128-bit ipv6 address.

After some deliberation, I believe Salman's answer is the best way to handle hexidecimals (using hexidecimal literals). However, unhex(), afaik, is required if you are using parameter binding in PHP since quotes will be added and will prevent MySQL from evaluating it as a literal versus a char string.

Therefore you will have to choose the best solution for you. Hexidecimal literals is a little bit faster but if you're handling user input, then unhex with parameter binding may be the best solution.

Answer 1

If you don't want to convert each item to hexadecimal, you can do the opposite:

SELECT *
FROM ipv6
WHERE address = UNHEX('FFFFFFFF000000000000000000000000');

This will do binary string comparisons as you would expect.