How the IS_IPV4_COMPAT() function works in Mariadb?

The MariaDB IS_IPV4_COMPAT() function is used to determine if an IPv6 address is IPv4-compatible. This function is essential when working with databases that store IP addresses, ensuring compatibility and proper format between IPv4 and IPv6.

Syntax

MariaDB IS_IPV4_COMPAT() function’s syntax is as follows:

IS_IPV4_COMPAT(ipv6_address)

• ipv6_address is the IPv6 address to be checked for IPv4 compatibility.

The function returns 1 if the IPv6 address is IPv4-compatible, otherwise, it returns 0.

Examples

Example 1: IPv4-Compatible Address

This example checks if the given IPv6 address is IPv4-compatible.

SELECT IS_IPV4_COMPAT(INET6_ATON('::192.168.0.1'));

The output for this statement is:

+---------------------------------------------+
| IS_IPV4_COMPAT(INET6_ATON('::192.168.0.1')) |
+---------------------------------------------+
|                                           1 |
+---------------------------------------------+

This result indicates that the IPv6 address is IPv4-compatible.

Example 2: Non-Compatible IPv6 Address

Demonstrating the check for an IPv6 address that is not IPv4-compatible.

SELECT IS_IPV4_COMPAT(INET6_ATON('2001:db8::1'));

The output for this statement is:

+-------------------------------------------+
| IS_IPV4_COMPAT(INET6_ATON('2001:db8::1')) |
+-------------------------------------------+
|                                         0 |
+-------------------------------------------+

Since the address is not IPv4-compatible, the function returns 0.

Example 3: Invalid IPv6 Address

Showing the result when checking an invalid IPv6 address.

SELECT IS_IPV4_COMPAT(INET6_ATON('invalid address'));

The output for this statement is:

+-----------------------------------------------+
| IS_IPV4_COMPAT(INET6_ATON('invalid address')) |
+-----------------------------------------------+
|                                             0 |
+-----------------------------------------------+

The function returns 0 because the address is not a valid IPv6 address.

Example 4: Checking Multiple Addresses

Using IS_IPV4_COMPAT() to check multiple IPv6 addresses in a single query.

SELECT IS_IPV4_COMPAT(INET6_ATON('::192.168.0.1')),
       IS_IPV4_COMPAT(INET6_ATON('2001:db8::1'));

The output for this statement is:

+---------------------------------------------+-------------------------------------------+
| IS_IPV4_COMPAT(INET6_ATON('::192.168.0.1')) | IS_IPV4_COMPAT(INET6_ATON('2001:db8::1')) |
+---------------------------------------------+-------------------------------------------+
|                                           1 |                                         0 |
+---------------------------------------------+-------------------------------------------+

This result shows the compatibility status of two different IPv6 addresses.

Example 5: Using IS_IPV4_COMPAT() with Table Data

Checking the IPv4 compatibility of IPv6 addresses stored in a table.

DROP TABLE IF EXISTS ip_addresses;
CREATE TABLE ip_addresses (id INT, ipv6_address VARBINARY(16));
INSERT INTO ip_addresses (id, ipv6_address) VALUES (1, INET6_ATON('::192.168.0.1')), (2, INET6_ATON('2001:db8::1'));

SELECT id, IS_IPV4_COMPAT(ipv6_address) AS ipv4_compat FROM ip_addresses;

The output for this statement is:

+------+-------------+
| id   | ipv4_compat |
+------+-------------+
|    1 |           1 |
|    2 |           0 |
+------+-------------+

This table shows the IPv4 compatibility status of the IPv6 addresses in the database.

Related Functions

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

• MariaDB INET6_ATON() function is used to convert an IPv6 or IPv4 address to binary form.
• MariaDB INET6_NTOA() function is used to convert the binary form of an IPv6 or IPv4 address back to text.

Conclusion

The IS_IPV4_COMPAT() function in MariaDB is a valuable tool for managing IP addresses within a database. It allows for the easy identification of IPv4-compatible addresses within an IPv6 context, facilitating smooth transitions and interoperability between different IP versions. Mastery of this function is crucial for database administrators and developers working with network-related data.