MySQL 减少mysql中的值但不是负数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16277339/
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
Decrement value in mysql but not negative
提问by juergen d
I want to decrement a value when user delete it in php and mysql. I want to check not to go below than 0. If value is 0 then do not decrement.
当用户在 php 和 mysql 中删除它时，我想递减一个值。我想检查不低于 0。如果值为 0，则不要减少。
mysql_query("UPDATE table SET field = field - 1 WHERE id = $number");
If field is 0then do not do anything
如果字段为0则什么都不做
回答by juergen d
Add another condition to update only if the fieldis greater 0
添加另一个条件以仅在field更大时更新0
UPDATE table
SET field = field - 1
WHERE id = $number
and field > 0
回答by Till Helge
You could prevent the new value to drop below zero by using GREATEST(). If the value drops below zero, zero will always be greater than your calculated value, thus preventing any value below zero to be used.
您可以使用 防止新值降至零以下GREATEST()。如果该值低于零，零将始终大于您的计算值，从而防止使用任何低于零的值。
UPDATE table
SET field = GREATEST(0, field - 1)
WHERE id = $number
And on a side note: Please don't use mysql_*functions any more. They are deprecated and will eventually be removed from PHP. Use PDOor MySQLiinstead.
回答by Eelco Luurtsema
The option using GREATEST will not work in newer MySQL versions, and the accepted answer can be unuseful if you want to update multiple fields instead of one. My solution for this problem is using IF:
使用 GREATEST 的选项在较新的 MySQL 版本中不起作用，如果您想更新多个字段而不是一个，则接受的答案可能没有用。我对这个问题的解决方案是使用 IF：
UPDATE table
SET field = IF(field > 0, field - 1, 0)
WHERE id = $number
回答by Niket
UPDATE table SET field = case when (field - 1) >0 then (field - 1)
else field end
WHERE id = $number
回答by Dev
if the field is int unsigned, below is the best:
如果该字段是 int unsigned，下面是最好的：
UPDATE table
SET field = field - 1
WHERE id = $number
and field > 0
# or
UPDATE table
SET field = IF(field > 0, field - 1, 0)
WHERE id = $number
回答by Aatif
UPDATE `table_name` SET field = field-1 WHERE `id` = '".$id."' AND field > 0
Note: field's data-type should be an INTEGER.
注意：字段的数据类型应该是整数。
