仅当值已更改时，才如何插入数据库? [英] How do I insert into a database only if a value has changed?

问题描述

我需要更新(替换)MySQL数据库中的字段，但前提是它们已更改.

I need to update (replace) fields in a MySQL database, but only if they have changed.

该表包含一个ID，文本字段和更改日期.用户根据ID的更改日期按ID查询数据.即，如果日期在用户查询数据的最后一次日期之前，那么他就不想要它.

The table contains an ID, text field and date changed. Users query the data by ID based on the date it was changed. i.e. If the date is before the last the time user queried the data, he doesn't want it.

仅当文本字段与具有相同ID的现有文本字段不同时，我才想更改数据库中的日期.

I would like to change the date in the database only if the text field is different from the existing text field with the same id.

我可以查询数据，比较结果，并且仅在新数据不同时才回发，但这会产生大量开销，并且数据库连接速度很慢，因此我正在寻找一种方法来单个查询.

I could query the data, compare the result and only post back if the new data is different, but this is a lot of overhead and the database connection is rather slow, so I am looking for a way to do this in a single query.

谢谢.

推荐答案

您可以在更新查询中包含CASE语句，该语句将有条件地设置日期字段，如下所示:

You can include a CASE statement in your update query that will set the date field conditionally, like so:

UPDATE MyTable
SET textfield = @newValue,
datefield = (CASE WHEN textfield <> @newValue THEN NOW() ELSE datefield END);

如果未更改textfield的值，此查询会将datefield设置为已经包含的相同值.

This query "sets" the datefield to the same value it already contains if the value of textfield hasn't been changed.

这篇关于仅当值已更改时，才如何插入数据库?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！