How the INET_ATON() function works in Mariadb?

The MariaDB INET_ATON() function is used to convert an IPv4 address in the dotted-quad notation (e.g., ‘192.168.0.1’) into a numerical representation. This function is particularly useful for storing IP addresses in a more efficient numerical format, which can be beneficial for performance in database operations such as sorting and searching.

Syntax

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

INET_ATON(ip_address)

Where ip_address is the dotted-quad IPv4 address. The function returns an integer value representing the IPv4 address.

Examples

Example 1: Basic Conversion

This example demonstrates how to convert a standard IPv4 address to its numerical equivalent.

SELECT INET_ATON('192.168.0.1');

The output for this statement is:

+--------------------------+
| INET_ATON('192.168.0.1') |
+--------------------------+
|               3232235521 |
+--------------------------+

This result represents the numerical version of the IPv4 address ‘192.168.0.1’.

Example 2: Conversion with Leading Zeros

Illustrating the function’s handling of IPv4 addresses with leading zeros.

SELECT INET_ATON('192.168.001.001');

The output for this statement is:

+------------------------------+
| INET_ATON('192.168.001.001') |
+------------------------------+
|                   3232235777 |
+------------------------------+

The leading zeros are ignored, and the result is the same as the previous example.

Example 3: Invalid IP Address

Showing the function’s response to an invalid IP address.

SELECT INET_ATON('999.999.999.999');

The output for this statement is:

+------------------------------+
| INET_ATON('999.999.999.999') |
+------------------------------+
|                         NULL |
+------------------------------+

Since the IP address is invalid, the function returns NULL.

Example 4: Using INET_ATON() with a Table

Creating a table to store IP addresses and demonstrating the conversion.

DROP TABLE IF EXISTS ip_addresses;
CREATE TABLE ip_addresses (ip VARCHAR(15));
INSERT INTO ip_addresses (ip) VALUES ('192.168.0.1'), ('10.0.0.1');

SELECT ip, INET_ATON(ip) AS numeric_ip FROM ip_addresses;

The output for this statement is:

+-------------+------------+
| ip          | numeric_ip |
+-------------+------------+
| 192.168.0.1 | 3232235521 |
| 10.0.0.1    |  167772161 |
+-------------+------------+

This table shows the original IP addresses and their numerical equivalents.

Example 5: Range of IP Addresses

Demonstrating the conversion of a range of IP addresses.

SELECT INET_ATON('192.168.0.0'), INET_ATON('192.168.0.255');

The output for this statement is:

+--------------------------+----------------------------+
| INET_ATON('192.168.0.0') | INET_ATON('192.168.0.255') |
+--------------------------+----------------------------+
|               3232235520 |                 3232235775 |
+--------------------------+----------------------------+

The results show the numerical representation for the start and end of the IP address range within a subnet.

Related Functions

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

• MariaDB INET_NTOA() function is used to convert the numerical representation of an IP address back to its dotted-quad format.
• MariaDB INET6_ATON() function is used for converting IPv6 addresses into their numerical representation.
• MariaDB INET6_NTOA() function is used to convert a numerical representation of an IPv6 address back to its standard format.

Conclusion

The INET_ATON() function in MariaDB is a powerful tool for converting IPv4 addresses into a numerical format, facilitating efficient storage and manipulation within a database. Understanding how to use this function, along with its related functions, can greatly enhance the management of IP address data in MariaDB.