MySQL uses inet_aton and inet_ntoa to process IP address data

This article will introduce how to save IP address data in a database using a suitable format and to easily compare IP addresses.

1. Save the IP address to the database

The IP address is saved in the database, and the field is generally defined as:

`ip` char(15) NOT NULL,

Because the maximum length of an IP address (255.255.255.255) is 15, a 15-bit char is sufficient.

Create table user

CREATE TABLE `user` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `name` varchar(30) NOT NULL,
 `ip` char(15) NOT NULL,
 PRIMARY KEY (`id`)
)ENGINE=InnoDB;

Insert some data

INSERT INTO `user` (`id`, `name`, `ip`) VALUES
(2, 'Abby', '192.168.1.1'),
(3, 'Daisy', '172.16.11.66'),
(4, 'Christine', '220.117.131.12');

2. MySQL inet_aton and inet_ntoa methods

MySQL provides two methods to handle IP addresses

inet_aton converts ip to unsigned integer (4-8 bits)

inet_ntoa converts the integer IP address to an address

Before inserting the data, use inet_aton to convert the IP address to an integer to save space, because char(15) occupies 16 bytes.

When displaying data, use inet_ntoa to convert the integer IP address into an address for display.

example:

CREATE TABLE `user` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `name` varchar(100) NOT NULL,
 `ip` int(10) unsigned NOT NULL,
 PRIMARY KEY (`id`)
)ENGINE=InnoDB;

Insert some data

INSERT INTO `user` (`id`, `name`, `ip`) VALUES
(2, 'Abby', inet_aton('192.168.1.1')),
(3, 'Daisy', inet_aton('172.16.11.66')),
(4, 'Christine', inet_aton('220.117.131.12'));

mysql> select * from `user`;
+----+-----------+------------+
| id | name | ip |
+----+-----------+------------+
| 2 | Abby | 3232235777 |
| 3 | Daisy | 2886732610 |
| 4 | Christine | 3698688780 |
+----+-----------+------------+

The query is displayed as the electrical address

mysql> select id,name,inet_ntoa(ip) as ip from `user`;
+----+-----------+----------------+
| id | name | ip |
+----+-----------+----------------+
| 2 | Abby | 192.168.1.1 |
| 3 | Daisy | 172.16.11.66 |
| 4 | Christine | 220.117.131.12 |
+----+-----------+----------------+

3. Comparison method

If you need to find users in a certain network segment (for example: 172.16.11.1 ~ 172.16.11.100), you can use PHP's ip2long method to convert the IP address into an integer and then compare it.

<?php
$ip_start = '172.16.11.1';
$ip_end = '172.16.11.100';

echo 'ip2long(ip_start):'.sprintf('%u',ip2long($ip_start)); // 2886732545
echo 'ip2long(ip_end):'.sprintf('%u',ip2long($ip_end)); // 2886732644
?>

Query:

mysql> select ip,name,inet_ntoa(ip) as ip from `user` where ip>=2886732545 and ip<=2886732644;
+------------+-------+---------------+
| ip | name | ip |
+------------+-------+---------------+
| 2886732610 | Daisy | 172.16.11.66 |
+------------+-------+---------------+

Note: When using the ip2long method to convert the IP address to an integer, a negative number may appear for a large IP. For the cause and solution, please refer to my other article: " Detailed discussion of the cause and solution of negative numbers in php ip2long "

4. Summary

1. Save the IP address to the database using the unsigned int format. When inserting, use the inet_aton method to convert the IP address into an unsigned integer to save storage space.

2. Use inet_ntoa to convert the integer IP address to an address when displaying.

3. When php ip2long converts IP to integer, you need to pay attention to negative numbers.

The above example of MySQL using inet_aton and inet_ntoa to process IP address data is all the content that the editor shares with you. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.