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