mysql 执行时间限制,如何限制SQL执行时间

Some sql is not well written. Sometimes a search costs hours in applications.

When a application(maybe a website) submit a query which run long time, I have to restart the mysql.

How can I limit a sql query's execution time in the database side?

解决方案

To auto kill a query in MySQL after a long execution time:

Create a stored procedure as:

DECLARE CURSOR cur1 FOR SELECT ID

FROM INFORMATION_SCHEMA.PROCESSLIST

WHERE COMMAND = 'Query' AND TIME > 120;

then inside curosr's loop do:

FETCH ID INTO @var_kill_id;

KILL QUERY @var_kill_id;

Create EVENT FOR EVERY 5 SECONDS and just CALL the above procedure inside it.

Note: KILL QUERY just kills the query and MySQL connection is not broken. see here.