Storing an IP address in a MySQL database (IPv4 and IPv6)

Question

Well, now I know that similar questions have probably been asked a million times, but I'm a real novice, and I really appreciate your help here.

Basically, I want to save the IP address of visitors in MySQL for later search and verification. First of all, I need to know what type of field I need to use to store the IP address. I would also like to make the system compatible with IPv6 addresses, if possible.

Thanks in advance

Answer 1

To save IPv4, you can use INT UNSIGNED , while for IPv6 you need decimal(39,0), , to save ip in a table, you can use the INET_ATON function:

 INSERT INTO table (ipcol) VALUES (INET_ATON('192.168.0.10')); 

and return it using the INET_NTOA function:

 SELECT INET_NTOA(ipcol) AS ip FROM table; 

This responded to pre-MySQL IPv6 support; users should be aware that MySQL now supports IPv6: https://dev.mysql.com/doc/refman/5.6/en/miscellaneous-functions.html

Answer 2

I use VARBINARY (16) for the data type and use the MySQL function INET_ATON() to insert the IP number (which I later read using the INET_NTOA() callback function.

Answer 3

MySQL does not have an integral type large enough to hold an IPv6 address. The most compact way to store it is something like BINARY(16) . If you just need to store and retrieve addresses, and you do not need to perform logical operations on them (for example, netmask operations for a query for which IP addresses fall under the coverage prefix), this will be enough. If you need to perform logical or bitwise operations, you need to be more attractive: you will need to store IPv6 addresses in two separate 64-bit integer columns.

Answer 4

int(10) unsigned

http://lists.mysql.com/cluster/2781