今天在写报表时，用Navicat客户端运行一个存储过程，由于语句的原因导致程序一直运行中，整个Navicat客户端都卡住了，无法关闭Navicat客户端。
于是就想到了kill掉这个线程，登录服务器mysql，想用showprocesslist方法找出程序ID，结果发现有很多用户在登录并且在执行语句，而且showprocesslist没办法加条件检索
mysql> show processlist;
+------+---------+----------------------+---------------------------+---------+-------+--------------+------------------------------------------------------------------------------------------------------+-----------+---------------+
| Id| User| Host | db| Command | Time | State| Info | Rows_sent | Rows_examined |
+------+---------+----------------------+---------------------------+---------+-------+--------------+------------------------------------------------------------------------------------------------------+-----------+---------------+
| 2158 | root| 172.158.8.136:50154 | NULL | Sleep| 20755 | | NULL |219 |219 |
| 2159 | root| 172.158.8.136:50157 | ADM| Sleep| 4087 | | NULL | 0 | 0 |
| 2187 | prouser | 172.158.8.226:49647 | NULL | Sleep| 21335 | | NULL |403 |403 |
| 2189 | admin| 172.158.8.226:49692 | skw_reportdata | Sleep| 4269 | | NULL | 0 | 0 |
| 2203 | admin| 172.158.8.226:49716 | skw_reportdata | Sleep| 20874 | | NULL | 1000 | 1000 |
| 2207 | admin| 172.158.8.226:49725 | skw_reportdata | Sleep| 20844 | | NULL | 0 | 0 |
| 2212 | root| 172.158.8.136:50556 | CDM| Sleep|930 | | NULL | 0 | 1 |
| 2217 | prouser | 172.30.249.28:47190 |account| Sleep| 11360 | | NULL | 0 | 0 |
| 2218 | root| 172.158.8.136:50601 | DW| Sleep| 20095 | | NULL | 0 | 0 |
| 2220 | admin| 172.158.8.61:49553| NULL | Sleep| 20247 | | NULL |19 |19 |
| 2221 | admin| 172.158.8.61:49554| ADM| Sleep| 20246 | | NULL | 7 | 7 |
| 2233 | prouser | 172.158.8.125:63769 | account| Sleep| 19659 | | NULL |21 |21 |
| 2234 | prouser | 172.158.8.125:63771 | account| Sleep| 19512 | | NULL
.......此处省略大部分结果
结果很不理想，当然我们可以用其他办法，show processlist的结果是来自information_schema.processlist表中
mysql> desc information_schema.processlist;
+---------------+---------------------+------+-----+---------+-------+
| Field | Type| Null | Key | Default | Extra |
+---------------+---------------------+------+-----+---------+-------+
| ID| bigint(21) unsigned | NO| | 0||
| USER | varchar(16) | NO| | ||
| HOST | varchar(64) | NO| | ||
| DB| varchar(64) | YES | | NULL||
| COMMAND| varchar(16) | NO| | ||
| TIME | int(7) | NO| | 0||
| STATE | varchar(64) | YES | | NULL||
| INFO | longtext| YES | | NULL||
| TIME_MS| bigint(21) | NO| | 0||
| ROWS_SENT | bigint(21) unsigned | NO| | 0||
| ROWS_EXAMINED | bigint(21) unsigned | NO| | 0||
| TID| bigint(21) unsigned | YES | | NULL||
+---------------+---------------------+------+-----+---------+-------+
12 rows in set (0.00 sec)
我们可以通过这张表进行检索出我们需要的结果
mysql> SELECT id,user, host, time, command,info from information_schema.processlist where user='root' and info like '%insert into%'\G;
*************************** 1. row ***************************
id: 2695
user: root
host: localhost
time: 0
command: Query
info: SELECT id,user, host, time, command,info from information_schema.processlist where user='root' and info like '%insert into%'
*************************** 2. row ***************************
id: 2645
user: root
host: 172.158.8.136:53258
time: 1522
command: Query
info: insert into `CDM`.cdm_product(product_id,product_type,product_name,add_rate,base_rate,year_rate,startdate,enddate,is_current)
SELECT a.id,CASE
WHEN IFNULL(i.enlending_type, '9') = '0' THEN '1'
WHEN IFNULL(i.enlending_type, '9') = '1' THEN '2'
WHEN IFNULL(i.enlending_type, '9') = '4' THEN '3'
WHEN IFNULL(i.enlending_type, '9') = '5' THEN '4'
WHEN IFNULL(i.enlending_type, '9') = '6' THEN'5'
WHEN IFNULL(i.enlending_type, '9') = '7' THEN '6'
WHEN IFNULL(i.enlending_type, '9') = '9' THEN '7' ELSE IFNULL(i.enlending_type, '9')END,
a.loan_info_title,0,a.loan_info_interest,a.loan_info_interest,'20140808','99990101',1
FROM DW.dw_biz_invest_statements a
left JOIN DW.dw_biz_loan_info i
ON a.loan_info_id=i.id
GROUP BY loan_info_title
2 rows in set (0.01 sec)
第二条就是我们要的结果kill掉就OK了
mysql>kill2645;
Query OK, 0 rows affected (0.00 sec)
这个时候Navicat客户端就恢复正常了。！