Sql Now() In Long Running Query

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_tabl

Solution 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-1213:47:36|0|2006-04-1213:47:36|+---------------------+----------+---------------------+

mysql>SELECT SYSDATE(), SLEEP(2), SYSDATE();
+---------------------+----------+---------------------+| SYSDATE()           | SLEEP(2) | SYSDATE()           |+---------------------+----------+---------------------+|2006-04-1213:47:44|0|2006-04-1213: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 (select1 N unionallselect2unionallselect3) M

You get:

N   now()           sleep(2)  sysdate()
12011-04-0223:55:2702011-04-0223:55:2922011-04-0223:55:2702011-04-0223:55:3132011-04-0223:55:2702011-04-0223:55:33