mysql 用相同的 now() 更新多个列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3802256/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
mysql update multiple columns with same now()
提问by Radu Maris
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:
我需要更新 2 个日期时间列，并且我需要它们完全相同，使用 mysql 版本 4.1.20。我正在使用这个查询：
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?
这是安全的，或者由于对now()?的 2 个可见调用，列有可能在不同时间更新
我不认为它可以用不同的值更新（我认为内部 mysqlnow()每行调用一次或类似的东西），但我不是专家，你怎么看？
Update:Second question was extracted here.
回答by Radu Maris
Found a solution:
找到了解决办法：
mysql> UPDATE table SET last_update=now(), last_monitor=last_update WHERE id=1;
I found thisin MySQL Docs and after a few tests it works:
我在 MySQL Docs 中找到了这个，经过一些测试后它可以工作：
the following statement sets col2 to the current (updated) col1 value, not the original col1 value. The result is that col1 and col2 have the same value. This behavior differs from standard SQL.
UPDATE t1 SET col1 = col1 + 1, col2 = col1;
以下语句将 col2 设置为当前（更新后的） col1 值，而不是原始 col1 值。结果是 col1 和 col2 具有相同的值。此行为不同于标准 SQL。
更新 t1 SET col1 = col1 + 1, col2 = col1;
回答by Olivier
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.
Mysql 不是很聪明。当您想在多个更新或插入查询中使用相同的时间戳时，您需要声明一个变量。
When you use the now()function, the system will call the current timestamp every time you call it in another query.
当您使用该now()函数时，系统会在您每次在另一个查询中调用它时调用当前时间戳。
回答by Rich Andrews
MySQL evaluates now() once per statement when the statement commences execution. So it is safe to have multiple visible now() calls per statement.
当语句开始执行时，MySQL 对每个语句计算 now() 一次。所以每个语句有多个可见的 now() 调用是安全的。
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)
回答by Sachin Shanbhag
You can store the value of a now() in a variable before running the update query and then use that variable to update both the fields last_updateand last_monitor.
您可以在运行更新查询之前将 now() 的值存储在一个变量中，然后使用该变量来更新字段last_update和last_monitor.
This will ensure the now() is executed only once and same value is updated on both columns you need.
这将确保 now() 只执行一次，并且在您需要的两列上更新相同的值。
回答by Danny
You can put the following code on the default value of the timestamp column:
CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, so on update the two columns take the same value.
您可以将以下代码放在时间戳列的默认值上：
CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP，因此更新时两列取相同的值。
回答by sathia
If you really need to be sure that now()has the same value you can run two queries (that will answer to your second question too, in that case you are asking to update last_monitor = to last_updatebut last_updatehasn't been updated yet)
如果您确实需要确保now()具有相同的值，您可以运行两个查询（这也将回答您的第二个问题，在这种情况下，您要求update last_monitor = to last_update但last_update尚未更新）
you could do something like:
你可以这样做：
mysql> update table set last_update=now() where id=1;
mysql> update table set last_monitor = last_update where id=1;
anyway I think that mysql is clever enough to ask for now()only once per query.
无论如何，我认为 mysql 足够聪明，now()每个查询只要求一次。
回答by Wahinya Brian
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;
首先，我建议您在将 now() 注入到 sql 语句之前将其声明为变量。可以说;
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;
从逻辑上讲，如果您想要 last_monitor 的不同输入，那么您将添加另一个变量，例如；
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.
通过这种方式，您可以尽可能多地使用变量，不仅在 mysql 语句中，而且在您在项目中使用的服务器端脚本语言（如 PHP）中。请记住，这些相同的变量可以作为输入插入到应用程序前端的表单中。这使得项目动态而不是静态。
Secondlyif 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.
其次，如果 now() 指示更新时间，那么使用 mysql 您可以将行的属性 decalre 作为时间戳。每次插入或更新一行时，时间也会更新。
