Storing IP addresses in MariaDB

What’s the best datatype to store IP addresses as in MariaDB?¹ It’s 2025 and, according to Google, nearly half the internet now supports IPv6. So whichever data type is the best, it will have to support both IPv4 and IPv6 addresses.

There are, in that case, three main options:

1. VARBINARY(16): With this option you could store IPv6 addresses as 16-byte byte-strings and IPv4 addresses as 4-byte byte-strings; or you could store all IP addresses as 16-byte byte-strings by converting all IPv4 addresses to IPv4-mapped IPv6 addresses first.² If you go with the latter option, then of course the data type could simply be BINARY(16) instead of VARBINARY(16), making the type a little bit simpler and saving you a byte per each row.³
2. VARCHAR(45): This is the simplest option. You just store the IP addresses in their textual representation. Like in the previous option, you could convert IPv4 addresses to IPv4-mapped IPv6 addresses first, but what would be the point?
3. INET6⁴: This is of course the data-type that MariaDB provides for storing IPv6 addresses, as well as IPv4 addresses mapped into IPv6 addresses. Internally, all INET6 values are stored as 16-byte fixed length byte-strings, and clients interacting with MariaDB see them in their textual representation.

As you might expect, if there’s nothing special that needs to be taken into consideration, INET6 is the best option for storing IP addresses in MariaDB, for several reasons.

The first is that, since INET6 values are stored as fixed-length binary-strings, they can be compared with one another, most importantly to select rows between two IP addresses. This also works with the first option above, but not with the second one.

The second reason is that unlike with the VARBINARY(16) option, clients interact with INET6 as if it’s a string type, which makes the use of INET6 much simpler. The third is that with INET6 MariaDB makes sure that you’re always inserting correct IP address values.

Here are some example SQL queries dealing with INET6:

 1CREATE TABLE t1 (a INET6);
 2
 3INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329'); -- IPv6 address
 4
 5INSERT INTO t1 VALUES ('::ffff:192.0.2.128'); -- IPv4-mapped IPv6 address
 6
 7INSERT INTO t1 VALUES ('::192.0.2.128'); -- IPv4-compatible IPv6 address 
 8
 9INSERT INTO t1 VALUES ('192.0.2.128'); -- IPv4-mapped IPv6 address 
10
11SELECT * FROM t1;
12+------------------------+
13| a                      |
14+------------------------+
15| 2001:db8::ff00:42:8329 |
16| 2001:db8::ff00:42:8329 |
17| 2001:db8::ff00:42:8329 |
18| 2001:db8::ff00:42:8329 |
19| ::ffff:192.0.2.128     |
20| ::192.0.2.128          |
21+------------------------+
22
23INSERT INTO t1 VALUES ('192.0.2.192'); -- IPv4-mapped IPv6 address 
24
25INSERT INTO t1 VALUES ('10.0.2.0'); -- IPv4-mapped IPv6 address 
26
27-- Select all rows between two IP addresses
28SELECT * FROM t1 WHERE a > '192.0.2.0' AND a < '192.0.2.255';
29+--------------------+
30| a                  |
31+--------------------+
32| ::ffff:192.0.2.128 |
33| ::ffff:192.0.2.128 |
34| ::ffff:192.0.2.192 |
35+--------------------+

In the last insert statement of the this snippet, an IPv4 address is inserted into an INET6 column as is; MariaDB, since version 11.3⁵, automatically converts these into IPv4-mapped IPv6 addresses.