将IP存储到mysql数据库中

Question

I'm using a simple way to get the real ip. I found it here: How to get Real IP from Visitor?

The IP will be stored into database, in some column width VARCHAR 15. I'm using Varchar with length 15 because I understand that the ip address can be 123.456.789.123, which has 12 numbers and 3 dots.

Now... I saw that the IPv6 has more chars. I do not understand. All users have a particular IPv4 or may be some users with IPv6 and not IPv4 ?

I need to understand because I want to optimize this column of IP, as I saw here: insert ip into mysql is better to make it decimal.

How to should I make this column decimal if the ip (version 6) can have more than 15 chars ?

I'm confuse...

Answer 1

For handling and storing both IPv4 and IPv6 addresses, you can use datatype VARBINARY(16).

In version 5.6, MySQL (finally!) introduced conversion functions for IPv6 addresses: INET6_ATON, so you don't have to do the conversion in your application.

If you are handling only IPv4 addresses, you can continue to use the INET6_ATON function, and BINARY(4) is a suitable datatype for storing it.

Conveniently, the inverse functions, for converting from the binary representation back to dotted decimal form (for IPv4 addresses) or the hex colon form (for IPv6 addresses) are also available.

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

Q: How to should I make this column decimal if the ip (version 6) can have more than 15 chars ?

A: Don't make the column DECIMAL, make it VARBINARY(16), and convert both the IPv4 dotted decimal representation, and the IPv6 hex colon representation into a binary representatin to store it.