MySQL MAX()函数比较更新中的数值? [英] MySQL MAX() function to compare numeric values in an update?

问题描述

当更新一行时，我希望有一个内置检查来做一些边界检查.大多数语言都有MAX()函数来返回传递的最大参数，但是MySQL似乎将MAX()用于其他目的.例如:

When updating a row, I want to have a built-in check to do some bounds checking. Most languages have a MAX() function to return the maximum of the arguments passed, but MySQL seems to use MAX() for something else. For example:

UPDATE person SET dollars = MAX(0, dollars-20) WHERE id=1

我想从人员id 1减去20美元，但我不希望美元一直用负值表示，所以我想与0进行内置比较.这行得通吗?还是有另一种方法?谢谢！

I want to subtract 20 dollars from person id 1, but I don't want dollars to ever be represented by a negative value, so I want a built-in comparison with 0. Does this work? Or is there another way? Thanks!

推荐答案

MySQL支持称为

MySQL supports a function called GREATEST(). It returns the largest value among a list of its arguments.

UPDATE person SET dollars = GREATEST(0, dollars-20) WHERE id=1

这不是ANSI SQL中的标准功能，因此不要指望它可以在其他品牌的SQL数据库中使用.如果需要独立于供应商的解决方案，请使用其他人建议的CASE语法.但是，如果您只需要使用MySQL，则此功能会更简洁.

This isn't a standard function in ANSI SQL, so don't count on it being available in other brands of SQL database. If you need a vendor-independent solution, use the CASE syntax suggested by others. But if all you need to use is MySQL, this function is more concise.

这篇关于MySQL MAX()函数比较更新中的数值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！