Storing IP address

Question

I have to store the IP address of all registered users in the database. I am wondering, how many characters should I declare for such a column?

Should I support IPv6 as well? If so, what is the maximum length of IP address?

Answer 1

Don't store as a string. Use an int unsigned column and store/retrieve with INET_ATON() and INET_NTOA() respectively. AFAIK mysql doesn't support INET_* for ipv6.

EDIT as per comment

Using built in function to converto IPs to/from integers (and so storing those integers in the database) has the side effect of automatically validate those IPs. Say you store an IP as a VARCHAR(16), you have to make sure not to store invalid IPs (like 999.999.999.999 as an example) with some custom validation. INET_* functions take care of that.

Answer 2

I'd suggest migration to PostgreSQL and use of INET or CIDR data types.

CREATE TABLE test ( test_id serial PRIMARY KEY, address inet );
INSERT INTO test ( address ) VALUES ( '1.2.3.4'::inet );
INSERT INTO test ( address ) VALUES ( 'a:b::c:d'::inet );
SELECT * FROM test;
 test_id | address  
---------+----------
       1 | 1.2.3.4
       2 | a:b::c:d

Answer 3

It is probably time to start considering IPv6. MySQL does not have methods to convert IPv6 addresses to binary format. A forty character string will handle any normal IPv6 addresses. There is a format that could exceed 40 characters, I would consider those unlikely to occur practice.

You can calculate the size from then information that there will be at most 8 four character groups with 7 separator characters. The abnormal format replaces the last two groups with an IPv4 format address. With no address compression it replaces the last 9 characters with up to 15 characters.

If you are storing blocks, the block size indication can take 4 characters rather than the 3 characters required for IPv4.

You should ensure the formatting you get is consistent, but all the software I have seen gives consistent formats for the addresses.

Answer 4

Here is the best answer made in one of the MySQL mailing lists. Read Best Fieldtype to store IP address....

Briefly it suggests, which I second, to use INT(10) UNSIGNED.

1. It uses less memory (4 bytes only)
2. Best for sorting and searching the IP ranges, especially if you look for country of origin of your visitors.

So, using 192.168.10.50:

(192 * 2^24) + (168 * 2^16) + (10 * 2^8) + 50 = 3232238130 (results in 192.168.10.50)

In MySQL, you can directly use SELECT INET_ATON('192.168.10.50'); to get 3232238130.

Or

192 + (168 * 2^8) + (10 * 2^16) + (50 * 2^24) = 839559360 (Backwards, results in 50.10.168.192)

In MySQL, you can directly use SELECT INET_NTOA(3232238130); to get 192.168.10.50 back.

Answer 5

As of MySQL v5.6.3 they added support for INET6_ATON and INET6_NOTA that will take care of IPv4 and IPv6 addresses. But they no longer store it as an integer. IPv6 returns a varbinary(16) and and IPv4 returns a varbinary(4).

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

Answer 6

You can store up to 15 characaters. Please do not use VARCHAR(15) because that is 16 bytes (first byte manages string length and thus slower retrieval and storage). Use CHAR(15) always on something like an IP address.

Answer 7

Sorry, can't comment on answers. There is a question about it on stackoverflow. And I totally agree with selected answer: using 2xBIGINT is probably the best way for ipv6 currently.

I'd suggest going for 2 * BIGINT, but make sure they're UNSIGNED. There's a sort of a natural split at the /64 address boundary in IPv6 (since a /64 is the smallest netblock size) which would align nicely with that.

It is also possible to store ipv4 on this bigints - either by marking one of them NULL or by using V4COMPAT format