长时间运行的查询中的 SQL NOW() [英] SQL NOW() in long running query

问题描述

说我有很长时间运行更新查询

Say I have long running update query

update some_table 
set modification_time = now() 
where (something incredibly complex);

some_table 中的修改时间的值是什么?它们是相同的还是不同的(例如，执行查询需要 2 天时间).

What will be values of modification_time in some_table? Will they be same or different (say, it took 2 days for query to execute).

如果它们会有所不同，我该如何编写此查询以使其全部相同?

And if they will be different, how do I write this query so that they all are same?

推荐答案

它们都是一样的，因为 NOW() 在查询开始时被锁定.

They will all be the same, since NOW() is locked in at the time of query start.

这个答案是否太短了?

好的，更多信息 MySQL 参考现在()

NOW() 返回一个常量时间，表示语句开始执行的时间.(在存储的函数或触发器中，NOW() 返回函数或触发语句开始执行的时间.)这与 SYSDATE() 的行为不同，后者返回它执行的确切时间.

NOW() returns a constant time that indicates the time at which the statement began to execute. (Within a stored function or trigger, NOW() returns the time at which the function or triggering statement began to execute.) This differs from the behavior for SYSDATE(), which returns the exact time at which it executes.

阅读SYSDATE() 但是，其中包含此代码段

It is actually more interesting to read the manual entry for SYSDATE() however, which contains this snippet

mysql> SELECT NOW(), SLEEP(2), NOW();
+---------------------+----------+---------------------+
| NOW()               | SLEEP(2) | NOW()               |
+---------------------+----------+---------------------+
| 2006-04-12 13:47:36 |        0 | 2006-04-12 13:47:36 |
+---------------------+----------+---------------------+

mysql> SELECT SYSDATE(), SLEEP(2), SYSDATE();
+---------------------+----------+---------------------+
| SYSDATE()           | SLEEP(2) | SYSDATE()           |
+---------------------+----------+---------------------+
| 2006-04-12 13:47:44 |        0 | 2006-04-12 13:47:46 |
+---------------------+----------+---------------------+

你问什么这么有趣..注意到你可以在查询中SLEEP??考虑这个查询(子查询只是模拟一个 3 记录表)

What's so interesting you ask.. notice that you can SLEEP in a query?? Consider this query (the sub-query just emulates a 3-record table)

select *, now(), sleep(2), sysdate()
from (select 1 N union all select 2 union all select 3) M

你得到:

N   now()           sleep(2)  sysdate()
1   2011-04-02 23:55:27   0   2011-04-02 23:55:29
2   2011-04-02 23:55:27   0   2011-04-02 23:55:31
3   2011-04-02 23:55:27   0   2011-04-02 23:55:33

这篇关于长时间运行的查询中的 SQL NOW()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！