SQL NOW () in a long query

Question

Let's say that I have already installed an update request for a long time

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

What will be the modify_time values ​​in some_table? Will they be the same or different (say, it took 2 days to complete the request).

And if they are different, how can I write this request so that they are all the same?

Answer 1

They will all be the same, since NOW () is blocked at the start of the request.

Is this a too short answer?

Ok, more info MySQL reference for NOW ()

NOW () returns a constant time that indicates the time when the statement began to execute . (Within a stored function or trigger, NOW () returns the time at which the function or start instruction begins to execute.) This is different from the SYSDATE () behavior, which returns the exact time at which it is executed.

It’s actually more interesting to read the manual SYSDATE () record that 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 interesting things do you ask. Notice that you can SLEEP in a query? Consider this query (a subquery just emulates a table of 3 records)

 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