How to use inet_aton and inet_ntoa to process ip address data through mysql

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

1. Save the ip address to the database

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

`ip` char(15) NOT NULL,

Because the maximum length of the ip address (255.255.255.255) is 15, using 15-bit char is enough.

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 pieces of 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 method

mysql provides two methods to handle ip addresses

##inet_aton Convert ip to unsigned integer Type (4-8 digits)
inet_ntoa Convert integer ip to electrical address

Before inserting data, use inet_atonConvert 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 to an electrical 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 several pieces of 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 email 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 out Users in a certain network segment (for example: 172.16.11.1 ~ 172.16.11.100) can use PHP's ip2long method to convert the IP address into an integer and then compare.

6f0ae2a3f34ecd991e57660263d12ee5

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, for large There will be negative numbers in the ip. For the reasons and solutions, please refer to my other article: "Causes and solutions for negative numbers in php ip2long"

4. Summary

1. Save the IP address to the database in unsigned int format. When inserting, use the inet_aton method to convert the IP address to an unsigned integer first, which can save storage space. 2. Use inet_ntoa to convert the integer ip address to an electrical address when displaying.
3.php When converting ip2long to ip into an integer, you need to pay attention to the occurrence of negative numbers.

Related content about using gzip compression output of php json data

How to use http_build_query through php, parse_url, parse_str creates and parses url

How to implement the shake function through html5

The above is the detailed content of How to use inet_aton and inet_ntoa to process ip address data through mysql. For more information, please follow other related articles on the PHP Chinese website!