CODEWITHSUNDEEP
phpmysqlipipv6ipv4
Andy
Andy

Reputation: 3692

Storing IP address in MySQL database (IPv4 AND IPv6)

Ok, now I'm aware that similar questions have probably been asked a million times but I'm a real novice at this and I'd really appreciate your help here.

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

Thanks in advance

Upvotes: 12

Views: 21842

Answers (5)

Yuvraj Gautam
Yuvraj Gautam

Reputation: 17

You are right but in MySQL 5.7 it is possible to store IPV6 in decimal format. also you can convert to ipv6 to decimal and vice versa:

MySQL 5.0 IPV4

select INET_ATON('192.168.0.1') returns int value: 3232235521

MySQL 5.7 IPV6

select cast(conv(substr(HEX(INET6_ATON('0:0:0:0:0:FFFF:C0A8:0001')), 1, 16), 16, 10) as decimal(65))*18446744073709551616 + 
       cast(conv(substr(HEX(INET6_ATON('0:0:0:0:0:FFFF:C0A8:0001')), 17, 16), 16, 10) as decimal(65))

returns bigint value: 281473913978881

Upvotes: 0

aleroot
aleroot

Reputation: 72686

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

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

and retrieve it back with the function INET_NTOA:

SELECT INET_NTOA(ipcol) AS ip FROM table;

This answered existing before MySQL IPv6 support; users should be made aware that MySQL now natively supports IPv6: https://dev.mysql.com/doc/refman/5.6/en/miscellaneous-functions.html

Upvotes: 12

mittmemo
mittmemo

Reputation: 2090

int(10) unsigned

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

Upvotes: -2

inhan
inhan

Reputation: 7470

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 reverse function, INET_NTOA().

Upvotes: 6

Celada
Celada

Reputation: 22261

There is no integral type in MySQL big enough to store an IPv6 address. The most compact way to store it is as something like BINARY(16). If you just need to store and retrieve addresses and you don't need to perform logical operations on them (e.g. netmask operations to query for which IP addresses come under a covering prefix) then that will be enough. If you need to do logical or bit operations, you will need to be fancier: you will need to store IPv6 addresses in two separate 64-bit integer columns.

Upvotes: 1

Related Questions