There are times that you want to store IPv4 addresses in MySQL (IPv6 has still some way to go). A quick and dirty solution is to store the addresses in a varchar(15) column, but this solution exhibits the problem of sorting the addresses. For example, 10.3.8.9 comes first compared to 10.240.8.9, while the opposite should apply. Of course, you can always splits the octets and sort them individually or generate a unique integer for each IP address, but this involves a bit of coding.
It's been a couple of days that I've discovered that MySQL provides two functions for the purpose I describe above. INET_ATON() accepts an IP address and returns an integer, while INET_NTOA() does exactly the opposite.
So, what's the deal? You can sort rows by an IP address. Sorting by IP addresses surely is not what you need every day, but when this functionality is necessary, these two functions come handy. So:
mysql> select INET_ATON('10.3.8.9');
+-----------------------+
| INET_ATON('10.3.8.9') |
+-----------------------+
| 167970825 |
+-----------------------+
1 row in set (0.00 sec)
mysql> select INET_ATON('10.240.8.9');
+-------------------------+
| INET_ATON('10.240.8.9') |
+-------------------------+
| 183502857 |
+-------------------------+
1 row in set (0.00 sec)
So, store your IP addresses as integers, sort them as you see fit and display them right. This of course is MySQL specific, but MySQL is widely used for a range of web applications.
)...
