Datatype and length for storing IP-addresses in databases

In AppGini you can automatically save the creator or last modifier of a data record. This is also possible for the IP address. Have you ever been unsure of which data type to use?

Please note: This article is about storing the string representation of IP addresses into a MySQL/MariaDB database. This is only a human-readable string-represenation of numeric data. From a technical point of view, IP addresses are treated numerically.

tl;dr

Recommended datatype for IP addresses

  • IP v4: VarChar [15]
  • IP v6: VarChar [45] Recommended

IP Address of record creator / modifier

In AppGini you can model a field to store the IP address of the browser being used for creating or modifying a record:

What data type do you use for this?

IP v4

In the past I had assumed IP v4 addresses like this:

192.168.178.31

I used varchar(15) as data type in the past. 15 characters seemed long enough to me:

xxx.xxx.xxx.xxx

Required number of characters for IP v4:
4 x 3 + 3 dots = 15 characters

IP v6

But I have started using varchar(45) to be prepared for IP v6 addresses. Usually we would expect IP v6 addresses like this:

0000:0000:0000:0000:0000:0000:0000:0000

So varchar(39) should be enough.

Expected number of characters:
8 x 4 + 7 colons = 39 characters

The question is:
Why am I using 45 characters instead of 39
for the string representation of IP v6 addresses?

The reason is that IP v4 addresses can be mapped to IP v6. In these cases the string representation of mapped ip addresses looks differently:

0000:0000:0000:0000:0000:ffff:192:168:178:31

Obviously we need more than 39 characters.

Number of characters:
6 x 4 + 4 x 3 + 9 colon-characters = 45 characters

Summary

I recommend using VarChar [45] for storing IP addresses in your MySQL/MariaDB database.

Was this helpful?