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?
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 only takes a minute to sign up.Sign up to join this community
Don't store as a string. Use an
int unsigned column and store/retrieve with
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.
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 ( '220.127.116.11'::inet ); INSERT INTO test ( address ) VALUES ( 'a:b::c:d'::inet ); SELECT * FROM test; test_id | address ---------+---------- 1 | 18.104.22.168 2 | a:b::c:d
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.
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.
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
192 + (168 * 2^8) + (10 * 2^16) + (50 * 2^24) = 839559360 (Backwards, results in 22.214.171.124)
In MySQL, you can directly use
SELECT INET_NTOA(3232238130);to get
As of MySQL v5.6.3 they added support for
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
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