Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

6 Answers

up vote 19 down vote accepted

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.

share|improve this answer
    
Can you expand this answer as to why? –  Gaius Mar 11 '11 at 13:07
    
@Gaius edited answer to explain a bit more... –  Mr Shunz Mar 11 '11 at 13:39
    
shunz thanks :-) –  Gaius Mar 11 '11 at 13:50
1  
-1, IP addresses are up to 128 bit and the largest integer type supported by MySQL is 64 bit. –  Hendrik Brummermann Mar 13 '11 at 10:24
1  
IPv4 is 32-bit. 128-bit is for IPv6, which, as he mentioned, the INET_* stuff doesn't support. –  Richard Aug 12 '11 at 15:06
add comment

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.

share|improve this answer
1  
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
add comment

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
share|improve this answer
    
To get which IP's are in a network SELECT * FROM test WHERE address << '1.2.3.0/24'::inet; –  jkj Mar 14 '11 at 10:17
add comment

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.

share|improve this answer
    
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
2  
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
add comment

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.

share|improve this answer
    
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
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.