在终端上运行的MySQL命令被卡住了，但是phpmyadmin成功了

Z时代
2024-01-10
分类：问答

我想从成员IP获取位置。我可以在phpmyadmin上使用这个命令，而且花费大约2秒。但是当通过终端在MySQL上执行该命令时，它会被卡住。我该如何解决这个问题。

SQL命令：在终端上运行的MySQL命令被卡住了，但是phpmyadmin成功了

SELECT 
    ID,country 
FROM 
    (SELECT ID,substring_index(members.server_ids,',',1) AS IP FROM members) AS A 
JOIN 
    (SELECT country,start,end FROM ip) AS B 
ON 
    INET_ATON(A.IP) BETWEEN INET_ATON(B.start) AND INET_ATON(B.end);

降序成员：

+---------------------+--------------+------+-----+---------+----------------+ 
| Field    | Type   | Null | Key | Default | Extra   | 
+---------------------+--------------+------+-----+---------+----------------+ 
| ID     | int(11)  | NO | PRI | NULL | auto_increment | 
| USER_NAME   | varchar(100) | NO | UNI | NULL |    | 
| PASSWORD   | varchar(32) | YES |  | NULL |    | 
| NAME    | varchar(100) | YES |  | NULL |    | 
| EMAIL    | varchar(50) | YES | MUL | NULL |    | 
| SAFE_EMAIL   | varchar(50) | YES |  | NULL |    | 
| NICK_NAME   | varchar(100) | YES | MUL | NULL |    | 
| GENDER    | tinyint(1) | YES |  | 0  |    | 
| BIRTHDAY   | date   | YES |  | NULL |    | 
| CREATEDAY   | datetime  | YES |  | NULL |    | 
| PHONE    | varchar(20) | YES |  | NULL |    | 
| MOBILE    | varchar(15) | YES |  | NULL |    | 
| ID_CARD    | varchar(32) | YES |  | NULL |    | 
| COUNTY    | int(11)  | YES |  | NULL |    | 
| ADDRESS    | varchar(255) | YES |  | NULL |    | 
| LOGIN_TIME   | datetime  | YES |  | NULL |    | 
| BUY_SN    | varchar(50) | YES |  | NULL |    | 
| BUY_KIND   | varchar(50) | YES |  | NULL |    | 
| PARTNER_SN   | varchar(50) | YES |  | NULL |    | 
| cookie_value  | varchar(100) | YES |  | NULL |    | 
| CONSIGNEE_INFO  | int(11)  | YES | MUL | NULL |    | 
| LOGIN_COUNT   | int(11)  | YES |  | NULL |    | 
| PAY_COUNT   | int(11)  | YES |  | NULL |    | 
| status    | tinyint(4) | YES |  | 1  |    | 
| experience   | int(11)  | YES |  | 0  |    | 
| amount    | int(11)  | YES |  | 0  |    | 
| score    | int(11)  | YES |  | 0  |    | 
| is_facebook_account | tinyint(4) | YES |  | 0  |    | 
| avatar    | varchar(200) | YES |  | NULL |    | 
| avatar_status  | tinyint(1) | YES |  | 0  |    | 
| member_authhash  | varchar(50) | YES |  | NULL |    | 
| job     | varchar(50) | YES |  | NULL |    | 
| server_ids   | varchar(255) | YES |  | NULL |    | 
+---------------------+--------------+------+-----+---------+----------------+

SELECT ID，server_ids FROM成员LIMIT 10000,1;

+-------+--------------------------+ 
| ID | server_ids    | 
+-------+--------------------------+ 
| 20005 | 61.20.167.219, 127.0.0.1 | 
+-------+--------------------------+

说明IP：

+---------+------------------+------+-----+---------+----------------+ 
| Field | Type    | Null | Key | Default | Extra   | 
+---------+------------------+------+-----+---------+----------------+ 
| id  | int(10) unsigned | NO | PRI | NULL | auto_increment | 
| country | char(2)   | NO |  | NULL |    | 
| type | char(4)   | NO |  | NULL |    | 
| start | varchar(15)  | NO | MUL | NULL |    | 
| end  | varchar(15)  | NO | MUL | NULL |    | 
+---------+------------------+------+-----+---------+----------------+

SELECT * FROM IP LIMIT 100.1;

+-----+---------+------+-----------+---------------+ 
| id | country | type | start  | end   | 
+-----+---------+------+-----------+---------------+ 
| 101 | IN  | ipv4 | 1.187.0.0 | 1.187.255.255 | 
+-----+---------+------+-----------+---------------+