SQL NOW() in long running query

Question

Say I have long running update query

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

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?

Answer 1

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

Is this too short as an answer?

Okay, more info MySQL reference for NOW()

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.

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 |
+---------------------+----------+---------------------+

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

You get:

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