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
Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.
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.
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0