MYSQL - SELECT IP v4 / v6, inet_pton & bin2hex

Question

I had a problem choosing the right values ​​on my mysql sql server.

ip can be ipv6 and v4.

Table: User {

...

ip binary (16)

}

$ip = '192.168.10.115'; $ip = bin2hex(inet_pton($ip)); // Returns c0a80a73 $result = $this->db->select("SELECT * FROM User WHERE HEX(ip) = $ip"); // $result empty because in db its stored as: // HEX(ip) = C0A80A73000000000000000000000000 

How can I get a viable match with * 00000 *?

If the input was ipv6, that would be ok, but ip v4 is not.

Answer 1

Can you use VARBINARY instead of BINARY ?

From the MySQL manual in Binary / Varbinary :

If the resulting value should be the same as the value specified for the vault without padding, it might be preferable to use VARBINARY or one of the BLOB data types.

Answer 2

UPDATE:

MySQL 5.6.3 and above support IPv6 addresses - see the following: "INET6_ATON (expr)"

The data type is VARBINARY(16) instead of BINARY(16) , as suggested by earlier comments here. The only reason for this is that the MySQL functions work for both IPv6 and IPv4 addresses. BINARY(16) is suitable for storing only IPv6 addresses and stores one byte. VARBINARY(16) should be used when working with IPv6 and IPv4 addresses.