Which MySQL datatype to use for an IP address?

Question

Possible Duplicate:
How to store an IP in mySQL

I want to get the IP address from $_SERVER['REMOTE_ADDR'] and some other $_SERVER variables, which datatype is the right one for this?

Is it VARCHAR(n)?

See this [discussion](http://forums.mysql.com/read.php?21,49094,49094#msg-49094) — Ragnar123, Feb 27 '11 at 13:57
I disagree that this is a duplicate question, since the other question is specifically a performance-related question for storing a very large number of ip addresses. The answer to that question might not be relevant to someone who is not interested in maximizing space usage or performance. — User, Sep 11 '12 at 01:25
@User I've asked this question about one and a half years ago ;) But thanks for sharing your opinion. — ComFreek, Sep 11 '12 at 08:12
Even though this question is a _possible_ duplicate. Here I have found better answers. Those who marked it as duplicate, made me waste my time :-P — Pathros, Mar 08 '16 at 16:52
Can anyone please unmark this as a duplicate. The real answer is in this question. — theking2, Jul 15 '19 at 12:42
It feels like every third SO answer I search for, the top result is "[DUPLICATE]". I think some people just like closing questions, and whether it's correct or not isn't really important. — Glenn Maynard, Mar 17 '23 at 00:43

score 158 · Accepted Answer · edited Jun 21 '13 at 14:35

158

Since IPv4 addresses are 4 byte long, you could use an INT (UNSIGNED) that has exactly 4 bytes:

`ipv4` INT UNSIGNED

And INET_ATON and INET_NTOA to convert them:

INSERT INTO `table` (`ipv4`) VALUES (INET_ATON("127.0.0.1"));
SELECT INET_NTOA(`ipv4`) FROM `table`;

For IPv6 addresses you could use a BINARY instead:

`ipv6` BINARY(16)

And use PHP’s inet_pton and inet_ntop for conversion:

'INSERT INTO `table` (`ipv6`) VALUES ("'.mysqli_real_escape_string(inet_pton('2001:4860:a005::68')).'")'
'SELECT `ipv6` FROM `table`'
$ipv6 = inet_pton($row['ipv6']);

edited Jun 21 '13 at 14:35

ComFreek

29,044
18
104
156

answered Feb 27 '11 at 14:00

Gumbo

643,351
109
780
844

9

Syntax for 5.1 is INT UNSIGNED, not UNSIGNED INT – Vinko Vrsalovic Oct 30 '12 at 08:54
1

With the suggested method - How to find addresses with matching subnet? – Alexander Farber Nov 17 '15 at 09:34
1

@AlexanderFarber [Check if IP is in subnet](http://stackoverflow.com/a/10002060/53114) – Gumbo Nov 17 '15 at 19:07
1

What if it could be either? – Jeff Davis Jul 18 '17 at 01:47
1

New Update: INET6_ATON() For IPV6 INET6_NTOA() – Jul 23 '17 at 23:36
When storing as binary format - IPv6 addresses using INET6_NTOA() and INET6_ATON() - use HEX() and UNHEX() for readability. And if you are fetching something based on IP address, make sure you use index on this column and query actually uses that index while execution - check here http://www.rathishkumar.in/2017/08/how-to-store-ip-address-in-mysql.html – Rathish Kumar B Aug 25 '17 at 16:55

score 56 · Answer 2 · answered Feb 27 '11 at 13:57

56

You have two possibilities (for an IPv4 address) :

a varchar(15), if your want to store the IP address as a string
- 192.128.0.15 for instance
an integer (4 bytes), if you convert the IP address to an integer
- 3229614095 for the IP I used before

The second solution will require less space in the database, and is probably a better choice, even if it implies a bit of manipulations when storing and retrieving the data (converting it from/to a string).

About those manipulations, see the ip2long() and long2ip() functions, on the PHP-side, or inet_aton() and inet_ntoa() on the MySQL-side.

answered Feb 27 '11 at 13:57

Pascal MARTIN

395,085
80
655
663

But I've heard that it also can be an IPv6 address in $_SERVER['REMOTE_ADDR']. And how could I convert something like xxx.xx.xx.xxx into an integer (4 bytes) like you said? – ComFreek Feb 27 '11 at 14:00
1

For an IPv6 address, you'll need more than 4 bytes -- https://secure.wikimedia.org/wikipedia/en/wiki/IPv6 says it uses 128 bits, so 16 bytes ;; in PHP, see http://fr2.php.net/manual/en/function.inet-pton.php – Pascal MARTIN Feb 27 '11 at 14:03
Which datatype would be correct for 16 bytes? – ComFreek Feb 27 '11 at 14:10
1

Considering that none of the integer datatype is more than 8 bytes (see http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html ) ? I would say you'll have to fallback to some kind of char-based type. – Pascal MARTIN Feb 27 '11 at 14:14
@ComFreek or somelse who stumble upon this 192*256*256*256 + 128*256*256 + 15 = 3229614095 – Saurabh Rana Mar 29 '21 at 22:42
`BINARY(128)` can be used for storing ipv6 addresses. – Ether Nov 03 '21 at 19:41

Michael Berkowski · Answer 3 · 2011-02-27T14:03:16.180

5

For IPv4 addresses, you can use VARCHAR to store them as strings, but also look into storing them as long integesrs INT(11) UNSIGNED. You can use MySQL's INET_ATON() function to convert them to integer representation. The benefit of this is it allows you to do easy comparisons on them, like BETWEEN queries

INET_ATON() MySQL function

edited Feb 27 '11 at 14:03

answered Feb 27 '11 at 13:57

Michael Berkowski

267,341
46
444
390

Which MySQL datatype to use for an IP address?

3 Answers3

Linked