How the INET6_NTOA() function works in Mariadb?

The MariaDB INET6_NTOA() function is used to convert a binary string representing an IPv6 address into the standard text representation. This is particularly useful for displaying IPv6 addresses in a human-readable format after they have been stored in a binary format in a database.

Syntax

The syntax for the MariaDB INET6_NTOA() function is as follows:

INET6_NTOA(binary_string)

Where binary_string is the binary representation of an IPv6 address. The function returns the IPv6 address in a readable text format.

Examples

Example 1: Basic Conversion

This example demonstrates converting a binary string to a standard IPv6 address.

SELECT INET6_NTOA(UNHEX('20010DB885A3000000008A2E03707334'));

The output for this statement is:

+-------------------------------------------------------+
| INET6_NTOA(UNHEX('20010DB885A3000000008A2E03707334')) |
+-------------------------------------------------------+
| 2001:db8:85a3::8a2e:370:7334                          |
+-------------------------------------------------------+

This result is the standard text representation of the IPv6 address.

Example 2: Shortened IPv6 Address

Showing how the function handles a shortened IPv6 address.

SELECT INET6_NTOA(UNHEX('20010DB80000000000008A2E03707334'));

The output for this statement is:

+-------------------------------------------------------+
| INET6_NTOA(UNHEX('20010DB80000000000008A2E03707334')) |
+-------------------------------------------------------+
| 2001:db8::8a2e:370:7334                               |
+-------------------------------------------------------+

The function correctly expands the shortened IPv6 address to its full representation.

Example 3: Loopback Address

Converting the binary string of the loopback address to its IPv6 format.

SELECT INET6_NTOA(UNHEX('00000000000000000000000000000001'));

The output for this statement is:

+-------------------------------------------------------+
| INET6_NTOA(UNHEX('00000000000000000000000000000001')) |
+-------------------------------------------------------+
| ::1                                                   |
+-------------------------------------------------------+

The result is the loopback address in IPv6 format.

Example 4: NULL Value

Explaining the function’s behavior when provided with a NULL value.

SELECT INET6_NTOA(NULL);

The output for this statement is:

NULL

When given a NULL value, the function returns NULL.

Example 5: Using INET6_NTOA() with a Table

Creating a table to demonstrate the conversion of binary strings to IPv6 addresses.

DROP TABLE IF EXISTS binary_ipv6;
CREATE TABLE binary_ipv6 (binary_data BINARY(16));
INSERT INTO binary_ipv6 (binary_data) VALUES (UNHEX('20010DB885A3000000008A2E03707334')), (UNHEX('00000000000000000000000000000001'));

SELECT binary_data, INET6_NTOA(binary_data) AS ipv6_address FROM binary_ipv6;

The output for this statement is:

+------------------------------------+------------------------------+
| binary_data                        | ipv6_address                 |
+------------------------------------+------------------------------+
| 0x20010DB885A3000000008A2E03707334 | 2001:db8:85a3::8a2e:370:7334 |
| 0x00000000000000000000000000000001 | ::1                          |
+------------------------------------+------------------------------+

This table shows the binary data and their corresponding IPv6 addresses.

Related Functions

Below are a few functions related to the MariaDB INET6_NTOA() function:

• MariaDB INET6_ATON() function is used to convert an IPv6 address into a binary string.
• MariaDB INET_ATON() function is used to convert an IPv4 address in dotted-quad notation to a numerical representation.
• MariaDB INET_NTOA() function is used to convert a numerical representation of an IPv4 address back to its dotted-quad format.

Conclusion

The INET6_NTOA() function in MariaDB is an essential tool for converting binary strings representing IPv6 addresses back into a standard text format. This function facilitates the easy display and management of IPv6 addresses stored in a database. Understanding its usage, along with related functions, can greatly enhance database operations involving IPv6 addresses.