Monday, April 4, 2011

How to store IP address value in integer datatype column.

Most of the time, we use string datatype column like char or varchar to store ip address values. There is a way to store ip address value in numeric datatype column like unsigned interger. There is miscellaneous functions INET_ATON(), INET_NTOA() using which it's possible

INET_ATON() Return the numeric value of an IP address
INET_NTOA() Return the IP address from a numeric value

SELECT INET_ATON('192.168.1.5') AS IP;
+------------+
| IP |
+------------+
| 3232235781 |
+------------+
1 row in set (0.00 sec)
The generated number is always in network byte order. For example
192x256^3 + 168x256^2 + 1x256^1 + 5
SELECT INET_NTOA('3232235781') AS IP;
+-------------+
| IP |
+-------------+
| 192.168.1.5 |
+-------------+
1 row in set (0.00 sec)

5 comments:

  1. Generally, that's not a very good idea, as it makes supporting other address families (think IPv6) a lot harder. Just use a varchar.

    ReplyDelete
  2. The only downside is that that'll only work for IPv4 and not for IPv6. There are UDF's available for IPv6 and I thought that I saw a commit for trunk, but bzr log disagrees with me.

    Storing it as a int makes it easier to sort and to see it an IP is within a specific range and such.

    ReplyDelete
  3. Or use postgres: http://www.postgresql.org/docs/8.3/static/datatype-net-types.html

    You even get ipv6 support that way.

    ReplyDelete
  4. Very Good informative article. I would also like to know what is the advantage of storing ip address as integer??. Is data is smaller size??

    ReplyDelete
  5. Good tip. I have a contact form from php forms and sometimes I need this function to store ip's.

    ReplyDelete