Why does this line not work in binary conversion?

Question

I save the IPv6 addresses as BINARY(16) and try to search for them using the hexadecimal representation, for example: FFFFFFFF000000000000000000000000 .

It works:

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

However, it is not:

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

There are no error messages, just does not return any results. Can MySQL handle conversion using CONV() ?

I could use the first option, but I assume that the second option is much faster in a large database, since it does not need to convert each address to a table.

Update:

UNHEX() seems to work just fine, as Jack pointed out. Standard conversions (from 5.6) using CONV() seem to be maximum in 8 bytes (64-bit) or in hexadecimal form FFFFFFFFFFFFFFFF , so it cannot handle the full 128-bit ipv6 address.

After some discussion, I find that Salman's answer is the best way to handle hexidecimals (using hexidecimal literals). However, UNHEX() , afaik, is required if you use parameter binding in PHP, because the quotes will be added and will not allow MySQL to evaluate it as a literal compared to the char string.

Therefore, you need to choose the best solution for you. Hexidecimal literals are a little faster, but if you're processing user input, then unhex with parameter binding might be the best solution.

Answer 1

You can use hexadecimal literals :

 SELECT * FROM ipv6 WHERE address = 0xFFFFFFFF000000000000000000000000; SELECT * FROM ipv6 WHERE address = X'FFFFFFFF000000000000000000000000'; 

If you use PDO, you can simply do this:

 # insert packed address $stmt = $db->prepare("INSERT INTO ipv6(address) VALUES (?)"); $stmt->execute(array(inet_pton("2001:0DB8:85A3:0000:0000:8A2E:0370:7334"))); # select packed address $stmt = $db->prepare("SELECT * FROM ipv6 WHERE address = ?"); $stmt->execute(array(inet_pton("2001:0DB8:85A3:0000:0000:8A2E:0370:7334"))); $rows = $stmt->fetchAll(PDO::FETCH_ASSOC); # verify the result echo inet_ntop($rows[0]["address"]); # 2001:db8:85a3::8a2e:370:7334 

Answer 2

If you do not want to convert each element to hex, you can do the opposite:

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

This will do binary string comparisons as you expected.