Store IPv4 and IPv6 Together in MySQL With INET6_ATON()

For years I stored client addresses as INT UNSIGNED with INET_ATON(). It worked well until the first IPv6 address appeared in the logs and INET_ATON() returned NULL for it. The text column alternative, VARCHAR(45), holds anything but sorts wrongly and cannot do a range query. The answer is INET6_ATON(), which packs both kinds of address into binary. You store the result in a VARBINARY(16) or BINARY(16) column.

This post covers both functions, the IS_IPV4() family, IPv4-mapped addresses, sorting, range queries, and MariaDB's native INET6 column type. Every query was run on MariaDB 11.8. INET6_ATON() and the related functions also exist in MySQL from 5.6 onwards. The INET6 type is MariaDB-only.

INET6_ATON() and INET6_NTOA()

INET6_ATON() takes an address as text and returns its network-order bytes: 4 bytes for IPv4, 16 bytes for IPv6. INET6_NTOA() turns those bytes back into the canonical text form. Unlike INET_ATON(), the result is a binary string, not a number, so the column type is VARBINARY(16):

sql

CREATE TABLE visitors (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  ip VARBINARY(16) NOT NULL,
  KEY idx_ip (ip)
);
INSERT INTO visitors (ip) VALUES
  (INET6_ATON('192.168.1.10')),
  (INET6_ATON('2001:db8::1')),
  (INET6_ATON('10.0.0.5')),
  (INET6_ATON('2001:db8:0:1::abcd')),
  (INET6_ATON('::1')),
  (INET6_ATON('::ffff:192.168.1.10')),
  (INET6_ATON('fe80::1ff:fe23:4567:890a'));
SELECT id, HEX(ip) AS hex, LENGTH(ip) AS len, INET6_NTOA(ip) AS addr
FROM visitors ORDER BY id;

output

+----+----------------------------------+-----+--------------------------+
| id | hex                              | len | addr                     |
+----+----------------------------------+-----+--------------------------+
|  1 | C0A8010A                         |   4 | 192.168.1.10             |
|  2 | 20010DB8000000000000000000000001 |  16 | 2001:db8::1              |
|  3 | 0A000005                         |   4 | 10.0.0.5                 |
|  4 | 20010DB800000001000000000000ABCD |  16 | 2001:db8:0:1::abcd       |
|  5 | 00000000000000000000000000000001 |  16 | ::1                      |
|  6 | 00000000000000000000FFFFC0A8010A |  16 | ::ffff:192.168.1.10      |
|  7 | FE8000000000000001FFFE234567890A |  16 | fe80::1ff:fe23:4567:890a |
+----+----------------------------------+-----+--------------------------+

Always read the column through INET6_NTOA() or HEX(). Selecting it raw sends binary bytes to your terminal. INET6_NTOA() also normalises: 2001:DB8:0:1::ABCD and 2001:0db8:0000:0001:0000:0000:0000:abcd both come back as 2001:db8:0:1::abcd.

INET6_ATON() is stricter than the IPv4 function. It returns NULL for the short form '10.1', which INET_ATON() accepts as 10.0.0.1. It does the same for anything with a leading space:

sql

SELECT INET6_ATON('bogus') AS a, INET6_ATON('10.1') AS b,
       INET_ATON('10.1') AS c, INET6_ATON(' 10.0.0.1') AS d;

output

+------+------+-----------+------+
| a    | b    | c         | d    |
+------+------+-----------+------+
| NULL | NULL | 167772161 | NULL |
+------+------+-----------+------+

Because the column is NOT NULL, a bad address makes the INSERT fail with ERROR 1048 (23000): Column 'ip' cannot be null instead of storing bad data. Trim user input before it reaches the function.

IS_IPV4(), IS_IPV6() and IPv4-mapped addresses

Four helper functions tell you what you are looking at. IS_IPV4() and IS_IPV6() take text. IS_IPV4_MAPPED() and IS_IPV4_COMPAT() take the binary form and test for the ::ffff:a.b.c.d and the old, deprecated ::a.b.c.d layouts.

sql

SELECT INET6_NTOA(ip) AS addr,
       IS_IPV4(INET6_NTOA(ip)) AS v4, IS_IPV6(INET6_NTOA(ip)) AS v6,
       IS_IPV4_MAPPED(ip) AS mapped, IS_IPV4_COMPAT(ip) AS compat
FROM visitors ORDER BY id;

output

+--------------------------+------+------+--------+--------+
| addr                     | v4   | v6   | mapped | compat |
+--------------------------+------+------+--------+--------+
| 192.168.1.10             |    1 |    0 |      0 |      0 |
| 2001:db8::1              |    0 |    1 |      0 |      0 |
| 10.0.0.5                 |    1 |    0 |      0 |      0 |
| 2001:db8:0:1::abcd       |    0 |    1 |      0 |      0 |
| ::1                      |    0 |    1 |      0 |      0 |
| ::ffff:192.168.1.10      |    0 |    1 |      1 |      0 |
| fe80::1ff:fe23:4567:890a |    0 |    1 |      0 |      0 |
+--------------------------+------+------+--------+--------+

Rows 1 and 6 are the same host. An IPv4-mapped address is how a dual-stack socket reports an IPv4 client: the 32-bit address sits in the last four bytes behind ten zero bytes and two 0xFF bytes. Depending on how your web server or application is configured, the same visitor can be logged either way. For SQL, C0A8010A and ...FFFFC0A8010A are two different values.

The sorting problem with mixed lengths

Binary strings compare byte by byte, and a 4-byte IPv4 value does not know that it belongs to a different address family. Sorting the table above by the raw column gives this:

sql

SELECT INET6_NTOA(ip) AS addr FROM visitors ORDER BY ip;

output

+--------------------------+
| addr                     |
+--------------------------+
| ::1                      |
| ::ffff:192.168.1.10      |
| 10.0.0.5                 |
| 2001:db8::1              |
| 2001:db8:0:1::abcd       |
| 192.168.1.10             |
| fe80::1ff:fe23:4567:890a |
+--------------------------+

IPv4 addresses are scattered among the IPv6 ones according to their first byte. ORDER BY LENGTH(ip), ip puts all IPv4 first and then all IPv6, which is readable. But a range query on the index still cannot tell a 4-byte 0x20... value from an IPv6 address starting with 2001.

Normalise IPv4 to the mapped form

The cleanest fix is to store every IPv4 address as IPv4-mapped IPv6. Then every value is exactly 16 bytes, all IPv4 sits together in the block ::ffff:0.0.0.0/96, and the column can be a fixed BINARY(16). Convert each address when you insert it, and convert it back when you read it:

sql

CREATE TABLE hits (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  ip BINARY(16) NOT NULL,
  KEY idx_ip (ip)
);
INSERT INTO hits (ip)
SELECT IF(IS_IPV4(a), INET6_ATON(CONCAT('::ffff:', a)), INET6_ATON(a))
FROM (SELECT '192.168.1.10' a UNION ALL SELECT '2001:db8::1'
      UNION ALL SELECT '10.0.0.5' UNION ALL SELECT '2001:db8:0:1::abcd'
      UNION ALL SELECT '::1' UNION ALL SELECT '192.168.1.200'
      UNION ALL SELECT '2001:db8:1::5'
      UNION ALL SELECT 'fe80::1ff:fe23:4567:890a') t;
SELECT id, HEX(ip) AS hex,
       IF(IS_IPV4_MAPPED(ip), INET6_NTOA(SUBSTRING(ip, 13)),
          INET6_NTOA(ip)) AS addr
FROM hits ORDER BY ip;

output

+----+----------------------------------+--------------------------+
| id | hex                              | addr                     |
+----+----------------------------------+--------------------------+
|  5 | 00000000000000000000000000000001 | ::1                      |
|  3 | 00000000000000000000FFFF0A000005 | 10.0.0.5                 |
|  1 | 00000000000000000000FFFFC0A8010A | 192.168.1.10             |
|  6 | 00000000000000000000FFFFC0A801C8 | 192.168.1.200            |
|  2 | 20010DB8000000000000000000000001 | 2001:db8::1              |
|  4 | 20010DB800000001000000000000ABCD | 2001:db8:0:1::abcd       |
|  7 | 20010DB8000100000000000000000005 | 2001:db8:1::5            |
|  8 | FE8000000000000001FFFE234567890A | fe80::1ff:fe23:4567:890a |
+----+----------------------------------+--------------------------+

Now IPv4 sorts in numeric order and lives in its own block. SUBSTRING(ip, 13) takes the last four bytes, which INET6_NTOA() prints as plain dotted IPv4.

Note: do not just put the 4-byte result of INET6_ATON('10.0.0.5') into a BINARY(16) column. BINARY pads on the right with zero bytes, so it becomes 0A000005000000000000000000000000. INET6_NTOA() reads this back as a00:5::, a completely different address. Either normalise as above or use VARBINARY(16).

Range queries

With fixed-length 16-byte values, a subnet is again a contiguous range from its first to its last address. BETWEEN on the bare column uses the index. An IPv4 /24 becomes a range inside the mapped block:

sql

SELECT INET6_NTOA(SUBSTRING(ip, 13)) AS addr FROM hits
WHERE ip BETWEEN INET6_ATON('::ffff:192.168.1.0')
             AND INET6_ATON('::ffff:192.168.1.255');

output

+---------------+
| addr          |
+---------------+
| 192.168.1.10  |
| 192.168.1.200 |
+---------------+

An IPv6 /32 works the same way:

sql

SELECT INET6_NTOA(ip) AS addr FROM hits
WHERE ip BETWEEN INET6_ATON('2001:db8::')
             AND INET6_ATON('2001:db8:ffff:ffff:ffff:ffff:ffff:ffff');

output

+--------------------+
| addr               |
+--------------------+
| 2001:db8::1        |
| 2001:db8:0:1::abcd |
| 2001:db8:1::5      |
+--------------------+

EXPLAIN for that query shows type range on idx_ip with key_len 16. Writing out the last address by hand is error-prone, though. MySQL has no 128-bit integers, so the IPv4 bit tricks from the CIDR matching post do not apply. For prefixes that are a multiple of 4 (which covers /32, /48, /56 and /64, the ones you actually see), the hex string works. Keep the first len / 4 hex digits, and pad with 0 for the first address and F for the last.

sql

SET @net = INET6_ATON('2001:db8::'), @len = 48;
SELECT INET6_NTOA(UNHEX(RPAD(LEFT(HEX(@net), @len DIV 4), 32, '0'))) AS first,
       INET6_NTOA(UNHEX(RPAD(LEFT(HEX(@net), @len DIV 4), 32, 'F'))) AS last;
SELECT INET6_NTOA(ip) AS addr FROM hits
WHERE ip BETWEEN UNHEX(RPAD(LEFT(HEX(@net), @len DIV 4), 32, '0'))
             AND UNHEX(RPAD(LEFT(HEX(@net), @len DIV 4), 32, 'F'));

output

+------------+------------------------------------+
| first      | last                               |
+------------+------------------------------------+
| 2001:db8:: | 2001:db8::ffff:ffff:ffff:ffff:ffff |
+------------+------------------------------------+
+--------------------+
| addr               |
+--------------------+
| 2001:db8::1        |
| 2001:db8:0:1::abcd |
+--------------------+

The /48 correctly drops 2001:db8:1::5, which belongs to the next /48. Note the way last is printed: the third group is zero, and MariaDB compresses even a single zero group to ::. So 2001:db8:0:ffff:... appears as 2001:db8::ffff:.... It is the same address.

MariaDB's native INET6 type

MariaDB 10.5 added a real INET6 column type, which does all of the above automatically. It is stored in 16 bytes, accepts and displays text, and rejects invalid input with an error:

sql

CREATE TABLE hits6 (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  ip INET6 NOT NULL,
  KEY idx_ip (ip)
);
INSERT INTO hits6 (ip) VALUES ('192.168.1.10'), ('2001:db8::1'),
  ('10.0.0.5'), ('2001:DB8:0:1::ABCD'), ('::1'), ('192.168.1.200');
SELECT id, ip, HEX(ip) AS hex FROM hits6 ORDER BY ip;

output

+----+----------------------+----------------------------------+
| id | ip                   | hex                              |
+----+----------------------+----------------------------------+
|  5 | ::1                  | 00000000000000000000000000000001 |
|  3 | ::ffff:10.0.0.5      | 00000000000000000000FFFF0A000005 |
|  1 | ::ffff:192.168.1.10  | 00000000000000000000FFFFC0A8010A |
|  6 | ::ffff:192.168.1.200 | 00000000000000000000FFFFC0A801C8 |
|  2 | 2001:db8::1          | 20010DB8000000000000000000000001 |
|  4 | 2001:db8:0:1::abcd   | 20010DB800000001000000000000ABCD |
+----+----------------------+----------------------------------+

On 11.8, plain IPv4 input was stored as IPv4-mapped automatically. This is exactly the normalisation I did by hand earlier. Range queries take string literals, and EXPLAIN again shows a range scan on the index:

sql

SELECT ip FROM hits6
WHERE ip BETWEEN '2001:db8::' AND '2001:db8:ffff:ffff:ffff:ffff:ffff:ffff';
INSERT INTO hits6 (ip) VALUES ('bogus');

output

+--------------------+
| ip                 |
+--------------------+
| 2001:db8::1        |
| 2001:db8:0:1::abcd |
+--------------------+
ERROR 1292 (22007) at line 1: Incorrect inet6 value: 'bogus' for column
`scratch_inet6`.`hits6`.`ip` at row 1

A comparison like WHERE ip = '10.0.0.5' also finds the mapped row, since the literal is converted the same way. MariaDB 11.8 has an INET4 type too, a 4-byte column for IPv4-only data, and CAST(ip AS INET6) turns an INET4 value into its mapped form. If your application only ever runs on MariaDB, INET6 is the one to use. If it has to run on MySQL as well, stay with BINARY(16) and the functions.

Migrating an INT UNSIGNED column

If you followed the older advice and have IPv4 in an INT UNSIGNED column, the conversion is one expression: INET6_ATON(CONCAT('::ffff:', INET_NTOA(ip))). Add the new column and fill it with an UPDATE. Check that no row ended up NULL, switch the application to the new column, and drop the old column last.

The MariaDB documentation for the INET6 data type lists the conversion and comparison rules in full.

INET6_ATON() and INET6_NTOA()

IS_IPV4(), IS_IPV6() and IPv4-mapped addresses

The sorting problem with mixed lengths

Normalise IPv4 to the mapped form

Range queries

MariaDB's native INET6 type

Migrating an INT UNSIGNED column

Related posts

Related articles

Match an IPv4 Address Against a CIDR Subnet in MySQL

ipcalc()

Xtables-addons: GeoIP, TARPIT and the psd Module I Ported