5 replies to this topic

[banker]

What is the MySQL datatype to store an IP address?

I extract the address using php

<?php $ip=$_SERVER['REMOTE_ADDR']; ?>

Then I put it into my database.

Database Column Info For the IP

--------------------------------------------------------------------
name - ip, datatype - VARCHAR, length/set - 15, no-default
--------------------------------------------------------------------

There has to be a more efficient datatype to store the IP address.
What is it?:cursing: I know I'm doing something wrong.

[WingedPanther]

VARCHAR 50. IPv6 is MUCH longer than 15 chars.

[xerno]

The length of IPv4 is up to 15 characters, minimum length is 7. More
The length of IPv6 is up to 39+1. Average length depends on how compressed the IPs are that you insert. More

So:
for IPv4: VARCHAR(15) // example "108.201.159.255"
for IPv6: VARCHAR(40) // example "2001:4860:0000:2001:0000:0000:0000:0068"

[BlaineSch]

Using a large varchar will not hurt you, since it is infact a "variable character".

Quote

Values in VARCHAR columns are variable-length strings.

MySQL :: MySQL 5.0 Reference Manual :: 10.4.1 The CHAR and VARCHAR Types

[TkTech]

... come on guys ...

For IPv4, the most efficient way to store it in MySQL is with a single 32bit INT, not a 15-byte string. IPv6 requires 128bits to store as a numerical value. Or, you can store an IPv6 address as a 20-byte string by converting it to base 85 instead of 40 bytes. :compress:

[Alexander]

Sample code to produce a workable integer:

INSERT INTO tbl VALUES (..., INET_ATON('192.168.0.10'), ...)

A 4 byte (32 bit) storable IP address purely using native MySQL functions.

The complementry function is INET_NTOA() ; (xerno, BlainSch): We should stop reinventing the wheel or do worse and not use what we have.