What is the ideal datatype to store IP address in a mysql table?

Question

Possible Duplicate:
What type should I store IP addresses for MySQL?

One single datatype which can accommodate both IPv4 and IPv6 addresses. I want to store IP addresses to limit the number of profiles that a person can make from his/her machine in my website.

Only IPv4, or IPv6 as well? Just an IP or also its subnet mask? — Martijn, Nov 16 '11 at 19:16
some similar topics in SO recommend varchar... wat abt that? — Robbie Dc, Nov 16 '11 at 19:17
I second the integer reccommendation. For example, 127.000.000.001 as string is the same as 127.0.0.1. Stored as strings, you'll need to do extra processing to dupe check the addresses — Sparky, Nov 16 '11 at 19:18
I don't think 128bit ipv6 address will fit into a int. See http://dba.stackexchange.com/questions/904/mysql-data-type-for-128-bit-integers for some info — Gerben, Nov 16 '11 at 19:26

score 17 · Accepted Answer · edited May 23 '17 at 12:32

17

IPv4 addresses use 32 bits (4 bytes), IPv6 addresses use 128 bits (16 bytes). Thus, you can store their binary representation in BINARY(16) or VARBINARY(16) fields.

Also see my answer to the question IP address storing in mysql database. It also provides comments why you would choose one over the other.

edited May 23 '17 at 12:32

Community

1
1

answered Nov 16 '11 at 19:28

knittl

246,190
53
318
364

the storage and retrieval works cool... but am not sure whether it will work cool for the intended purpose that I mentioned in the question – Robbie Dc Nov 17 '11 at 06:15
2

`varbinary(16)` seems to be the best choice. `INET6_ATON('172.32.x.x')` can convert both IPv4 and IPv6 addresses into `blob`. The same can be converted back using `INET6_NTOA`. There won't be any need to juggle code between v4 and v6; at least for storage and retrieval. – Tirtha R Apr 10 '18 at 18:42
how to translate this varbinary back into the readable form ? – bansal Jun 20 '18 at 11:32
@Abhi there's utility function in many languages, for example in PHP there's `inet_pton`/`inet_ntop` – knittl Jun 20 '18 at 17:20
2

careful, you can **NOT** reliably store both ipv4 and ipv6 addresses in BINARY(16) - sure, you can *guess* that if the last 12 bytes are null's, then it's *probably* a ipv4 address, but with this guessing, about 4.2 billion ipv6 addresses will be falsely identified as ipv4! (on the other hand, 4.2 billion ipv6 addresses is about 0.00000002% of the total ipv6 space, but it's still a bug, just one presumably very hard to hit) – hanshenrik Nov 01 '19 at 23:24

score 0 · Answer 2 · answered Nov 16 '11 at 19:20

0

A trie datatype would be optimal. There is uncompressed and compressed data struture from that type.

answered Nov 16 '11 at 19:20

Micromega

12,486
7
35
72

What is the ideal datatype to store IP address in a mysql table?

2 Answers2

Linked