본문으로 건너뛰기

MySQL INET_ATON Expressions

The `INET_ATON()` expression in MySQL converts an IPv4 address from its standard dotted-decimal format to an integer representation. This conversion facilitates efficient storage and comparison of IP addresses in databases.

Usage

`INET_ATON()` is used when you need to store or compare IPv4 addresses in a database as integers rather than strings. This can improve both the performance and ease of IP address manipulations.

INET_ATON(ip_address)

In this syntax, `ip_address` is the IPv4 address in dotted-decimal format that you want to convert to an integer.

Examples

1. Basic Conversion

SELECT INET_ATON('192.168.1.1');

This example converts the IP address `192.168.1.1` to its integer equivalent, which is `3232235777`.

2. Storing Converted IP Addresses

INSERT INTO ip_storage (ip_integer)
VALUES (INET_ATON('10.0.0.5'));

Here, the IP address `10.0.0.5` is converted and stored as an integer in the `ip_integer` column of the `ip_storage` table.

3. Comparing IP Addresses

SELECT user_id
FROM user_logins
WHERE INET_ATON(ip_address) BETWEEN INET_ATON('192.168.1.0') AND INET_ATON('192.168.1.255');

This example retrieves `user_id` values where the `ip_address` falls within a specified range, using `INET_ATON()` for conversion and comparison.

Tips and Best Practices

  • Index the integer column. When storing IPs as integers, ensure the column is indexed for faster search and comparison.
  • Use with IPv4 only. `INET_ATON()` works only with IPv4 addresses, not IPv6.
  • Consistent format. Ensure the IP address is in a valid dotted-decimal format to avoid errors during conversion. MySQL will return `NULL` if the input is invalid.
  • Combine with INET_NTOA(). Use `INET_NTOA()` for the reverse conversion when you need to display the IP address back in its human-readable format.
  • Performance Note. Storing IP addresses as integers can be more efficient because integer comparisons are generally faster than string comparisons and require less storage space.
  • Related Functions. For IPv6 addresses, consider using `INET6_ATON()` and `INET6_NTOA()`, as `INET_ATON()` is not suitable for IPv6.