利用MySql内置函数对IP进行排序

INET_ATON(expr)


Given the dotted-quad representation of a network address as a string, returns an integer that represents the numeric value of the address. Addresses may be 4- or 8-byte addresses.


mysql> SELECT INET_ATON('209.207.224.40');
        -> 3520061480
The generated number is always in network byte order. For the example just shown, the number is calculated as 209×2563 + 207×2562 + 224×256 + 40.


INET_ATON() also understands short-form IP addresses:


mysql> SELECT INET_ATON('127.0.0.1'), INET_ATON('127.1');
        -> 2130706433, 2130706433
Note: When storing values generated by INET_ATON(), it is recommended that you use an INT UNSIGNED column. If you use a (signed) INT column, values corresponding to IP addresses for which the first octet is greater than 127 cannot be stored correctly. See Section 11.2, “Numeric Types”.


INET_NTOA(expr)


Given a numeric network address (4 or 8 byte), returns the dotted-quad representation of the address as a string.


mysql> SELECT INET_NTOA(3520061480);
        -> '209.207.224.40'


以上是Mysql手册中的说明，下面用实例来说明如何用这些函数进行排序




假设ip是以字符串形式存储在表table_device的device_ip字段中，则以下这句可以返回以ip排序的结果


select device_ip from table_device order by inet_aton(device_ip)


结果：


192.168.0.1


192.168.0.51


92.168.0.111


92.168.0.223