The Ultimate Guide to High Availability with MariaDB
This is a read-only copy of the MariaDB Knowledgebase generated on 2024-11-15. For the latest, interactive version please visit https://mariadb.com/kb/.

INET6

MariaDB starting with 10.5.0

The INET6 data type was added in MariaDB 10.5.0

Syntax

INET6

Contents

  1. Syntax
  2. Description
    1. Retrieval
    2. Casting
    3. Comparisons
    4. Mixing INET6 Values for Result
    5. Functions and Operators
    6. Prepared Statement Parameters
    7. Migration between BINARY(16) and INET6
  3. Examples
    1. Comparison Examples
    2. Mixing for Result Examples
    3. Functions and Operators Examples
    4. Prepared Statement Parameters Examples
    5. Migration between BINARY(16) and INET6 Examples
    6. Casting from INET4 to INET6
  4. See Also

Description

The INET6 data type is intended for storage of IPv6 addresses, as well as IPv4 addresses assuming conventional mapping of IPv4 addresses into IPv6 addresses.

Both short and long IPv6 notation are permitted, according to RFC-5952.

  • Values are stored as a 16-byte fixed length binary string, with most significant byte first.
  • Storage engines see INET6 as BINARY(16).
  • Clients see INET6 as CHAR(39) and get text representation on retrieval.

The IPv4-compatible notation is considered as deprecated. It is supported for compatibility with the INET6_ATON function, which also understands this format. It's recommended to use the mapped format to store IPv4 addresses in INET6.

When an IPv4 mapped (or compatible) value is stored in INET6, it still occupies 16 bytes:

Retrieval

On retrieval, in the client-server text protocol, INET6 values are converted to the short text representation, according to RFC-5952, that is with all leading zeroes in each group removed and with consequent zero groups compressed.

Besides creating one's own stored function, there is no a way to retrieve an INET6 value using long text representation.

Casting

  • CAST from a character string to INET6 understands addresses in short or long text notation (including IPv4 mapped and compatible addresses). NULL is returned if the format is not understood.
  • CAST from a binary string to INET6 requires a 16-byte string as an argument. NULL is returned if the argument length is not equal to 16.
  • CAST from other data types to INET6 first converts data to a character string, then CAST from character string to INET6 is applied.
  • CAST from INET6 to CHAR returns short text address notation.
  • CAST from INET6 to BINARY returns its 16-byte binary string representation.
  • CAST from INET6 to data types other than CHAR (e.g. SIGNED, UNSIGNED, TIME, etc) returns an error.

Comparisons

An INET6 expression can be compared to:

  • another INET6 expression
  • a character string expression with a text (short or long) address representation:
  • a 16-byte binary string expression:

Attempting to compare INET6 to an expression of any other data type returns an error.

Mixing INET6 Values for Result

An INET6 expression can be mixed for result (i.e. UNION, CASE..THEN, COALESCE etc) with:

  • another INET6 expression. The resulting data type is INET6.
  • a character string in text (short or long) address representation. The result data type is INET6. The character string counterpart is automatically converted to INET6. If the string format is not understood, it's converted with a warning to either NULL or to '::', depending on the NULL-ability of the result.
  • a 16-byte binary string. The resulting data type is INET6. The binary string counterpart is automatically converted to INET6. If the length of the binary string is not equal to 16, it's converted with a warning to NULL or to '::' depending on the NULL-ability of the result.

Attempts to mix INET6 for result with other data types will return an error.

Mixing INET6 with other data types for LEAST and GREATEST, when mixing for comparison and mixing for result are involved at the same time, uses the same rules with mixing for result, described in the previous paragraphs.

Functions and Operators

  • HEX() with an INET6 argument returns a hexadecimal representation of the underlying 16-byte binary string
  • Arithmetic operators (+,-,*,/,MOD,DIV) are not supported for INET6. This may change in the future.
  • The INET6_ATON function now understands INET6 values as an argument
  • The prototypes of the IS_IPV4_COMPAT and IS_IPV4_MAPPED functions have changed from a BINARY(16) to a INET6,
  • When the argument for these two functions is not INET6, automatic implicit CAST to INET6 is applied. As a consequence, both functions now understand arguments in both text representation and binary(16) representation. Before MariaDB 10.5.0, these functions understood only binary(16) representation.

Prepared Statement Parameters

INET6 understands both text and binary(16) address representation in prepared statement parameters (PREPARE..EXECUTE and EXECUTE IMMEDIATE statements).

Migration between BINARY(16) and INET6

Before MariaDB 10.5.0, you may have used BINARY(16) as a storage for IPv6 internet addresses, in combination with INET6_ATON and INET6_NTOA to respectively insert and retrieve data.

From 10.5, you can ALTER BINARY(16) columns storing IPv6 addresses to INET6. After such an alter, there is no a need to use INET6_ATON() and INET6_NTOA(). Addresses can be inserted and retrieved directly.

It is also possible to convert INET6 columns to BINARY(16) and continue using the data in combination with INET6_NTOA() and INET6_ATON().

Examples

CREATE TABLE t1 (a INET6);

Inserting using short text address notation:

INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329');

Long text address notation:

INSERT INTO t1 VALUES ('2001:0db8:0000:0000:0000:ff00:0042:8329');

16-byte binary string notation:

INSERT INTO t1 VALUES (0x20010DB8000000000000FF0000428329);
INSERT INTO t1 VALUES (UNHEX('20010DB8000000000000FF0000428329'));

IPv4 addresses, using IPv4-mapped and IPv4-compatible notations:

INSERT INTO t1 VALUES ('::ffff:192.0.2.128'); -- mapped
INSERT INTO t1 VALUES ('::192.0.2.128'); -- compatible

SELECT * FROM t1;
+------------------------+
| a                      |
+------------------------+
| 2001:db8::ff00:42:8329 |
| 2001:db8::ff00:42:8329 |
| 2001:db8::ff00:42:8329 |
| 2001:db8::ff00:42:8329 |
| ::ffff:192.0.2.128     |
| ::192.0.2.128          |
+------------------------+

IPv4 mapped (or compatible) values still occupy 16 bytes:

CREATE OR REPLACE TABLE t1 (a INET6);

INSERT INTO t1 VALUES ('::ffff:192.0.2.128');

SELECT * FROM t1;
+--------------------+
| a                  |
+--------------------+
| ::ffff:192.0.2.128 |
+--------------------+

SELECT HEX(a) FROM t1;
+----------------------------------+
| HEX(a)                           |
+----------------------------------+
| 00000000000000000000FFFFC0000280 |
+----------------------------------+

Casting from INET6 to anything other than CHAR returns an error:

SELECT CAST(a AS DECIMAL) FROM t1;

ERROR 4079 (HY000): Illegal parameter data type inet6 for operation 'decimal_typecast'

Comparison Examples

Comparison with another INET6 expression:

    CREATE OR REPLACE TABLE t1 (a INET6);
    CREATE OR REPLACE TABLE t2 (a INET6);

    INSERT INTO t1 VALUES ('2001:db8::ff00:42:8328'),('2001:db8::ff00:42:8329');
    INSERT INTO t2 VALUES ('2001:db8::ff00:42:832a'),('2001:db8::ff00:42:8329');

    SELECT t1.* FROM t1,t2 WHERE t1.a=t2.a;
    +------------------------+
    | a                      |
    +------------------------+
    | 2001:db8::ff00:42:8329 |
    +------------------------+

With a character string expression with a text (short or long) address representation:

    CREATE OR REPLACE TABLE t1 (a INET6);

    INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329');

    SELECT * FROM t1 WHERE a='2001:db8::ff00:42:8329';
    +------------------------+
    | a                      |
    +------------------------+
    | 2001:db8::ff00:42:8329 |
    +------------------------+

With a 16-byte binary string expression:

    CREATE OR REPLACE TABLE t1 (a INET6);

    INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329');

    SELECT * FROM t1 WHERE a=X'20010DB8000000000000FF0000428329';
    +------------------------+
    | a                      |
    +------------------------+
    | 2001:db8::ff00:42:8329 |
    +------------------------+

With an expression of another data type:

SELECT * FROM t1 WHERE a=1;
ERROR 4078 (HY000): Illegal parameter data types inet6 and int for operation '='

Mixing for Result Examples

Mixed with another INET6 expression, returning an INET6 data type:

    CREATE OR REPLACE TABLE t1 (a INET6, b INET6);

    INSERT INTO t1 VALUES (NULL,'2001:db8::ff00:42:8329');

    SELECT a FROM t1 UNION SELECT b FROM t1;
    +------------------------+
    | a                      |
    +------------------------+
    | NULL                   |
    | 2001:db8::ff00:42:8329 |
    +------------------------+

    SELECT COALESCE(a, b) FROM t1;
    +------------------------+
    | COALESCE(a, b)         |
    +------------------------+
    | 2001:db8::ff00:42:8329 |
    +------------------------+

Mixed with a character string in text (short or long) address representation:

    CREATE OR REPLACE TABLE t1 (a INET6, b VARCHAR(64));

    INSERT INTO t1 VALUES (NULL,'2001:db8::ff00:42:8328');

    INSERT INTO t1 VALUES (NULL,'2001:db8::ff00:42:832a garbage');

    SELECT COALESCE(a,b) FROM t1;
    +------------------------+
    | COALESCE(a,b)          |
    +------------------------+
    | 2001:db8::ff00:42:8328 |
    | NULL                   |
    +------------------------+
    2 rows in set, 1 warning (0.001 sec)

    SHOW WARNINGS;
    +---------+------+---------------------------------------------------------+
    | Level   | Code | Message                                                 |
    +---------+------+---------------------------------------------------------+
    | Warning | 1292 | Incorrect inet6 value: '2001:db8::ff00:42:832a garbage' |
    +---------+------+---------------------------------------------------------+

Mixed with a 16-byte binary string:

    CREATE OR REPLACE TABLE t1 (a INET6, b VARBINARY(16));

    INSERT INTO t1 VALUES (NULL,CONCAT(0xFFFF,REPEAT(0x0000,6),0xFFFF));

    INSERT INTO t1 VALUES (NULL,0x00/*garbage*/);

    SELECT COALESCE(a,b) FROM t1;
    +---------------+
    | COALESCE(a,b) |
    +---------------+
    | ffff::ffff    |
    | NULL          |
    +---------------+
    2 rows in set, 1 warning (0.001 sec)

    SHOW WARNINGS;
    +---------+------+-------------------------------+
    | Level   | Code | Message                       |
    +---------+------+-------------------------------+
    | Warning | 1292 | Incorrect inet6 value: '\x00' |
    +---------+------+-------------------------------+

Mixing with other data types:

SELECT CAST('ffff::ffff' AS INET6) UNION SELECT 1;
ERROR 4078 (HY000): Illegal parameter data types inet6 and int for operation 'UNION'

Functions and Operators Examples

HEX with an INET6 argument returning a hexadecimal representation:

    SELECT HEX(CAST('2001:db8::ff00:42:8329' AS INET6));
    +----------------------------------------------+
    | HEX(CAST('2001:db8::ff00:42:8329' AS INET6)) |
    +----------------------------------------------+
    | 20010DB8000000000000FF0000428329             |
    +----------------------------------------------+

INET6_ATON now understands INET6 values as an argument:

    CREATE OR REPLACE TABLE t1 (a INET6);

    INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329');

    SELECT a, HEX(INET6_ATON(a)) FROM t1;
    +------------------------+----------------------------------+
    | a                      | HEX(INET6_ATON(a))               |
    +------------------------+----------------------------------+
    | 2001:db8::ff00:42:8329 | 20010DB8000000000000FF0000428329 |
    +------------------------+----------------------------------+

IS_IPV4_COMPAT and IS_IPV4_MAPPED prototype now a BINARY(16)):

    CREATE OR REPLACE TABLE t1 (a INET6);

    INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329');
    INSERT INTO t1 VALUES ('::ffff:192.168.0.1');
    INSERT INTO t1 VALUES ('::192.168.0.1');

    SELECT a, IS_IPV4_MAPPED(a), IS_IPV4_COMPAT(a) FROM t1;
    +------------------------+-------------------+-------------------+
    | a                      | IS_IPV4_MAPPED(a) | IS_IPV4_COMPAT(a) |
    +------------------------+-------------------+-------------------+
    | 2001:db8::ff00:42:8329 |                 0 |                 0 |
    | ::ffff:192.168.0.1     |                 1 |                 0 |
    | ::192.168.0.1          |                 0 |                 1 |
    +------------------------+-------------------+-------------------+

Automatic implicit CAST to INET6:

    CREATE OR REPLACE TABLE t1 (
      a INET6,
      b VARCHAR(39) DEFAULT a
    );

    INSERT INTO t1 (a) VALUES ('ffff::ffff'),('::ffff:192.168.0.1');

    SELECT a, IS_IPV4_MAPPED(a), b, IS_IPV4_MAPPED(b) FROM t1;
    +--------------------+-------------------+--------------------+-------------------+
    | a                  | IS_IPV4_MAPPED(a) | b                  | IS_IPV4_MAPPED(b) |
    +--------------------+-------------------+--------------------+-------------------+
    | ffff::ffff         |                 0 | ffff::ffff         |                 0 |
    | ::ffff:192.168.0.1 |                 1 | ::ffff:192.168.0.1 |                 1 |
    +--------------------+-------------------+--------------------+-------------------+

    CREATE OR REPLACE TABLE t1 (
      a INET6,
      b BINARY(16) DEFAULT UNHEX(HEX(a))
    );

    INSERT INTO t1 (a) VALUES ('ffff::ffff'),('::ffff:192.168.0.1');

    SELECT a, IS_IPV4_MAPPED(a), HEX(b), IS_IPV4_MAPPED(b) FROM t1;
    +--------------------+-------------------+----------------------------------+-------------------+
    | a                  | IS_IPV4_MAPPED(a) | HEX(b)                           | IS_IPV4_MAPPED(b) |
    +--------------------+-------------------+----------------------------------+-------------------+
    | ffff::ffff         |                 0 | FFFF000000000000000000000000FFFF |                 0 |
    | ::ffff:192.168.0.1 |                 1 | 00000000000000000000FFFFC0A80001 |                 1 |
    +--------------------+-------------------+----------------------------------+-------------------+

Prepared Statement Parameters Examples

CREATE OR REPLACE TABLE t1 (a INET6);

EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (?)' USING 'ffff::fffe';
EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (?)' USING X'FFFF000000000000000000000000FFFF';

SELECT * FROM t1;
+------------+
| a          |
+------------+
| ffff::fffe |
| ffff::ffff |
+------------+

EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE a=?' USING 'ffff::fffe';
+------------+
| a          |
+------------+
| ffff::fffe |
+------------+

EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE a=?' USING X'FFFF000000000000000000000000FFFF';
+------------+
| a          |
+------------+
| ffff::ffff |
+------------+

Migration between BINARY(16) and INET6 Examples

Before MariaDB 10.5:

CREATE OR REPLACE TABLE t1 (a BINARY(16));

INSERT INTO t1 VALUES (INET6_ATON('ffff::ffff'));

SELECT INET6_NTOA(a) FROM t1;
+---------------+
| INET6_NTOA(a) |
+---------------+
| ffff::ffff    |
+---------------+

Migrating to INET6, from MariaDB 10.5:

ALTER TABLE t1 MODIFY a INET6;

INSERT INTO t1 VALUES ('ffff::fffe');

SELECT * FROM t1;
+------------+
| a          |
+------------+
| ffff::ffff |
| ffff::fffe |
+------------+

Migration from INET6 to BINARY(16):

CREATE OR REPLACE TABLE t1 (a INET6);

INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329');
INSERT INTO t1 VALUES ('::ffff:192.168.0.1');
INSERT INTO t1 VALUES ('::192.168.0.1');

ALTER TABLE t1 MODIFY a BINARY(16);

SELECT INET6_NTOA(a) FROM t1;
+------------------------+
| INET6_NTOA(a)          |
+------------------------+
| 2001:db8::ff00:42:8329 |
| ::ffff:192.168.0.1     |
| ::192.168.0.1          |
+------------------------+

Casting from INET4 to INET6

From MariaDB 11.3.0, casting from INET4 data types to INET6 is permitted, allowing INET4 values to be inserted into INET6 columns.

Before MariaDB 11.3:

CREATE TABLE t1 (a INET6);

INSERT INTO t1 VALUES('0.0.0.0'), ('255.10.0.0'), ('255.255.255.255');
ERROR 1292 (22007): Incorrect inet6 value: '0.0.0.0' for column `test`.`t1`.`a` at row 1

From MariaDB 11.3:

CREATE TABLE t1 (a INET6);

INSERT INTO t1 VALUES('0.0.0.0'), ('255.10.0.0'), ('255.255.255.255');
Query OK, 3 rows affected (0.027 sec)

See Also

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.