在存储过程中是否有SLEEP（）的方法？

I have a stored procedure I'd like to run forever, but sleep for one second in a loop. When it wakes up it would poll a table to see if it should do some work. Work only needs to be done every minute, so there is no worry about the poll table getting hit with updates from two writers at the same time.

我有一个存储过程，我想永远运行，但在一个循环中睡一秒钟。当它醒来时，它将轮询一个表，看它是否应该做一些工作。工作只需要每分钟完成，因此不必担心轮询表会同时受到来自两个编写者的更新的影响。

What is the best way to SLEEP() for an interval in a stored procedure? It would be nice, actually, if it could sleep for 200 milliseconds, but one second would work too.

SLEEP（）在存储过程中的间隔的最佳方法是什么？实际上，如果它可以睡眠200毫秒，那将是很好的，但是一秒也可以工作。

4 个解决方案

#1

I've encountered the same problem. After googling a lot, I found out that we can use

我遇到了同样的问题。经过谷歌搜索后，我发现我们可以使用

SELECT SLEEP(<seconds>);

to delay our procedures for this many seconds. In your case, using

延迟我们的程序这么多秒。在你的情况下，使用

SELECT SLEEP(0.2);

would be just fine.

会没事的。

#2

You can use:

您可以使用：

DO SLEEP(0.2);

Reference: http://dev.mysql.com/doc/refman/5.7/en/do.html

参考：http：//dev.mysql.com/doc/refman/5.7/en/do.html

要么

SELECT SLEEP(0.2);

Reference: http://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_sleep

参考：http：//dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_sleep

#3

You dont specify which database you are using, but generally the way to achive what you want is not to have an infinetly running sproc but to have some external component - like a scheduler, or SQL Server Agent in MSSQL - execute the sproc every so often.

你没有指定你正在使用哪个数据库，但一般来说，获得你想要的东西的方法不是拥有一个infinetly运行的sproc，而是拥有一些外部组件 - 比如调度程序或MSSQL中的SQL Server代理 - 每隔一段时间执行一次sproc 。

#4

I would use cron (linux) or Windows Task Scheduler (windows) to schedule it to run every minute, and have your stored procedure complete its task, then exit.

我会使用cron（linux）或Windows Task Scheduler（windows）来安排它每分钟运行一次，让你的存储过程完成它的任务，然后退出。

Having stored procs run "forever" is a really bad idea. You have long-held connections (which can die), you could inadvertently lock a table (forever), etc.

存储过程“永远”运行是一个非常糟糕的主意。你有长期连接（可能会死），你可能会无意中锁定一个表（永远），等等。

Since they have nothing to do with each other, separating your "loop" from your "task" is good design, and it means you can use (different) tools that are most appropriate for each part.

由于它们彼此无关，因此将“循环”与“任务”分开是一种很好的设计，这意味着您可以使用最适合每个部分的（不同）工具。

#1