I’ve been working on a web-based tool that stores, among other network-related things, IP addresses. When I first started I stored each IP address as four TINYINTS (0-255 for each octet):
mysql> DESC ipaddresses;
+----------+---------------------+------+-----+---------+----------------+
| FIELD | Type | NULL | KEY | DEFAULT | Extra |
+----------+---------------------+------+-----+---------+----------------+
| id | int(10) UNSIGNED | NO | PRI | NULL | AUTO_INCREMENT |
| A | tinyint(3) UNSIGNED | NO | | NULL | |
| B | tinyint(3) UNSIGNED | NO | | NULL | |
| C | tinyint(3) UNSIGNED | NO | | NULL | |
| D | tinyint(3) UNSIGNED | NO | | NULL | |
+----------+---------------------+------+-----+---------+----------------+
5 rows IN SET (0.00 sec)
mysql> SELECT * FROM ipaddresses WHERE id=1
+----+----+----+----+-----+
| id | A | B | C | D |
+----+----+----+----+-----+
| 1 | 10 | 20 | 30 | 131 |
+----+----+----+----+-----+
1 row IN SET (0.02 sec)
As I started manipulating these addresses I found it awkward to do common binary math (like bitwise ANDs). I decided instead to store these 32-bit values as unsigned integers (of length 32). To make my life easier yet, MySQL and PHP both have native functions to convert IP addresses between my old and new formats to make this migration extremely easy.
» Read more: IP Addresses in PHP/MySQL
