MySQL :: MySQL 8.0: Storing IPv6

In MySQL 8.0.0 we introduced many new features; among those, we extended the bit-wise operations to work with binary data. Because of these changes, storing and manipulating IPv6 addresses can be done in an easier manner. In this blog we will take a look at how can you do this for some of the most common use cases.

Let’s start by creating a table, where we will store the CIDR, split in two columns: one for the IPv6 address and one for the network length. The most compact way of storing IPv6 values is to use the binary(16).

CREATE TABLE `cidr` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ip_address` binary(16) NOT NULL,
  `net_len` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `cidr` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`ip_address` binary(16) NOT NULL,

`net_len` int(11) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB;

In order to generate some random data, I will use a stored procedure which will insert 100,000 IP addresses in the previously created table. The addresses will be generated by inserting the first 16 bytes of the current time-stamp’s SHA value.

DELIMITER //
CREATE PROCEDURE generate_ips(no_ips INT)
    BEGIN
      DECLARE x INT DEFAULT 0;
      DECLARE ip binary(16);
      DECLARE rand_net_len INT;
      REPEAT
         SET x = x + 1;
         # Generate a random ip address
         SET ip= substring(unhex(sha(RAND())), 1, 16);
         # Generate a random network length between 64 and 123
         SET rand_net_len= 64 + FLOOR(RAND() * 60);
         INSERT INTO cidr(ip_address, net_len) values (ip, rand_net_len);
      UNTIL x > no_ips END REPEAT;
    END
//
DELIMITER ;

# Generate the IPs
CALL generate_ips(100000);

DELIMITER //

CREATE PROCEDURE generate_ips(no_ips INT)

BEGIN

DECLARE x INT DEFAULT 0;

DECLARE ip binary(16);

DECLARE rand_net_len INT;

REPEAT

SET x = x + 1;

# Generate a random ip address

SET ip= substring(unhex(sha(RAND())), 1, 16);

# Generate a random network length between 64 and 123

SET rand_net_len= 64 + FLOOR(RAND() * 60);

INSERT INTO cidr(ip_address, net_len) values (ip, rand_net_len);

UNTIL x > no_ips END REPEAT;

END

DELIMITER ;

# Generate the IPs

CALL generate_ips(100000);

If you want to insert the values from a human-readable string, MySQL provides the INET6_ATON("2001:0db8:85a3:0000:0000:8a2e:0370:7334") function which will strip the colons (“:”) and convert the 32 characters to a binary(16) string.

Next I will select the first 3 IP address. In order to do that I will use the INET6_NTOA function, which will convert from the binary format to the human-readable format.

mysql> SELECT id, INET6_NTOA(ip_address), net_len FROM cidr LIMIT 3;
+----+-----------------------------------------+---------+
| id | INET6_NTOA(ip_address)                  | net_len |
+----+-----------------------------------------+---------+
|  1 | 200d:31c4:1905:9eb2:3c7f:c45c:de78:42cd |      97 |
|  2 | 59b0:c4d6:48b4:3717:f031:d05b:705d:6c65 |      95 |
|  3 | 788e:3f48:e62b:c3bb:da10:6a03:f987:7a16 |     110 |
+----+-----------------------------------------+---------+
3 rows in set (0,01 sec)

mysql> SELECT id, INET6_NTOA(ip_address), net_len FROM cidr LIMIT 3;

+----+-----------------------------------------+---------+

| id | INET6_NTOA(ip_address) | net_len |

+----+-----------------------------------------+---------+

| 1 | 200d:31c4:1905:9eb2:3c7f:c45c:de78:42cd | 97 |

| 2 | 59b0:c4d6:48b4:3717:f031:d05b:705d:6c65 | 95 |

| 3 | 788e:3f48:e62b:c3bb:da10:6a03:f987:7a16 | 110 |

+----+-----------------------------------------+---------+

3 rows in set (0,01 sec)

Next, I would like to select the network mask, host mask, network address and also generate the network range intervals. For this I will create a few helper functions:

# Returns the net mask based on the network length
DELIMITER //
CREATE FUNCTION net_mask(net_len int) RETURNS binary(16)
    DETERMINISTIC
BEGIN
    RETURN (~INET6_ATON('::') << (128 - net_len));
END
//

# Returns the network address using an IP and the network length
CREATE FUNCTION subnet(ip BINARY(16), net_len int) RETURNS binary(16)
    DETERMINISTIC
BEGIN
    RETURN ip & ((~INET6_ATON('::') << (128 - net_len)));
END
//

# Returns the host mask
CREATE FUNCTION host_mask(net_len int) RETURNS binary(16)
    DETERMINISTIC
BEGIN
    RETURN (~INET6_ATON('::') >> net_len);
END
//
DELIMITER ;

# Returns the net mask based on the network length

DELIMITER //

CREATE FUNCTION net_mask(net_len int) RETURNS binary(16)

DETERMINISTIC

BEGIN

RETURN (~INET6_ATON('::') << (128 - net_len));

END

# Returns the network address using an IP and the network length

CREATE FUNCTION subnet(ip BINARY(16), net_len int) RETURNS binary(16)

DETERMINISTIC

BEGIN

RETURN ip & ((~INET6_ATON('::') << (128 - net_len)));

END

# Returns the host mask

CREATE FUNCTION host_mask(net_len int) RETURNS binary(16)

DETERMINISTIC

BEGIN

RETURN (~INET6_ATON('::') >> net_len);

END

DELIMITER ;

Let’s start first with the network address:

mysql> SELECT INET6_NTOA(subnet(ip_address, net_len)) AS network
    -> FROM cidr LIMIT 3;
+--------------------------------------+
| network                              |
+--------------------------------------+
| 200d:31c4:1905:9eb2:3c7f:c45c:8000:: |
| 59b0:c4d6:48b4:3717:f031:d05a::      |
| 788e:3f48:e62b:c3bb:da10:6a03:f984:: |
+--------------------------------------+
3 rows in set (0,00 sec)

mysql> SELECT INET6_NTOA(subnet(ip_address, net_len)) AS network

-> FROM cidr LIMIT 3;

+--------------------------------------+

| network |

+--------------------------------------+

| 200d:31c4:1905:9eb2:3c7f:c45c:8000:: |

| 59b0:c4d6:48b4:3717:f031:d05a:: |

| 788e:3f48:e62b:c3bb:da10:6a03:f984:: |

+--------------------------------------+

3 rows in set (0,00 sec)

Next will be the net mask:

mysql> SELECT INET6_NTOA(net_mask(net_len)) as net_mask from cidr LIMIT 3;
+--------------------------------------+
| net_mask                             |
+--------------------------------------+
| ffff:ffff:ffff:ffff:ffff:ffff:8000:: |
| ffff:ffff:ffff:ffff:ffff:fffe::      |
| ffff:ffff:ffff:ffff:ffff:ffff:fffc:: |
+--------------------------------------+
3 rows in set (0,00 sec)

mysql> SELECT INET6_NTOA(net_mask(net_len)) as net_mask from cidr LIMIT 3;

+--------------------------------------+

| net_mask |

+--------------------------------------+

| ffff:ffff:ffff:ffff:ffff:ffff:8000:: |

| ffff:ffff:ffff:ffff:ffff:fffe:: |

| ffff:ffff:ffff:ffff:ffff:ffff:fffc:: |

+--------------------------------------+

3 rows in set (0,00 sec)

And the host mask:

mysql> SELECT INET6_NTOA(host_mask(net_len)) as host_mask from cidr LIMIT 3;
+-------------------+
| host_mask         |
+-------------------+
| ::127.255.255.255 |
| ::1:ffff:ffff     |
| ::0.3.255.255     |
+-------------------+
3 rows in set (0,00 sec)

mysql> SELECT INET6_NTOA(host_mask(net_len)) as host_mask from cidr LIMIT 3;

+-------------------+

| host_mask |

+-------------------+

| ::127.255.255.255 |

| ::1:ffff:ffff |

| ::0.3.255.255 |

+-------------------+

3 rows in set (0,00 sec)

And finally the ends of the IP range:

mysql> SELECT INET6_NTOA(subnet(ip_address, net_len)) as range_from,
    -> INET6_NTOA(subnet(ip_address, net_len) | host_mask(net_len)) as range_to FROM cidr LIMIT 3;
+--------------------------------------+-----------------------------------------+
| range_from                           | range_to                                |
+--------------------------------------+-----------------------------------------+
| 200d:31c4:1905:9eb2:3c7f:c45c:8000:: | 200d:31c4:1905:9eb2:3c7f:c45c:ffff:ffff |
| 59b0:c4d6:48b4:3717:f031:d05a::      | 59b0:c4d6:48b4:3717:f031:d05b:ffff:ffff |
| 788e:3f48:e62b:c3bb:da10:6a03:f984:: | 788e:3f48:e62b:c3bb:da10:6a03:f987:ffff |
+--------------------------------------+-----------------------------------------+
3 rows in set (0,00 sec)

mysql> SELECT INET6_NTOA(subnet(ip_address, net_len)) as range_from,

-> INET6_NTOA(subnet(ip_address, net_len) | host_mask(net_len)) as range_to FROM cidr LIMIT 3;

+--------------------------------------+-----------------------------------------+

| range_from | range_to |

+--------------------------------------+-----------------------------------------+

| 200d:31c4:1905:9eb2:3c7f:c45c:8000:: | 200d:31c4:1905:9eb2:3c7f:c45c:ffff:ffff |

| 59b0:c4d6:48b4:3717:f031:d05a:: | 59b0:c4d6:48b4:3717:f031:d05b:ffff:ffff |

| 788e:3f48:e62b:c3bb:da10:6a03:f984:: | 788e:3f48:e62b:c3bb:da10:6a03:f987:ffff |

+--------------------------------------+-----------------------------------------+

3 rows in set (0,00 sec)

In order to select the row which contains a certain IP address this query can be used, but for better performance an INDEX on the ip_address column should first be added:

mysql> CREATE INDEX ip_address ON cidr(ip_address);
Query OK, 0 rows affected (1,08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT id, INET6_NTOA(ip_address), net_len FROM cidr WHERE ip_address = INET6_ATON('59b0:c4d6:48b4:3717:f031:d05b:705d:6c65');
+----+-----------------------------------------+---------+
| id | INET6_NTOA(ip_address)                  | net_len |
+----+-----------------------------------------+---------+
|  2 | 59b0:c4d6:48b4:3717:f031:d05b:705d:6c65 |      95 |
+----+-----------------------------------------+---------+
1 row in set (0,01 sec)

mysql> CREATE INDEX ip_address ON cidr(ip_address);

Query OK, 0 rows affected (1,08 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> SELECT id, INET6_NTOA(ip_address), net_len FROM cidr WHERE ip_address = INET6_ATON('59b0:c4d6:48b4:3717:f031:d05b:705d:6c65');

+----+-----------------------------------------+---------+

| id | INET6_NTOA(ip_address) | net_len |

+----+-----------------------------------------+---------+

| 2 | 59b0:c4d6:48b4:3717:f031:d05b:705d:6c65 | 95 |

+----+-----------------------------------------+---------+

1 row in set (0,01 sec)

Next let’s see how can we get the network which this IP belongs to:

mysql> SET @ip = INET6_ATON('59b0:c4d6:48b4:3717:f031:d05b:705d:6c65');
Query OK, 0 rows affected (0,00 sec)

mysql> select id, inet6_ntoa(subnet(ip_address, net_len)) from cidr
    -> where subnet(@ip, net_len) = subnet(ip_address, net_len);
+----+-----------------------------------------+---------+
| id | inet6_ntoa(subnet(ip_address),net_len)  | net_len |
+----+-----------------------------------------+---------+
|  2 | 59b0:c4d6:48b4:3717:f031:d05a::         |      95 |
+----+-----------------------------------------+---------+
1 row in set (6,79 sec)

mysql> SET @ip = INET6_ATON('59b0:c4d6:48b4:3717:f031:d05b:705d:6c65');

Query OK, 0 rows affected (0,00 sec)

mysql> select id, inet6_ntoa(subnet(ip_address, net_len)) from cidr

-> where subnet(@ip, net_len) = subnet(ip_address, net_len);

+----+-----------------------------------------+---------+

| id | inet6_ntoa(subnet(ip_address),net_len) | net_len |

+----+-----------------------------------------+---------+

| 2 | 59b0:c4d6:48b4:3717:f031:d05a:: | 95 |

+----+-----------------------------------------+---------+

1 row in set (6,79 sec)

This seems to be very inefficient, since we need to calculate, for each row in the table the network address for the IP address stored in the row.

So, how about creating a table that stores the network address and the net mask?

mysql> CREATE TABLE
    -> `networks` ( `id` int(11) NOT NULL AUTO_INCREMENT,
    ->              `network` binary(16) NOT NULL,
    ->              `mask` binary(16) NOT NULL,
    ->              PRIMARY KEY (`id`));
Query OK, 0 rows affected (0,01 sec)

mysql> INSERT INTO networks
    -> (SELECT id, subnet(ip_address, net_len), net_mask(net_len)
    -> FROM cidr);
Query OK, 100001 rows affected (18,30 sec)
Records: 100001  Duplicates: 0  Warnings: 0

mysql> FLUSH STATUS;
Query OK, 0 rows affected (0,00 sec)

mysql> select id, inet6_ntoa(network) from networks where @ip & mask = network;
+-------+----------------------------------------+
| id | inet6_ntoa(network) |
+-------+----------------------------------------+
| 2 | 59b0:c4d6:48b4:3717:f031:d05a:: |
+-------+----------------------------------------+
1 row in set (0,55 sec)
mysql> SHOW STATUS LIKE "Handler_read_%";
+-----------------------+--------+
| Variable_name | Value |
+-----------------------+--------+
| Handler_read_first | 1 |
| Handler_read_key | 1 |
| Handler_read_last | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 100002 |
+-----------------------+--------+
7 rows in set (0,01 sec)

mysql> CREATE TABLE

-> `networks` ( `id` int(11) NOT NULL AUTO_INCREMENT,

-> `network` binary(16) NOT NULL,

-> `mask` binary(16) NOT NULL,

-> PRIMARY KEY (`id`));

Query OK, 0 rows affected (0,01 sec)

mysql> INSERT INTO networks

-> (SELECT id, subnet(ip_address, net_len), net_mask(net_len)

-> FROM cidr);

Query OK, 100001 rows affected (18,30 sec)

Records: 100001 Duplicates: 0 Warnings: 0

mysql> FLUSH STATUS;

Query OK, 0 rows affected (0,00 sec)

mysql> select id, inet6_ntoa(network) from networks where @ip & mask = network;

+-------+----------------------------------------+

| id | inet6_ntoa(network) |

+-------+----------------------------------------+

| 2 | 59b0:c4d6:48b4:3717:f031:d05a:: |

+-------+----------------------------------------+

1 row in set (0,55 sec)

mysql> SHOW STATUS LIKE "Handler_read_%";

+-----------------------+--------+

| Variable_name | Value |

+-----------------------+--------+

| Handler_read_first | 1 |

| Handler_read_key | 1 |

| Handler_read_last | 0 |

| Handler_read_next | 0 |

| Handler_read_prev | 0 |

| Handler_read_rnd | 0 |

| Handler_read_rnd_next | 100002 |

+-----------------------+--------+

7 rows in set (0,01 sec)

A lot faster but still, we had to do a full table scan as we still have to do the bit-wise operations for each row in order to get the result.

How about storing the network range ends and checking if the IP is between them?

mysql> CREATE TABLE `network_range` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `range_from` binary(16) NOT NULL,
    ->   `range_to` binary(16) NOT NULL,
    ->   PRIMARY KEY (`id`),
    ->   KEY `ip_range` (`range_from`,`range_to`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0,01 sec)

mysql> INSERT INTO network_range
 -> (SELECT id, subnet(ip_address, net_len),
 -> subnet(ip_address, net_len) | host_mask(net_len)
 -> FROM cidr);
Query OK, 100001 rows affected (18,30 sec)
Records: 100001 Duplicates: 0 Warnings: 0

mysql> FLUSH STATUS;
Query OK, 0 rows affected (0,00 sec)

mysql> SELECT id, INET6_NTOA(range_from), INET6_NTOA(range_to) from network_range where @ip between range_from and range_to;
+----+---------------------------------+-----------------------------------------+
| id | INET6_NTOA(range_from) | INET6_NTOA(range_to) |
+----+---------------------------------+-----------------------------------------+
| 2 | 59b0:c4d6:48b4:3717:f031:d05a:: | 59b0:c4d6:48b4:3717:f031:d05b:ffff:ffff |
+----+---------------------------------+-----------------------------------------+
1 row in set (0,15 sec)

mysql> SHOW STATUS LIKE "Handler_read_%";
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 1 |
| Handler_read_key | 1 |
| Handler_read_last | 0 |
| Handler_read_next | 34923 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
+-----------------------+-------+
7 rows in set (0,00 sec)

mysql> EXPLAIN SELECT id, INET6_NTOA(range_from), INET6_NTOA(range_to) from network_range where @ip between range_from and range_to;
+----+-------------+---------------+------------+-------+---------------+----------+---------+------+-------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------+------------+-------+---------------+----------+---------+------+-------+----------+--------------------------+
| 1 | SIMPLE | network_range | NULL | range | ip_range | ip_range | 16 | NULL | 49901 | 11.11 | Using where; Using index |
+----+-------------+---------------+------------+-------+---------------+----------+---------+------+-------+----------+--------------------------+
1 row in set, 1 warning (0,00 sec)

mysql> CREATE TABLE `network_range` (

-> `id` int(11) NOT NULL AUTO_INCREMENT,

-> `range_from` binary(16) NOT NULL,

-> `range_to` binary(16) NOT NULL,

-> PRIMARY KEY (`id`),

-> KEY `ip_range` (`range_from`,`range_to`)

-> ) ENGINE=InnoDB;

Query OK, 0 rows affected (0,01 sec)

mysql> INSERT INTO network_range

-> (SELECT id, subnet(ip_address, net_len),

-> subnet(ip_address, net_len) | host_mask(net_len)

-> FROM cidr);

Query OK, 100001 rows affected (18,30 sec)

Records: 100001 Duplicates: 0 Warnings: 0

mysql> FLUSH STATUS;

Query OK, 0 rows affected (0,00 sec)

mysql> SELECT id, INET6_NTOA(range_from), INET6_NTOA(range_to) from network_range where @ip between range_from and range_to;

+----+---------------------------------+-----------------------------------------+

| id | INET6_NTOA(range_from) | INET6_NTOA(range_to) |

+----+---------------------------------+-----------------------------------------+

| 2 | 59b0:c4d6:48b4:3717:f031:d05a:: | 59b0:c4d6:48b4:3717:f031:d05b:ffff:ffff |

+----+---------------------------------+-----------------------------------------+

1 row in set (0,15 sec)

mysql> SHOW STATUS LIKE "Handler_read_%";

+-----------------------+-------+

| Variable_name | Value |

+-----------------------+-------+

| Handler_read_first | 1 |

| Handler_read_key | 1 |

| Handler_read_last | 0 |

| Handler_read_next | 34923 |

| Handler_read_prev | 0 |

| Handler_read_rnd | 0 |

| Handler_read_rnd_next | 0 |

+-----------------------+-------+

7 rows in set (0,00 sec)

mysql> EXPLAIN SELECT id, INET6_NTOA(range_from), INET6_NTOA(range_to) from network_range where @ip between range_from and range_to;

+----+-------------+---------------+------------+-------+---------------+----------+---------+------+-------+----------+--------------------------+

+----+-------------+---------------+------------+-------+---------------+----------+---------+------+-------+----------+--------------------------+

+----+-------------+---------------+------------+-------+---------------+----------+---------+------+-------+----------+--------------------------+

1 row in set, 1 warning (0,00 sec)

That was definitely faster, and the index was also used, but, can this be even faster? If we know that the IP ranges are non-overlapping (so no parent-child networks exist) then we can use this tip and get the result instantly:

mysql> flush status;
Query OK, 0 rows affected (0,00 sec)

mysql> SELECT id, INET6_NTOA(range_from), INET6_NTOA(range_to)
    -> from network_range where @ip BETWEEN range_from AND range_to
    -> ORDER BY range_from DESC
    -> LIMIT 1;
+----+---------------------------------+-----------------------------------------+
| id | INET6_NTOA(range_from)          | INET6_NTOA(range_to)                    |
+----+---------------------------------+-----------------------------------------+
|  2 | 59b0:c4d6:48b4:3717:f031:d05a:: | 59b0:c4d6:48b4:3717:f031:d05b:ffff:ffff |
+----+---------------------------------+-----------------------------------------+
1 row in set (0,00 sec)

mysql> flush status;

Query OK, 0 rows affected (0,00 sec)

mysql> SELECT id, INET6_NTOA(range_from), INET6_NTOA(range_to)

-> from network_range where @ip BETWEEN range_from AND range_to

-> ORDER BY range_from DESC

-> LIMIT 1;

+----+---------------------------------+-----------------------------------------+

| id | INET6_NTOA(range_from) | INET6_NTOA(range_to) |

+----+---------------------------------+-----------------------------------------+

| 2 | 59b0:c4d6:48b4:3717:f031:d05a:: | 59b0:c4d6:48b4:3717:f031:d05b:ffff:ffff |

+----+---------------------------------+-----------------------------------------+

1 row in set (0,00 sec)

So now we can replace the compound index with an index on range_from and still get instant results as we will search for the first IP’s starting range (range_from) using the index and then we would stop there as we know the network range is unique (also because we’ve set the LIMIT to 1).

Conclusion

The improvements added to the bit-wise operations make it easy to work with IPv6 addresses. We would like to further improve this experience and that’s why we’re looking for your feed-back. Which functions would you find helpful and in which way would you use them?

Thank you for using MySQL!