60

what is the right field type for IP address in mysql? and what is the right way of storing it using PHP?

Stephen R
  • 3,512
  • 1
  • 28
  • 45
user552828
  • 769
  • 1
  • 8
  • 17

3 Answers3

62

This tutorial might help you.

The most efficient way of saving IPv4 addresses is with an INT field (not VARCHAR as you might expect). You convert them using PHP's ip2long and back using either MySQL's INET_NTOA function or PHP's long2ip function.

If you need to store IPv6, you'll want to use a BINARY field instead and PHP's inet_pton function.

rybo111
  • 12,240
  • 4
  • 61
  • 70
Francois Deschenes
  • 24,816
  • 4
  • 64
  • 61
  • there's no builtin function named `ip2bin`. you can easily write one yourself though: `function ip2bin($ip) { return inet_pton($ip); }` – knittl Jun 21 '11 at 15:34
  • @knittl - I added the function I was referring to. My bad for not including it. – Francois Deschenes Jun 21 '11 at 15:40
  • 4
    @francois: why not simply use `inet_pton`? it already works with almost all string representations of ip addresses, no need for complicated transformations – knittl Jun 21 '11 at 15:45
  • @knittl - That's a good point, actually. Let me update my answer. – Francois Deschenes Jun 21 '11 at 15:47
  • 1
    if you're running a 64-bit system the MySQL INT field must be UNSIGNED – JoshuaDavid May 18 '12 at 22:00
  • Even, if you running a 32-bit system, it will be considered as BIGINT UNSIGNED for computations. For IPv6 addresses use INET6_ATON() and INET6_NTOA() with HEX() and UNHEX() functions to store IP addresses as BINARY column. Still data type depends on what you want to do with this data. Check here http://www.rathishkumar.in/2017/08/how-to-store-ip-address-in-mysql.html – Rathish Kumar B Aug 25 '17 at 17:03
53

you can store them in a binary field with a length of 128 bits (16 bytes, BINARY(16) or VARBINARY(16)). to convert any ip address to its binary representation, you can use the php function inet_pton. this method will work for both IPv4 and IPv6 addresses. inet_ntop can be used to get back the string representation of the stored ip address (regardless of version)

knittl
  • 246,190
  • 53
  • 318
  • 364
  • Can I use it both for IPv6 and IPv4 – user552828 Jun 21 '11 at 15:29
  • 2
    @user552828: yes, you can use it for both ipv4 and ipv6 – knittl Jun 21 '11 at 15:30
  • but they say that IPv6 can be 45 characters long in case of tunneled ip, so is 128bit lenght is ok? Im asking because Im not soo good at coding , Im a newbie – user552828 Jun 21 '11 at 15:35
  • an ipv6 address will always be of length 128 bits. different string representations can give different string lengths. with tunneled ipv6 addresses it could look like the following: `1234:4567:89ab:cdef:1234:ffff:127.127.127.127` (not entirely correct, usually it's `::ffff:127.127.127.127` (80 bits zero, 16 bits one, 32 bits ipv4 address)) – knittl Jun 21 '11 at 15:42
  • 8
    `pton` = *presentation* to *network*. `ntop` = *network* to *presentation*. – Bell Nov 04 '16 at 02:54
  • 2
    @knittl It appears that `BINARY(16)` is not fine to use in this case. check out this link: https://stackoverflow.com/q/55347251/1640660 so `VARBINARY(16)` should be used instead. – Vladimir Mar 31 '19 at 19:29
17

Generally you can go with VARCHAR(45) as it will be long enough to even store IPv6.

Sander
  • 1,402
  • 2
  • 21
  • 44
  • 7
    IPv6 can be 45 characters if you have a tunneled address. Granted it's an edge case, but why not future proof. – Robert Jun 21 '11 at 15:27
  • Oh yeah, forgot about that :) – Sander Jun 21 '11 at 15:29
  • 5
    so, you basically use up to 45 bytes for addresses which use 16 byte max? seems quite wasteful to me … (with ipv4 addresses it gets worse: 4 byte address and 15 byte string repr.) – knittl Jun 21 '11 at 15:55
  • 1
    @Knittl you dont use empty data with VARCHAR, that's only with CHAR, see this: http://dev.mysql.com/doc/refman/5.0/en/char.html – Sander Jun 22 '11 at 11:05
  • 3
    yes, that's why i wrote "15 byte for ipv4 addresses" and not 45 byte. it still uses ~4 times as much space as necessary – knittl Jun 22 '11 at 11:31