[sql] Max length for client ip address

Possible Duplicate:
Maximum length of the textual representation of an IPv6 address?

What would you recommend as the maximum size for a database column storing client ip addresses? I have it set to 16 right now, but could I get an ip address that is longer than that with IPv6, etc?

This question is related to sql database database-design

The answer is


There's a caveat with the general 39 character IPv6 structure. For IPv4 mapped IPv6 addresses, the string can be longer (than 39 characters). An example to show this:

IPv6 (39 characters) :

ABCD:ABCD:ABCD:ABCD:ABCD:ABCD:ABCD:ABCD

IPv4-mapped IPv6 (45 characters) :

ABCD:ABCD:ABCD:ABCD:ABCD:ABCD:192.168.158.190

Note: the last 32-bits (that correspond to IPv4 address) can need up to 15 characters (as IPv4 uses 4 groups of 1 byte and is formatted as 4 decimal numbers in the range 0-255 separated by dots (the . character), so the maximum is DDD.DDD.DDD.DDD).

The correct maximum IPv6 string length, therefore, is 45.

This was actually a quiz question in an IPv6 training I attended. (We all answered 39!)


Take it from someone who has tried it all three ways... just use a varchar(39)

The slightly less efficient storage far outweighs any benefit of having to convert it on insert/update and format it when showing it anywhere.


If you want to handle IPV6 in standard notation there are 8 groups of 4 hex digits:

2001:0dc5:72a3:0000:0000:802e:3370:73E4

32 hex digits + 7 separators = 39 characters.

CAUTION: If you also want to hold IPV4 addresses mapped as IPV6 addresses, use 45 characters as @Deepak suggests.


As described in the IPv6 Wikipedia article,

IPv6 addresses are normally written as eight groups of four hexadecimal digits, where each group is separated by a colon (:)

A typical IPv6 address:

2001:0db8:85a3:0000:0000:8a2e:0370:7334

This is 39 characters long. IPv6 addresses are 128 bits long, so you could conceivably use a binary(16) column, but I think I'd stick with an alphanumeric representation.


If you are just storing it for reference, you can store it as a string, but if you want to do a lookup, for example, to see if the IP address is in some table, you need a "canonical representation." Converting the entire thing to a (large) number is the right thing to do. IPv4 addresses can be stored as a long int (32 bits) but you need a 128 bit number to store an IPv6 address.

For example, all these strings are really the same IP address: 127.0.0.1, 127.000.000.001, ::1, 0:0:0:0:0:0:0:1


People are talking about characters when one can compress an IP address into raw data.

So in principle, since we only use IPv4 (32bit) or IPv6 (128bit), that means you need at most 128 bits of space, or 128/8 = 16 bytes!

Which is much less than the suggested 39 bytes (assuming charset is ascii).

That said, you will have to decode and encode the IP address into/from the raw data, which in itself is a trivial thing to do (I've done it before, see PHP's ip2long() for 32-bit IPs).

Edit: inet_pton (and its opposite, inet_ntop()) does what you need, and works with both address types. But beware, on Windows it's available since PHP 5.3.


IPv4 uses 32 bits, in the form of:

255.255.255.255

I suppose it depends on your datatype, whether you're just storing as a string with a CHAR type or if you're using a numerical type.

IPv6 uses 128 bits. You won't have IPs longer than that unless you're including other information with them.

IPv6 is grouped into sets of 4 hex digits seperated by colons, like (from wikipedia):

2001:0db8:85a3:0000:0000:8a2e:0370:7334

You're safe storing it as a 39-character long string, should you wish to do that. There are other shorthand ways to write addresses as well though. Sets of zeros can be truncated to a single 0, or sets of zeroes can be hidden completely by a double colon.


Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to database

Implement specialization in ER diagram phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' cannot be loaded Room - Schema export directory is not provided to the annotation processor so we cannot export the schema SQL Query Where Date = Today Minus 7 Days MySQL Error: : 'Access denied for user 'root'@'localhost' SQL Server date format yyyymmdd How to create a foreign key in phpmyadmin WooCommerce: Finding the products in database TypeError: tuple indices must be integers, not str

Examples related to database-design

What are OLTP and OLAP. What is the difference between them? How to create a new schema/new user in Oracle Database 11g? What are the lengths of Location Coordinates, latitude and longitude? cannot connect to pc-name\SQLEXPRESS SQL ON DELETE CASCADE, Which Way Does the Deletion Occur? What are the best practices for using a GUID as a primary key, specifically regarding performance? "Prevent saving changes that require the table to be re-created" negative effects Difference between scaling horizontally and vertically for databases Using SQL LOADER in Oracle to import CSV file What is cardinality in Databases?