patternsqlMajor
Storing IP address
Viewed 0 times
storingaddressstackoverflow
Problem
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?
Should I support IPv6 as well? If so, what is the maximum length of IP address?
Solution
Don't store as a string. Use an
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.
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.
Context
StackExchange Database Administrators Q#1694, answer score: 29
Revisions (0)
No revisions yet.