mysql update multiple columns with same now()

user_207603 0
Viewed 246101

I need to update 2 datetime columns, and I need them to be exactly the same, using mysql version 4.1.20. I'm using this query:

mysql> update table set last_update=now(), last_monitor=now() where id=1;

It is safe or there is a chance that the columns are update with different time, because of the 2 visible calls to now()?
I don't think that it can be update with different values (I think internally mysql calls now() just once per row or something similar), but I'm not an expert, what do you think?

Update: Second question was extracted here.

sql-update mysql
7 Answers

Mysql isn't very clever. When you want to use the same timestamp in multiple update or insert queries, you need to declare a variable.

When you use the now() function, the system will call the current timestamp every time you call it in another query.

user_30418360

MySQL evaluates now() once per statement when the statement commences execution. So it is safe to have multiple visible now() calls per statement.

select now(); select now(), sleep(10), now(); select now();
+---------------------+
| now()               |
+---------------------+
| 2018-11-05 16:54:00 |
+---------------------+
1 row in set (0.00 sec)

+---------------------+-----------+---------------------+
| now()               | sleep(10) | now()               |
+---------------------+-----------+---------------------+
| 2018-11-05 16:54:00 |         0 | 2018-11-05 16:54:00 |
+---------------------+-----------+---------------------+
1 row in set (10.00 sec)

+---------------------+
| now()               |
+---------------------+
| 2018-11-05 16:54:10 |
+---------------------+
1 row in set (0.00 sec)
user_93246780

There are 2 ways to this;

First, I would advice you declare now() as a variable before injecting it into the sql statement. Lets say;

var x = now();
mysql> UPDATE table SET last_update=$x, last_monitor=$x WHERE id=1;

Logically if you want a different input for last_monitor then you will add another variable like;

var y = time();
mysql> UPDATE table SET last_update=$x, last_monitor=$y WHERE id=1;

This way you can use the variables as many times as you can, not only in mysql statements but also in the server-side scripting-language(like PHP) you are using in your project. Remember these same variables can be inserted as inputs in a form on the front-end of the application. That makes the project dynamic and not static.

Secondly if now() indicates time of update then using mysql you can decalre the property of the row as a timestamp. Every time a row is inserted or updated time is updated too.

user_106427160
Related