How the IS_IPV4() function works in Mariadb?

The MariaDB IS_IPV4() function is used to determine if a given IP address is an IPv4 address. This function is particularly useful in environments where both IPv4 and IPv6 addresses are used and it’s necessary to distinguish between the two for compatibility and formatting reasons.

Syntax

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

IS_IPV4(ip_address)

• ip_address is the IP address to be checked.

The function returns 1 if the ip_address is an IPv4 address, otherwise, it returns 0.

Examples

Example 1: Checking an IPv4 Address

This example demonstrates how to check if a given address is an IPv4 address.

SELECT IS_IPV4('192.168.0.1');

The output for this statement is:

+------------------------+
| IS_IPV4('192.168.0.1') |
+------------------------+
|                      1 |
+------------------------+

This indicates that ‘192.168.0.1’ is a valid IPv4 address.

Example 2: Checking an IPv6 Address

Illustrating the behavior when an IPv6 address is provided.

SELECT IS_IPV4('2001:0db8:85a3:0000:0000:8a2e:0370:7334');

The output for this statement is:

+----------------------------------------------------+
| IS_IPV4('2001:0db8:85a3:0000:0000:8a2e:0370:7334') |
+----------------------------------------------------+
|                                                  0 |
+----------------------------------------------------+

Since the provided address is an IPv6 address, the function returns 0.

Example 3: Checking an Invalid IP Address

Showing the result when an invalid IP address is checked.

SELECT IS_IPV4('invalid IP');

The output for this statement is:

+-----------------------+
| IS_IPV4('invalid IP') |
+-----------------------+
|                     0 |
+-----------------------+

The function returns 0 because ‘invalid IP’ is not a valid IP address.

Example 4: Using IS_IPV4() with Table Data

Using IS_IPV4() to check the type of IP addresses stored in a table.

DROP TABLE IF EXISTS ip_table;
CREATE TABLE ip_table (id INT, ip VARCHAR(45));
INSERT INTO ip_table (id, ip) VALUES (1, '192.168.0.1'), (2, '2001:0db8:85a3:0000:0000:8a2e:0370:7334');

SELECT id, ip, IS_IPV4(ip) AS is_ipv4 FROM ip_table;

The output for this statement is:

+------+-----------------------------------------+---------+
| id   | ip                                      | is_ipv4 |
+------+-----------------------------------------+---------+
|    1 | 192.168.0.1                             |       1 |
|    2 | 2001:0db8:85a3:0000:0000:8a2e:0370:7334 |       0 |
+------+-----------------------------------------+---------+

This table shows the IPv4 status of the IP addresses in the database.

Example 5: Checking a Range of IP Addresses

Checking a range of IP addresses for IPv4 status.

SELECT IS_IPV4('192.168.0.1'), IS_IPV4('10.0.0.1'), IS_IPV4('::1');

The output for this statement is:

+------------------------+---------------------+----------------+
| IS_IPV4('192.168.0.1') | IS_IPV4('10.0.0.1') | IS_IPV4('::1') |
+------------------------+---------------------+----------------+
|                      1 |                   1 |              0 |
+------------------------+---------------------+----------------+

This result shows the IPv4 status for a set of different IP addresses.

Related Functions

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

• MariaDB INET_ATON() function is used to convert an IPv4 address to a numerical value.
• MariaDB INET_NTOA() function is used to convert the numerical value of an IPv4 address back to its dotted-quad string representation.

Conclusion

The IS_IPV4() function in MariaDB is an essential tool for working with IP addresses within a database. It allows for easy identification of IPv4 addresses, ensuring proper handling and compatibility in a network environment that may use both IPv4 and IPv6 addresses. Understanding how to use this function can greatly aid in the management of IP-related data and ensure accurate processing and storage of such information.