How do I store an IP Address in MySQL? INET_AtoN!

One of the most popular questions out there is, “How do I store an IP Address in MySQL?”.   The fastest and most compact way to store an IP address in the format “10.51.1.10” is as an unsigned integer.  If you store the actual values as a char(15), you will require 11 extra bytes per row.  In addition, using IP as a condition when querying is much faster when using an unsigned integer.  Testing has shown that range queries can result in as much as a 35 million times improvement over storing an IP address as a character string.  Yes, that is 35 million!

Simply define IP as an INT UNSIGNED.

Use MySQL’s built-in function INET_ATON() to convert the string representation of the IP Address into an unsigned Integer.  INET_NTOA() will perform the reverse translation.

This will result in space savings, and faster queries, who could ask for more than that?

• ← Show Processlist and Show Full Processlist – MySQL Tip
• MySQL Delimiters – Or why I hate stored procedures and Error Code : 1064 You have an error in your SQL syntax. →