Order by .... numbers? Help me sort the ip addresses

Question

MySQL trying to get a list of ip addresses in order.

this request

select ip from sn_192_168_0 

gives it

192.168.0.1
192.168.0.10
192.168.0.100
192.168.0.101

We want

192.168.0.1
192.168.0.2
...snip..
192.168.0.10

Answer 1

Try the INET_ATON function

SELECT ip FROM sn_192_168_0
ORDER BY INET_ATON(ip);

Give it a try !!!

CAVEAT: Better not store the values ​​of INET_ATON. There are some past quirks with this feature: you have invalid numbers between points and call them to triggers.

• http://bugs.mysql.com/bug.php?id=5448
• http://bugs.mysql.com/bug.php?id=39386

Now these errors are cleared.

Short IP addresses are handled appropriately. Here is an example from MySQL 5.5.12 on Windows 7

mysql> SELECT INET_ATON('127.0.0.1'), INET_ATON('127.1');
+------------------------+--------------------+
| INET_ATON('127.0.0.1') | INET_ATON('127.1') |
+------------------------+--------------------+
|             2130706433 |         2130706433 |
+------------------------+--------------------+
1 row in set (0.05 sec)

Answer 2

you can use

SELECT ip FROM sn_192_168_0 ORDER BY LPAD(  ip, 16, 0 ) 

number 16 is the maximum length of ip