What type should I store IP addresses for MySQL?

I was going to use varchar(20), but I was开发者_运维百科 wondering what should if I should do INT and strip off the periods instead. What would be better and why?

I presume you're only interested in IPv4 addresses, not IPv6.

I would use an INT UNSIGNED for the column, and then use INET_ATON and INET_NTOA to convert back and forth between the textual representation and the int value.

mysql> SELECT INET_ATON('192.168.10.50');
+----------------------------+
| INET_ATON('192.168.10.50') |
+----------------------------+
|                 3232238130 |
+----------------------------+
1 row in set (0.00 sec)

mysql> SELECT INET_NTOA(3232238130);
+-----------------------+
| INET_NTOA(3232238130) |
+-----------------------+
| 192.168.10.50         |
+-----------------------+
1 row in set (0.00 sec)

store IPV4 as int unsigned

http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#function_inet-aton

http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#function_inet-ntoa

If you're really concerned about saving space, you can pack it into a 4-byte int. Each block of an IPv4 IP address can have 256 possible values, which just happens to be the range of a single byte.

-- Edit --> What I just described can be done with the mysql functions f00 linked in his answer

However, storing it as a string will save a bit of coding time. Honestly, unless you're doing this on a very large scale, the optimization from a denser storage format won't matter.

Just in case you already switch from MySQL to MariaDB or people (like me) did research on how to store IP addresses in MariaDB.

There is a new datatype known as INET6, it works for both IP version.

Documentation can be found here.