MySQL 客户端与MySQL server建立连接后，就可以执行SQL语句了。
如何查看一个连接上是否正在执行SQL语句，或者连接是否处于空闲呢？
下面我们做下测试。
1.查看连接的空闲时间
首先看下测试程序。
代码中，每3s执行一条sql语句。
//conn_idle_time.go
package main
import (
"database/sql"
"log"
"time"
_ "github.com/go-sql-driver/mysql"
)
var DB *sql.DB
var dataBase = "root:Aa123456@tcp(127.0.0.1:3306)/?loc=Local&parseTime=true"
func mysqlInit() {
var err error
DB, err = sql.Open("mysql", dataBase)
if err != nil {
log.Fatalln("open db fail:", err)
}
DB.SetMaxOpenConns(1)
err = DB.Ping()
if err != nil {
log.Fatalln("ping db fail:", err)
}
}
func main() {
mysqlInit()
for {
execSql()
time.Sleep(3*time.Second)
}
}
func execSql() {
var connection_id int
err := DB.QueryRow("select CONNECTION_ID()").Scan(&connection_id)
if err != nil {
log.Println("query connection id failed:", err)
return
}
log.Println("connection id:", connection_id)
}
启动程序，输出结果如下：
2019/10/13 12:20:59 connection id: 5
2019/10/13 12:21:02 connection id: 5
2019/10/13 12:21:05 connection id: 5
2019/10/13 12:21:08 connection id: 5
2019/10/13 12:21:11 connection id: 5
2019/10/13 12:21:14 connection id: 5
2019/10/13 12:21:17 connection id: 5
2019/10/13 12:21:20 connection id: 5
2019/10/13 12:21:23 connection id: 5
2019/10/13 12:21:26 connection id: 5
2019/10/13 12:21:29 connection id: 5
可以看到，连接MySQL的connection id 为5。
接着，通过show processlist查看连接情况：
mysql> show processlist;
+----+------+-----------------+------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------+------+---------+------+----------+------------------+
| 3 | root | localhost | NULL | Query | 0 | starting | show processlist |
| 5 | root | localhost:51823 | NULL | Sleep | 0 | | NULL |
+----+------+-----------------+------+---------+------+----------+------------------+
2 rows in set (0.00 sec)
mysql> show processlist;
+----+------+-----------------+------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------+------+---------+------+----------+------------------+
| 3 | root | localhost | NULL | Query | 0 | starting | show processlist |
| 5 | root | localhost:51823 | NULL | Sleep | 3 | | NULL |
+----+------+-----------------+------+---------+------+----------+------------------+
2 rows in set (0.00 sec)
mysql> show processlist;
+----+------+-----------------+------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------+------+---------+------+----------+------------------+
| 3 | root | localhost | NULL | Query | 0 | starting | show processlist |
| 5 | root | localhost:51823 | NULL | Sleep | 1 | | NULL |
+----+------+-----------------+------+---------+------+----------+------------------+
2 rows in set (0.00 sec)
mysql> show processlist;
+----+------+-----------------+------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------+------+---------+------+----------+------------------+
| 3 | root | localhost | NULL | Query | 0 | starting | show processlist |
| 5 | root | localhost:51823 | NULL | Sleep | 3 | | NULL |
+----+------+-----------------+------+---------+------+----------+------------------+
2 rows in set (0.00 sec)
mysql> show processlist;
+----+------+-----------------+------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------+------+---------+------+----------+------------------+
| 3 | root | localhost | NULL | Query | 0 | starting | show processlist |
| 5 | root | localhost:51823 | NULL | Sleep | 2 | | NULL |
+----+------+-----------------+------+---------+------+----------+------------------+
2 rows in set (0.00 sec)
mysql> show processlist;
+----+------+-----------------+------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------+------+---------+------+----------+------------------+
| 3 | root | localhost | NULL | Query | 0 | starting | show processlist |
| 5 | root | localhost:51823 | NULL | Sleep | 1 | | NULL |
+----+------+-----------------+------+---------+------+----------+------------------+
2 rows in set (0.00 sec)
可以看到localhost:51823是连接MySQL server使用的端口，当Command列为Sleep时，表示当前连接是空闲的，Time列显示了处于当前状态的时间。
每执行一次SQL语句，Sleep状态的Time都会被重置为0.
如果当前连接一直没有执行SQL语句，那么Sleep状态的Time会一直增加，直到达到连接最大时间(由参数wait_timeout控制)，最后连接断开。
再来看下，如何查看正在执行的SQL语句。
2.查看连接正在执行的SQL
将上面示例程序执行的SQL语句改为:
select SLEEP(10)
查看连接情况:
mysql> show processlist;
+----+------+-----------------+------+---------+------+------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------+------+---------+------+------------+------------------+
| 3 | root | localhost | NULL | Query | 0 | starting | show processlist |
| 6 | root | localhost:52186 | NULL | Query | 1 | User sleep | select SLEEP(10) |
+----+------+-----------------+------+---------+------+------------+------------------+
2 rows in set (0.00 sec)
mysql> show processlist;
+----+------+-----------------+------+---------+------+------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------+------+---------+------+------------+------------------+
| 3 | root | localhost | NULL | Query | 0 | starting | show processlist |
| 7 | root | localhost:52200 | NULL | Query | 3 | User sleep | select SLEEP(10) |
+----+------+-----------------+------+---------+------+------------+------------------+
2 rows in set (0.00 sec)
mysql> show processlist;
+----+------+-----------------+------+---------+------+------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------+------+---------+------+------------+------------------+
| 3 | root | localhost | NULL | Query | 0 | starting | show processlist |
| 7 | root | localhost:52200 | NULL | Query | 7 | User sleep | select SLEEP(10) |
+----+------+-----------------+------+---------+------+------------+------------------+
2 rows in set (0.00 sec)
mysql> show processlist;
+----+------+-----------------+------+---------+------+------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------+------+---------+------+------------+------------------+
| 3 | root | localhost | NULL | Query | 0 | starting | show processlist |
| 7 | root | localhost:52200 | NULL | Query | 8 | User sleep | select SLEEP(10) |
+----+------+-----------------+------+---------+------+------------+------------------+
2 rows in set (0.00 sec)
mysql> show processlist;
+----+------+-----------------+------+---------+------+------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------+------+---------+------+------------+------------------+
| 3 | root | localhost | NULL | Query | 0 | starting | show processlist |
| 7 | root | localhost:52200 | NULL | Query | 10 | User sleep | select SLEEP(10) |
+----+------+-----------------+------+---------+------+------------+------------------+
2 rows in set (0.00 sec)
mysql> show processlist;
+----+------+-----------------+------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------+------+---------+------+----------+------------------+
| 3 | root | localhost | NULL | Query | 0 | starting | show processlist |
| 7 | root | localhost:52200 | NULL | Sleep | 12 | | NULL |
+----+------+-----------------+------+---------+------+----------+------------------+
2 rows in set (0.00 sec)
从输出可以看到，Command为Query表示正在执行语句，Info列显示了正在执行的具体SQL语句。