Reason of searching with INET6_NTON()?

I saw someone says that you can store IPV4 (4bytes) and IPV6 (16bytes) in a column of varbinary(16), and do something like the following code when you want to insert an IP:

INSERT INTO `log_table` (`ip_address`, `created_at`) VALUES(INET6_ATON(?), NOW());

The β€œ?” is the IP to save.

I have no questions with the above code as it is self-explanatory.

But then he says that when you want to search an IP, do this:

SELECT INET6_NTOA(`ip_address`) AS `ip` FROM `log_table` WHERE `ip_address` = INET6_ATON(?);

I don’t understand the reason of selecting INET6_NTOA(`ip_address`) rather than simply selecting `ip_address`.

It does not seem to me that it is because searching with INET6_NTON() is more efficient in this case. I did a benchmark test and found that there is no noticeable difference of speed between selecting INET6_NTOA(`ip_address`) and simply selecting `ip_address`.

Maybe the person who said that misunderstood something?

The two functions convert the IP in question to binary, and then back to regular string format again.

You can just select the data in the column as well, but in that case you will get binary data, and not the IP in string format.

For more information take a look in the manual:
https://dev.mysql.com/doc/refman/8.0/en/miscellaneous-functions.html#function_inet6-aton

2 Likes

Thanks. I understand now. So the INET6_NTOA(`ip_address`) in the second query is the result that is returned, not the thing that is used to search.

That is correct.

1 Like