Tag: ip

MySQL does not provide special datatype or operators for IP addresses, but has two functions, that convert IP addresses to and from integers. In order to lookup addresses in a subnet, an efficient index range scan query could be used:

SELECT ip FROM table WHERE ip BETWEEN
	INET_NTOA("213.197.137.0") AND
	INET_NTOA("213.197.137.255")

In order to make it easier, we can have several handy functions, that simplify the task a bit:

SELECT ip FROM table WHERE ip BETWEEN
	inet_aton_net("213.197.137.3/24") AND
	inet_aton_bc("213.197.137.3/24")

I intentionally didn’t create/use function that would check IPs existence in network, as optimizer wouldn’t know it can be converted to efficient range scan, though, some operators like… LIKE are optimized properly.

CREATE FUNCTION inet_aton_net (ip VARCHAR(18))
	RETURNS INTEGER UNSIGNED
	DETERMINISTIC
BEGIN
	DECLARE nm INTEGER UNSIGNED DEFAULT 0;
	SET nm=32-SUBSTRING(ip,LOCATE("/",ip)+1);
RETURN
	-- shift right and left looses host bits
	INET_ATON(LEFT(ip,LOCATE("/",ip)-1)) >> nm << nm;
END

CREATE FUNCTION inet_aton_bc (ip VARCHAR(18))
	RETURNS INTEGER UNSIGNED
	DETERMINISTIC
BEGIN
	DECLARE nm INTEGER UNSIGNED DEFAULT 0;
	SET nm=SUBSTRING(ip,LOCATE("/",ip)+1);
RETURN
	-- ip ORed with inverse netmask provides broadcast address
	INET_ATON(LEFT(ip,LOCATE("/",ip)-1)) | (4294967295>>nm);
END