MySQL Cookbook, 2nd Edition

Sorting Dotted-Quad IP Values in Numeric Order

Problem

You want to sort strings that represent IP numbers in numeric order.

Solution

Break apart the strings, and sort the pieces numerically. Or just use INET_ATON().

Discussion

If a table contains IP numbers represented as strings in dotted-quad notation (111.122.133.144), they’ll sort lexically rather than numerically. To produce a numeric ordering instead, you can sort them as four-part values with each part sorted numerically. Or, to be more efficient, you can represent the IP numbers as 32-bit unsigned integers, which take less space and can be ordered by a simple numeric sort. This section shows both methods.

To sort string-valued dotted-quad IP numbers, use a technique similar to that for sorting hostnames, but with the following differences:

• Dotted quads always have four segments, so there’s no need to add dots to the value before extracting substrings.

• Dotted quads sort left to right, so the order of the substrings used in the ORDER BY clause is opposite to that used for hostname sorting.

• The segments of dotted-quad values are numbers, so add zero to each substring to tell MySQL to use a numeric sort rather than a lexical one.

Suppose that you have a hostip table with a string-valued ip column containing IP numbers:

mysql>SELECT ip FROM hostip ORDER BY ip; +-----------------+ | ip | +-----------------+ | 127.0.0.1 | | 192.168.0.10 | | 192.168.0.2 | | 192.168.1.10 | | 192.168.1.2 | | 21.0.0.1 | | 255.255.255.255 | +-----------------+ ...