Storage and use of IP addresses

Storage and use of IP addresses

IP addresses are stored in int type and converted using INET_NTOA () and INET_ATON ().

mysql> select inet_ntoa('2130706433'),inet_aton('127.0.0.1'); +-------------------------+------------------------+ | inet_ntoa('2130706433') | inet_aton('127.0.0.1') | +-------------------------+------------------------+ | 127.0.0.1 | 2130706433 | +-------------------------+------------------------+ 1 row in set (0.00 sec)

1. Environment

mysql ----5.6.13

mysql> show create table test \G;*************************** 1. row *************************** Table: test Create Table: CREATE TABLE `test` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `ip_from` int(10) unsigned DEFAULT NULL, `ip_to` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_ip` (`ip_from`,`ip_to`), KEY `idx_ip_from` (`ip_from`) ) ENGINE=InnoDB AUTO_INCREMENT=9568111 DEFAULT CHARSET=utf8 COLLATE=utf8_bin 1 row in set (0.01 sec)ERROR: No query specified------------------------------------------------------mysql> show index from test;+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| test | 0 | PRIMARY | 1 | id | A | 9289578 | NULL | NULL | | BTREE | | | | test | 1 | idx_ip | 1 | ip_from | A | 9289578 | NULL | NULL | YES | BTREE | | || test | 1 | idx_ip | 2 | ip_to | A | 9289578 | NULL | NULL | YES | BTREE | | | | test | 1 | idx_ip_from | 1 | ip_from | A | 9289578 | NULL | NULL | YES | BTREE | | |+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+4 rows in set (0.00 sec) mysql> select count(*) from test; +----------+ | count(*) | +----------+ | 9541210 | +----------+ 1 row in set (2.84 sec)

 2. Use

Query the ip segment of a value.

• SELECT * FROM test WHERE ip_from <= 2352356 AND ip_to> = 2352356;

mysql> explain SELECT * FROM test WHERE ip_from<=2352356 AND ip_to>=2352356;+----+-------------+-------+-------+--------------------+--------+---------+------+------+-----------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+-------+--------------------+--------+---------+------+------+-----------------------+| 1 | SIMPLE | test | range | idx_ip,idx_ip_from | idx_ip | 5 | NULL | 1 | Using index condition |+----+-------------+-------+-------+--------------------+--------+---------+------+------+-----------------------+1 row in set (0.08 sec)

 

• This method performs a full range scan of the index, which takes a long time.

• SELECT * FROM test WHERE id IN (SELECT id FROM test WHERE ip_from <= 2352356 AND ip_to> = 2352356 );

mysql> EXPLAIN SELECT * FROM test WHERE id IN (-> SELECT id FROM test WHERE ip_from<=2352356 AND ip_to>=2352356 ); +----+-------------+-------+--------+----------------------------+---------+---------+---------------------+------+--------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+--------+----------------------------+---------+---------+---------------------+------+--------------------------+| 1 | SIMPLE | test | range | PRIMARY,idx_ip,idx_ip_from | idx_ip | 5 | NULL | 1 | Using where; Using index | | 1 | SIMPLE | test | eq_ref | PRIMARY | PRIMARY | 8 | ip2location.test.id | 1 | NULL |+----+-------------+-------+--------+----------------------------+---------+---------+---------------------+------+--------------------------+2 rows in set (0.01 sec)mysql> status; -------------- mysql Ver 14.14 Distrib 5.5.35, for debian-linux-gnu (x86_64) using readline 6.2Connection id: 4305567 Current database: ip2location Current user: ip2location@10.1.255.10 SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.6.13-log MySQL Community Server (GPL) Protocol version: 10 Connection: ip2location.cgs2bjzqxcxl.us-east-1.rds.amazonaws.com via TCP/IP Insert id: 1 Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 TCP port: 3306 Uptime: 30 days 18 hours 51 min 44 secThreads: 4 Questions: 21017670 Slow queries: 4 Opens: 188007 Flush tables: 1 Open tables: 147 Queries per second avg: 7.901--------------------------------------------- mysql> EXPLAIN SELECT * FROM test WHERE id IN ( -> SELECT id FROM test WHERE ip_from<=2352356 AND ip_to>=2352356 ); +----+--------------------+-------+-----------------+----------------------------+---------+---------+------+--------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+--------------------+-------+-----------------+----------------------------+---------+---------+------+--------+-------------+| 1 | PRIMARY | test | ALL | NULL | NULL | NULL | NULL | 206509 | Using where || 2 | DEPENDENT SUBQUERY | test | unique_subquery | PRIMARY,idx_ip,idx_ip_from | PRIMARY | 8 | func | 1 | Using where |+----+--------------------+-------+-----------------+----------------------------+---------+---------+------+--------+-------------+2 rows in set (0.00 sec)mysql> status; -------------- mysql Ver 14.14 Distrib 5.5.37, for Linux (x86_64) using EditLine wrapperConnection id: 5 Current database: howe Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.5.37-log Source distribution Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8 Db characterset: utf8 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /home/mysql/mysql5/tmp/mysql.sock Uptime: 6 min 52 secThreads: 3 Questions: 208 Slow queries: 0 Opens: 112 Flush tables: 1 Open tables: 105 Queries per second avg: 0.504--------------

Different versions have different processing methods for IN, and 5.6 is better than previous versions.

• SELECT * FROM test WHEREIp_from <= 2352356 order by ip_from desc limit 1;

mysql> explain SELECT * FROM test WHERE ip_from<=2352356 ORDER BY ip_from DESC LIMIT 1;+----+-------------+-------+-------+--------------------+--------+---------+------+------+-----------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+-------+--------------------+--------+---------+------+------+-----------------------+| 1 | SIMPLE | test | range | idx_ip,idx_ip_from | idx_ip | 5 | NULL | 1 | Using index condition |+----+-------------+-------+-------+--------------------+--------+---------+------+------+-----------------------+1 row in set (0.00 sec)

DeleteIdx_ip index.

mysql> explain SELECT * FROM test WHERE ip_from<=2352356 ORDER BY ip_from DESC LIMIT 1;+----+-------------+-------+-------+---------------+-------------+---------+------+------+-----------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+-------+---------------+-------------+---------+------+------+-----------------------+| 1 | SIMPLE | test | range | idx_ip_from | idx_ip_from | 5 | NULL | 1 | Using index condition |+----+-------------+-------+-------+---------------+-------------+---------+------+------+-----------------------+1 row in set (0.00 sec)

 This method is optimal. The ip segment features, order by, and limit are used.

 

 

 

Storage space occupied by IP addresses

4 bytes. According to the TCP/IP (Transport Control Protocol/Internet Protocol, Transmission Control Protocol/Internet Protocol) Protocol, IP addresses are expressed in binary format. Each IP address is 32 bits in length, and the bits are converted into bytes, 4 bytes. For example, if an IP address in binary format is "00001010000000000000000000000001", it is too hard for people to process such a long address. For convenience, IP addresses are often written in decimal format, and different bytes are separated by the symbol. Therefore, the IP address above can be expressed as "10.0.0.1 ".

How can I save an IP address?

The batch creation file is named ip. bat.
Copy the following content to the file and change the IP address to your corresponding IP address.

@ Echo off
Set str1 = "Local Connection"
Set oknum = 192.168.120.238
Set oknum2 = 192.168.120.1
Set netnum = 255.255.255.0
Echo is executing... % ip %
Netsh interface ip set address % str1 % static % oknum % netnum % oknum2 % 1
Netsh interface ip set dns % str1 % static 202.96.104.15

You can create two files with different ip addresses.
Above is ip, gateway, mask, dns
Change by yourself

You can also use this software.

IPSwitcher