INET6
MariaDB starting with 10.5.0
The INET6 data type was added in MariaDB 10.5.0
Syntax
INET6
Contents
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 and to INET6
- 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.
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 | +----------------------------------+