java - 停止在 Java 中以编程方式执行 Spring JDBC 查询

Question

目前，我正在使用 Java Spring JDBC 库运行长时间运行的 MySQL 查询。对于这个程序，我想要一种停止正在运行的查询的方法。我想以编程方式执行此操作，因此在这里不需要杀死进程。

此外，不推荐使用 Java 代码设置超时和终止线程。

有没有办法使用编程约定来做到这一点，或者有没有首选的方式或设计来在 Java 中实现这个用例？

标签： javamysqlspringjdbcspring-jdbc

Answer 1

您可以使用 SQL KILL 命令停止进程

样本

MariaDB [(none)]> show processlist;;
+----+-------------+-----------+------+---------+------+--------------------------+-------------------+----------+
| Id | User        | Host      | db   | Command | Time | State                    | Info              | Progress |
+----+-------------+-----------+------+---------+------+--------------------------+-------------------+----------+
|  1 | system user |           | NULL | Daemon  | NULL | InnoDB purge worker      | NULL              |    0.000 |
|  2 | system user |           | NULL | Daemon  | NULL | InnoDB purge worker      | NULL              |    0.000 |
|  3 | system user |           | NULL | Daemon  | NULL | InnoDB purge worker      | NULL              |    0.000 |
|  4 | system user |           | NULL | Daemon  | NULL | InnoDB purge coordinator | NULL              |    0.000 |
|  5 | system user |           | NULL | Daemon  | NULL | InnoDB shutdown handler  | NULL              |    0.000 |
| 46 | root        | localhost | NULL | Query   |   56 | User sleep               | select sleep(199) |    0.000 |
| 47 | root        | localhost | NULL | Query   |    0 | init                     | show processlist  |    0.000 |
+----+-------------+-----------+------+---------+------+--------------------------+-------------------+----------+
7 rows in set (0.00 sec)


MariaDB [(none)]> kill 46;
Query OK, 0 rows affected (0.02 sec)

MariaDB [(none)]> show processlist;;
+----+-------------+-----------+------+---------+------+--------------------------+------------------+----------+
| Id | User        | Host      | db   | Command | Time | State                    | Info             | Progress |
+----+-------------+-----------+------+---------+------+--------------------------+------------------+----------+
|  1 | system user |           | NULL | Daemon  | NULL | InnoDB purge worker      | NULL             |    0.000 |
|  2 | system user |           | NULL | Daemon  | NULL | InnoDB purge worker      | NULL             |    0.000 |
|  3 | system user |           | NULL | Daemon  | NULL | InnoDB purge worker      | NULL             |    0.000 |
|  4 | system user |           | NULL | Daemon  | NULL | InnoDB purge coordinator | NULL             |    0.000 |
|  5 | system user |           | NULL | Daemon  | NULL | InnoDB shutdown handler  | NULL             |    0.000 |
| 47 | root        | localhost | NULL | Query   |    0 | init                     | show processlist |    0.000 |
+----+-------------+-----------+------+---------+------+--------------------------+------------------+----------+
6 rows in set (0.00 sec)