Mysql uses inet_aton and inet_ntoa to process IP address data, inet_atoninet_ntoa

Mysql uses inet_aton and inet_ntoa to process IP address data, inet_atoninet_ntoa

This article describes how to save IP address data in the appropriate format in the database and how to conveniently compare IP addresses.

1. Save the IP address to the database

Save the IP address in the database. The field is generally defined:

'IP' char (15) not null,

Because the maximum length of an IP address (255.255.255.255) is 15, 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 several data records

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 process IP addresses.

Inet_atonConvert an ip address to an unsigned integer (4-8 digits)

Inet_ntoaConvert an integer ip address to an electrical address

Before inserting data, useInet_atonConverting an IP address to an integer type saves space because char (15) occupies 16 bytes.

When displaying data, useInet_ntoaConvert an integer IP address to an electrical address.

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 several data records

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 an 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 the users in a CIDR Block (for example, 172.16.11.1 ~ 172.16.11.100), you can use the ip2long method of php 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)); // 2886732545echo '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 the ip2long method is used to convert an ip address to an integer type, a negative number may occur for a large ip address. For the cause and solution, refer to another article :《Details about the causes and solutions for a negative number in php ip2long"

4. Summary

1. Save the ip address to the database and use the unsigned int format. during insertion, use the inet_aton method to convert the ip address to an unsigned integer, which saves storage space.

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

3. When converting php ip2long to an integer, you must note that a negative number is displayed.

The above mysql instance that uses inet_aton and inet_ntoa to process IP address data is all the content that I have shared with you. I hope to provide you with a reference and support for more customers.