IPv6 address data type. Stores IPv6 addresses as 16-byte binary strings, also supporting IPv4 addresses via mapping.
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 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:
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 , there is no a way to retrieve an INET6 value using long text representation.
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.
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.
An INET6 expression can be mixed for result (i.e. , , 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.
Attempts to mix INET6 for result with other data types will return an error.
Mixing INET6 with other data types for and , 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.
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 function now understands INET6 values as an argument
The prototypes of the and I functions have changed from a BINARY(16)
When the argument for the aforementioned two functions is not INET6, automatic implicit CAST to INET6 is applied. As a consequence, both functions understand arguments in both text representation and binary(16) representation.
When the argument for the aforementioned two functions is not INET6, automatic implicit CAST to INET6 is not applied.
INET6 understands both and address representation in parameters (.. and statements).
You may have used as a storage for IPv6 internet addresses, in combination with and to respectively insert and retrieve data.
However, you can 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.
You may use as a storage for IPv6 internet addresses, in combination with and to respectively insert and retrieve data.
It is also possible to convert INET6 columns to BINARY(16) and continue using the data in combination with INET6_NTOA() and INET6_ATON().
Inserting using short text address notation:
Long text address notation:
16-byte binary string notation:
IPv4 addresses, using IPv4-mapped and IPv4-compatible notations:
IPv4 mapped (or compatible) values still occupy 16 bytes:
Casting from INET6 to anything other than returns an error:
Comparison with another INET6 expression:
With a character string expression with a text (short or long) address representation:
With a 16-byte binary string expression:
With an expression of another data type:
Mixed with another INET6 expression, returning an INET6 data type:
Mixed with a character string in text (short or long) address representation:
Mixed with a 16-byte binary string:
Mixing with other data types:
with an INET6 argument returning a hexadecimal representation:
now understands INET6 values as an argument:
and prototype now a BINARY(16)):
Automatic implicit CAST to INET6:
Migrating to INET6:
There's no conversion you can use:
Migration from INET6 to BINARY(16):
Casting from data types to INET6 is permitted, allowing INET4 values to be inserted into INET6 columns.
Casting from data types to INET6 is not permitted. You get an error if you try:
(video)
This page is licensed: CC BY-SA / Gnu FDL
INET6CAST 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.
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.
a INET6CREATE TABLE t1 (a INET6);INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329');INSERT INTO t1 VALUES ('2001:0db8:0000:0000:0000:ff00:0042:8329');INSERT INTO t1 VALUES (0x20010DB8000000000000FF0000428329);
INSERT INTO t1 VALUES (UNHEX('20010DB8000000000000FF0000428329'));INSERT INTO t1 VALUES ('::ffff:192.0.2.128'); -- mapped
INSERT INTO t1 VALUES ('::192.0.2.128'); -- compatibleSELECT * 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 |
+------------------------+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 |
+----------------------------------+SELECT CAST(a AS DECIMAL) FROM t1;
ERROR 4079 (HY000): Illegal parameter data type inet6 for operation 'decimal_typecast'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 |
+------------------------+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 |
+------------------------+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 |
+------------------------+SELECT * FROM t1 WHERE a=1;
ERROR 4078 (HY000): Illegal parameter data types inet6 and int for operation '='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 |
+------------------------+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' |
+---------+------+---------------------------------------------------------+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' |
+---------+------+-------------------------------+SELECT CAST('ffff::ffff' AS INET6) UNION SELECT 1;
ERROR 4078 (HY000): Illegal parameter data types inet6 and int for operation 'UNION'SELECT HEX(CAST('2001:db8::ff00:42:8329' AS INET6));
+----------------------------------------------+
| HEX(CAST('2001:db8::ff00:42:8329' AS INET6)) |
+----------------------------------------------+
| 20010DB8000000000000FF0000428329 |
+----------------------------------------------+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 |
+------------------------+----------------------------------+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 |
+------------------------+-------------------+-------------------+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 |
+--------------------+-------------------+----------------------------------+-------------------+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 |
+------------+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 |
+---------------+ALTER TABLE t1 MODIFY a INET6;
INSERT INTO t1 VALUES ('ffff::fffe');
SELECT * FROM t1;
+------------+
| a |
+------------+
| ffff::ffff |
| ffff::fffe |
+------------+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 |
+---------------+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 |
+------------------------+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)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