Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Storing IP address in MySQL database (IPv4 AND IPv6)

Tags:

php

mysql

ip

ipv6

ipv4

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

like image 436
Andy Avatar asked May 14 '12 19:05

Andy


1 Answers

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

like image 149
aleroot Avatar answered Sep 19 '22 17:09

aleroot