MySQL：仅在满足条件时更新字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14893355/
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 a field only if condition is met
提问by Caballero
Is it possible to do an UPDATE query in MySQL which updates field value only if certain condition is met? Something like this:
是否可以在 MySQL 中执行 UPDATE 查询，仅在满足某些条件时才更新字段值？像这样的东西：
UPDATE test
SET
CASE
WHEN true
THEN field = 1
END
WHERE id = 123
In other words:
换句话说：
UPDATE test
SET
something = 1, /*field that always gets updated*/
CASE
WHEN true
THEN field = 1 /*field that should only get updated when condition is met*/
END
WHERE id = 123
What is the proper way to do this?
这样做的正确方法是什么？
回答by fedorqui 'SO stop harming'
Yes!
是的！
Here you have another example:
这里还有另一个例子：
UPDATE prices
SET final_price= CASE
WHEN currency=1 THEN 0.81*final_price
ELSE final_price
END
This works because MySQL doesn't update the row, if there is no change, as mentioned in docs:
这是有效的，因为如果没有更改，MySQL 不会更新该行，如文档中所述：
If you set a column to the value it currently has, MySQL notices this and does not update it.
如果您将一列设置为它当前拥有的值，MySQL 会注意到这一点并且不会更新它。
回答by Dominique
Another solution which, in my opinion, is easier to read would be:
在我看来，另一个更容易阅读的解决方案是：
UPDATE test
SET something = 1, field = IF(condition is true, 1, field)
WHERE id = 123
What this does is set 'field' to 1 (like OP used as example) if the condition is met and use the current value of 'field' if not met. Using the previous value is the same as not changing, so there you go.
如果满足条件，则将“字段”设置为 1（如用作示例的 OP），如果不满足则使用“字段”的当前值。使用先前的值与不更改相同，所以你去。
回答by Hamlet Hakobyan
Try this:
尝试这个：
UPDATE test
SET
field = 1
WHERE id = 123 and condition
回答by krishna
Another solution we can use MySQL IF()conditional function :
我们可以使用 MySQLIF()条件函数的另一种解决方案：
UPDATE test
SET field = IF(something == 1{CONDITION}, 1 {NEW VALUE}, field)
WHERE `id` = 5
回答by phoenix
Another variation:
另一种变体：
UPDATE test
SET field = IF ( {condition}, {new value}, field )
WHERE id = 123
This will update the fieldwith {new value}only if {condition}is met
这将更新field与{new value}只有{condition}满足
回答by Oldhuntersblood
found the solution with AND condition:
找到了 AND 条件的解决方案：
$trainstrength = "UPDATE user_character SET strength_trains = strength_trains + 1, trained_strength = trained_strength +1, character_gold = character_gold - $gold_to_next_strength WHERE ID = $currentUser AND character_gold > $gold_to_next_strength";
