All we need is an easy explanation of the problem, so here it is.
I am storing ips using
VARBINARY(16) data type. I’d like to select just the ipv4 ips. Is it possible to filter column by the lenght used in
VARCHAR)? Something like this
SELECT INET6_NTOA(`ip`) from TABLE where BYTESLENGHT(`ip`) = 4
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
ip_n is the "numeric" version, then
will be 16 for IPv6 and something smaller for IPv4.
mysql> SELECT LENGTH(INET_ATON('22.214.171.124')) AS v4, LENGTH(INET6_ATON('f::f')) AS v6; +------+------+ | v4 | v6 | +------+------+ | 9 | 16 | +------+------+
If you were starting a string ("A"), this will be true (non-zero) for
ip_a (string version) if it is IPv6, or false (zero) for IPv4:
There is a IS_IPV4 function as a mechanism too.
SELECT INET6_NTOA(ip) FROM TABLE WHERE IS_IPV4(INET6_NTOA(ip))
Note MariaDB-10.5 has a INET6 data type too.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂