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?


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.


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.

  • 2
    I totally agree, IPv6 is coming and it's better to be ready for it than to be waiting for it like Y2K :D – Jeff Mar 13 '11 at 4:09
  • Not only coming, but already here on my systems. – BillThor Aug 21 '11 at 8:01

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 ( ''::inet );
INSERT INTO test ( address ) VALUES ( 'a:b::c:d'::inet );
 test_id | address  
       1 |
       2 | a:b::c:d
  • To get which IP's are in a network SELECT * FROM test WHERE address << ''::inet; – jkj Mar 14 '11 at 10:17

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 * 2^24) + (168 * 2^16) + (10 * 2^8) + 50 = 3232238130 (results in

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


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

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

  • Impressive, +1 for You !!! Using 4 bytes unsigned sure beats string manipulation anyday. – RolandoMySQLDBA Mar 14 '11 at 15:27
  • -1, IP addresses are up to 128 bit and the largest integer type supported by MySQL is 64 bit. – Hendrik Brummermann Mar 15 '11 at 17:39
  • 3
    nhnb, IPv6 is 128 bit, but the conversation is about IPv4 which IS 32 bit. No need to comment on each comment/answer insisting on your knowledge. – Eye Mar 16 '11 at 5:32
  • Your answer does not mention that it only deals with the old internet protocol, whereas the question explicitly mentions IPv6 at the end. Given that the last mayor internet provider (at least in my country) will support IPv6 before the end of this year, it is an extremely bad idea to design a database structure that cannot handle it. – Hendrik Brummermann Mar 17 '11 at 22:12

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).



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.

  • The max length of an IP address is 45 characters. – Hendrik Brummermann Mar 13 '11 at 10:27
  • Will CHAR not pad it with spaces? – Gaius Mar 14 '11 at 6:57

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

Your Answer

