How to use Inet_aton and Inet_ntoa to process IP address data via MySQL

This article describes how to save IP address data in a database using the appropriate format, and a convenient way to 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 (+) is not NULL,

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

CREATE TABLE User

CREATE TABLE ' user ' (' id ' int (one) unsigned not null auto_increment, ' name ' varchar (+) ' NOT null ', ' IP ' char (+) ' is not NULL, PRIMARY KEY (' id ')) engine=innodb;

Insert several data

INSERT into ' user ' (' id ', ' name ', ' IP ') VALUES (2, ' Abby ', ' 192.168.1.1 '), (3, ' Daisy ', ' 172.16.11.66 '), (4, ' Christine ', ' 22 0.117.131.12 ');

2.mysql Inet_aton and Inet_ntoa method

MySQL provides two ways to handle IP addresses

inet_aton converting IP to unsigned integer (4-8-bit)
inet_ntoa The integer IP to the power address

Before inserting the data, the IP address is converted to integer using Inet_aton , which saves space because char (15) occupies 16 bytes.
When displaying the data, use Inet_ntoa to turn the IP address of the integer into a power address display.

Example:

CREATE TABLE ' user ' (' id ' int (one) unsigned not null auto_increment, ' name ' varchar (+) ' NOT null ', ' IP ' int (ten) unsigned N OT NULL, PRIMARY KEY (' id ')) engine=innodb;

Insert several 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 a power 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 network segment (for example: 172.16.11.1 ~ 172.16.11.100), you can use the PHP Ip2long method, the IP address to the integer type, and then compare.

<?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?>

Inquire:

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 IP address to integer type, there will be negative numbers for large IP, the reason and processing method can refer to my other article: "PHP Ip2long negative Causes and solutions"

4. Summary

1. Save the IP address to the database, use the unsigned int format, use the Inet_aton method when inserting the IP first to unsigned integer, you can save storage space.
2. Use Inet_ntoa to turn the integer IP address into a power address when displayed.
3.php ip2long to Integer, you need to be aware of negative numbers.

This article explains how to use Inet_aton and Inet_ntoa to process IP address data through MySQL, and more about the PHP Chinese web.

Related recommendations:

PHP JSON data uses gzip to compress output related content

How to use HTTP_BUILD_QUERY,PARSE_URL,PARSE_STR to create and resolve URLs using PHP

How to achieve the function of shaking by HTML5