From: http://dev.mysql.com/doc/refman/5.7/en/bit-functions.html#c9386
Posted by Neil Davis on April 3, 2008
Revised by Phillip Temple on September 11, 2008
If you store your ip addresses as a 32 bit unsigned integer representation instead of strings(using INET_ATON()), you can use bitwise operators to pull the octets for grouping and sorting when you need to retrieve them:
select
ipAddress, (ipAddress >> 24) as firstOctet,
(ipAddress>>16) & 255 as secondOctet,
(ipAddress>>8) & 255 as thirdOctet,
ipAddress & 255 as fourthOctet
from ips;Result:
+------------+------------+-------------+------------+-------------+
| ipAddress | firstOctet | secondOctet | thirdOctet | fourthOctet |
+------------+------------+-------------+------------+-------------+
| 2082027709 | 124 | 25 | 56 | 189 |
| 2082027710 | 124 | 25 | 56 | 190 |
| 2082027711 | 124 | 25 | 56 | 191 |
| 2082093145 | 124 | 26 | 56 | 89 |
| 2082093146 | 124 | 26 | 56 | 90 |
+------------+------------+-------------+------------+-------------+
This way you don't need a string parser to separate the octets for display, drill down menus, sorting etc, and don't need to tear the octets apart with a string parser to put them in separate fields.
If these are your IP addresses I sincerely apologize. I just pulled them out of thin air to write this sql :D
There are 10 types of people in the world... Sorry had to throw that in there 8)
There are probably "easier" ways to do this, but they aren't as efficient. I had a requirement for a tree view of ip addresses and this seems to be the easiest way to do it.
-Neil