MySQL Tips

MySQL has come of age with Version 5. We use it as the database of choice because it is fast, reliable and robust.

During the course of our work we have run accress a number of times we were required to create unusual queries. We will share these query methods with you as we encounter them.

How to sort or group on CIDR

I found myself having to detect user download accesses for a client with the user possibly using different IP's within the same classless subnet. The user's IP is recorded as part of the session, and the client then updates the netmask using CIDR notation. The IP is stored as varchar(15) and the netmask may be stored as int or char(2) with a default value of 32.

CREATE TABLE logtable (
.... ,
ip varchar(15) default "",
netmask int default 32,
.... )

MySQL has two functions, INET_ATON and INET_NTOA which convert dotted quad notation to an integer and back. It is easy to sort by IP using INET_ATON(ip). However grouping by subnet requires an understanding of the bitwise operators in MySQL.

When two numbers are compared using the MySQL bitwise operators, the numbers and the result are cast to bigint (64 bits). To create a bit mask for the network address, which is 32 bits, we must first shift the mask the required number of bits, then we have to mask the lower 32 bits to create the bit mask for the subnet:

Thus the SQL becomes:

INET_NTOA((INET_ATON("") << (32 - netmask))
& INET_ATON("")) AS bitmask,
& ((INET_ATON("") << (32 - netmask))
& INET_ATON(""))) AS subnet
FROM logtable;

For an IP of and a netmask of 24, this will give:

| ip | bitmask | subnet |
| | | |

The SQL might be a little more readable, and a tad more efficient, if we substitute the actual integer:

INET_ATON("") = 4294967295

INET_NTOA((4294967295 << (32 - netmask))
& 4294967295) AS bitmask,
& ((4294967295 << (32 - netmask))
& 4294967295)) AS subnet
FROM logtable;