ip地址使用int類型存儲，用INET_NTOA()和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.環境
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.使用
查詢某個值屬於哪個ip段。
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)
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.2 Connection id: 4305567 Current database: ip2location Current user: [email protected] 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 sec Threads: 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 wrapper Connection 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 sec Threads: 3 Questions: 208 Slow queries: 0 Opens: 112 Flush tables: 1 Open tables: 105 Queries per second avg: 0.504 --------------
不同版本對IN的處理方式不同，5.6優於以前的版本
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)
刪除idx_ip索引。
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)
這個方式是最優。利用了ip段的特性、order by、limit。
4個字節。按照TCP/IP（Transport Control Protocol/Internet Protocol，傳輸控制協議/Internet協議）協議規定，IP地址用二進制來表示，每個IP地址長32bit，比特換算成字節，就是4個字節。例如一個采用二進制形式的IP地址是“00001010000000000000000000000001”，這麼長的地址，人們處理起來也太費勁了。為了方便人們的使用，IP地址經常被寫成十進制的形式，中間使用符號“.”分開不同的字節。於是，上面的IP地址可以表示為“10.0.0.1”。
建批處理文件 名字為ip.bat
將下邊的內容復制到文件裡 將裡面的IP改成你對應的IP
@echo off
set str1="本地連接"
set oknum=192.168.120.238
set oknum2=192.168.120.1
set netnum=255.255.255.0
echo 正在執行... %ip%
netsh interface ip set address %str1% static %oknum% %netnum% %oknum2% 1
netsh interface ip set dns %str1% static 202.96.104.15
你可以建立2個文件，分別下上不同的ip，運行就好了
上面是ip，網關，掩碼，dns
自己改
也可以使用這個軟件
IPSwitcher
