Recipe 7.13. 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 | +-----------------+ 

The preceding query produces output sorted in lexical order. To sort the ip values numerically, you can extract each segment and add zero to convert it to a number using an ORDER BY clause like this:

mysql> SELECT ip FROM hostip     -> ORDER BY     -> SUBSTRING_INDEX(ip,'.',1)+0,     -> SUBSTRING_INDEX(SUBSTRING_INDEX(ip,'.',-3),'.',1)+0,     -> SUBSTRING_INDEX(SUBSTRING_INDEX(ip,'.',-2),'.',1)+0,     -> SUBSTRING_INDEX(ip,'.',-1)+0; +-----------------+ | ip              | +-----------------+ | 21.0.0.1        | | 127.0.0.1       | | 192.168.0.2     | | 192.168.0.10    | | 192.168.1.2     | | 192.168.1.10    | | 255.255.255.255 | +-----------------+ 

However, although that ORDER BY produces a correct result, it involves a lot of messing around. A simpler solution is possible: use the INET_ATON⁠(⁠ ⁠ ⁠) function to convert network addresses in string form directly to their underlying numeric values and sort those numbers:

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

If you're tempted to sort by simply adding zero to the ip value and using ORDER BY on the result, consider the values that kind of string-to-number conversion actually produces:

mysql> SELECT ip, ip+0 FROM hostip; +-----------------+---------+ | ip              | ip+0    | +-----------------+---------+ | 127.0.0.1       |     127 | | 192.168.0.2     | 192.168 | | 192.168.0.10    | 192.168 | | 192.168.1.2     | 192.168 | | 192.168.1.10    | 192.168 | | 255.255.255.255 | 255.255 | | 21.0.0.1        |      21 | +-----------------+---------+ 

The conversion retains only as much of each value as can be interpreted as a valid number. The remainder would be unavailable for sorting purposes, even though it's necessary to produce a correct ordering.

Use of INET_ATON⁠(⁠ ⁠ ⁠) in the ORDER BY clause is more efficient than six SUBSTRING_INDEX⁠(⁠ ⁠ ⁠) calls. Moreover, if you're willing to consider storing IP addresses as numbers rather than as strings, you avoid having to perform any conversion at all when sorting. You gain an additional benefit as well because if you index the column, the query optimizer may be able to use the index for certain queries. Numeric IP addresses have 32 bits, so you can use an INT UNSIGNED column to store them. For cases when you need to display those values in dotted-quad notation, convert them with the INET_NTOA⁠(⁠ ⁠ ⁠) function.




MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2004
Pages: 375
Authors: Paul DuBois

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net