英文:
MySQL - trigger ON UPDATE without changing any other columns
问题
示例表格定义如下：
```sql
create table testtable (
some_id int primary key,
ts timestamp default 0 on update CURRENT_TIMESTAMP,
＜零个或多个列...＞
);
是否可以针对此表运行UPDATE查询，根据给定的some_id，以便将该行的ts更新为当前时间戳？
一种方法是使用
UPDATE testtable SET column1 = column1 WHERE some_id = %s
然而，这样做是否存在任何缺点？在表格只有两列 - some_id 和 ts 的情况下又如何？
<details>
<summary>英文:</summary>
Example table is defined like this:
```sql
create table testtable (
some_id int primary key,
ts timestamp default 0 on update CURRENT_TIMESTAMP,
<zero or more columns...>
);
Is it possible to run UPDATE query on this table, given some_id, so that ts gets updated to the current timestamp for that row?
One way would be using
UPDATE testtable SET column1 = column1 WHERE some_id = %s
however, are there any drawbacks to this? What about when the table only has two columns - some_id and ts in this case?
答案1
得分: 2
看起来你有一个表格，想要做一个巧妙的小技巧，你想要更新一行，但实际上并不想改变任何数据。相反，你只想让时间戳列（ts）更新为当前时间。所以，就像在说：“嘿，数据库，假装我更新了什么东西，但实际上只是更新时间戳！”
你分享的一个小技巧是告诉数据库将某一列设置为它已经有的值。有点像告诉某人：“换件衬衫…但穿上同样的衬衫。” 数据库会说：“好的！” 并模拟一次更新，这也会更新时间戳，因为它被设置为在更新发生时执行。
所以你会这样说：
UPDATE my_table SET column_name = column_name WHERE some_id = whatever_id_you_want;
现在，你可能会好奇如果没有其他列可以“假装更新”会发生什么。你仍然需要伪造一个更新来刷新时间戳。因此，如果表只有some_id和ts，你仍然可以使用相同的技巧，但使用some_id：
UPDATE my_table SET some_id = some_id WHERE some_id = whatever_id_you_want;
但是，尽管这是一个巧妙的技巧，它有点像对数据库说一个小小的谎言，这可能会有一些不利之处：
对数据库额外工作: 数据库仍然认为发生了变化，所以它正在做额外的工作，尽管实际上什么都没有发生。如果你有大量数据或经常使用这个技巧，这可能有点麻烦。
其他触发器可能会混淆: 如果在数据更新时有其他自动化操作发生，它们也会触发！就像意外触发了警报一样。
不必要的记录（日志）: 如果你的数据库保留了所有更改的日志，它也会记录这个，这可能并不理想，因为实际上什么都没有改变。
它会暂时锁定行: 这个技巧在“更改”数据时会在数据上放一个小锁。如果你的数据库非常繁忙，有很多活动，这可能会导致交通堵塞。
那么，有更好的方法吗？如果你只想说：“嘿，更新时间戳，什么都不做！”那就直接更新时间戳，像这样：
UPDATE my_table SET ts = CURRENT_TIMESTAMP WHERE some_id = whatever_id_you_want;
这样，你对数据库诚实，告诉它你真正想做的事情。而且，阅读你的代码的人不会不明白你为什么要做这个小技巧。清晰而简单！
英文:
It seems like you have a table, and you want to do a sort of sneaky trick where you update a row, but you don't actually want to change any data. Instead, you just want to make the timestamp column (ts) update itself to the current time. So, it's like saying, "Hey database, pretend I updated something, but actually, just update the timestamp!"
One trick you shared is telling the database to set a column to the same value it already has. Kind of like telling someone, "Change your shirt...but put on the same shirt." The database says, "Well, okay!" and goes through the motions of an update, which also updates the timestamp since it’s set to do that whenever an update happens.
So you'd say:
UPDATE my_table SET column_name = column_name WHERE some_id = whatever_id_you_want;
Now, you were curious what happens if there's no other column to "pretend update." You'd still need to fake an update to get the timestamp to refresh. So if the table only had some_id and ts, you can still do the same trick, but with some_id:
UPDATE my_table SET some_id = some_id WHERE some_id = whatever_id_you_want;
But, you know, even though this is a nifty trick, it's kinda like telling a little white lie to the database, and that can have some downsides:
Extra Effort for Database: The database still thinks something's changed, so it’s doing extra work, even though nothing really did. This can be a bit of a drag if you have tons of data or do this trick a lot.
Other Triggers Might Get Confused: If there are other automated actions that happen when data is updated, those will go off too! It’s like accidentally setting off an alarm.
Unnecessary Paperwork (Logs): If your database keeps a diary (logs) of all the changes, it's going to jot this down too, which might not be ideal since nothing really changed.
It Locks Up the Row Temporarily: This trick puts a tiny padlock on the data while it's "changing." If you have a bustling database with lots of activity, this might cause a traffic jam.
So, what’s a better way? Well, if you just want to say, “Hey, update the timestamp and nothing else!” be direct and update the timestamp yourself like this:
UPDATE my_table SET ts = CURRENT_TIMESTAMP WHERE some_id = whatever_id_you_want;
This way, you’re honest with the database and just tell it what you really want to do. Plus, people who read your code won't have to scratch their heads wondering why you’re doing that little trick. Clear and simple!
答案2
得分: 1
UPDATE testtable SET ts = CURRENT_TIMESTAMP WHERE some_id = %s;
英文:
UPDATE testtable SET ts = CURRENT_TIMESTAMP WHERE some_id = %s;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论