在MySQL中限制字段的值？

Question

我正在开发一个使用MySQL数据库来存储数据的程序。我有一张桌子(这里简化了)：

+---------+------------------+------+-----+---------+----------------+
| Field   | Type             | Null | Key | Default | Extra          |
+---------+------------------+------+-----+---------+----------------+
| dataId  | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| someNum | tinyint(4)       | NO   |     | 0       |                |
+---------+------------------+------+-----+---------+----------------+

现在，我使用类似于此的查询更新表。

UPDATE table SET someNum=someNum+2 WHERE dataId=78225;

ID和数量someNum更改动态来自代码。

现在，我想要做的是将someNum的值限制在-3到3之间，特别是在那个UPDATE中。它不是必需的，我的软件可以处理超出这个范围的值，但是这个限制的数据会更清楚。如果someNum+2超过3，我只想把它设置为3。

数字MIN/MAX可以简化以下操作：

UPDATE table SET someNum=MAX(-3,MIN(3,someNum+2)) WHERE dataId=78225;

我查看了docs 这里，但是数字似乎没有MIN/MAX。MIN和MAX被发现为这里，但它们似乎不适合这样做。

在MySQL (而不是调用MySQL的代码)中实现这种限制的最佳方法(如果有的话)是什么？

Answer 1

第一种方法:使用LEAST()和GREATEST()

UPDATE t SET someNum=GREATEST(-3,LEAST(3,someNum+2)) WHERE dataId=78225;

这是最简单的方法，因为您将把所有的逻辑存储在一个UPDATE查询中。

第二种方法:创建触发器

DELIMITER //
CREATE TRIGGER catCheck BEFORE UPDATE ON t
    FOR EACH ROW
    BEGIN
        IF NEW.someNum<-3 THEN
           NEW.someNum=-3;
        END IF;

        IF NEW.someNum>3 THEN
           NEW.someNum=3;
        END IF;
    END;//
DELIMITER ;

您也可以将IF替换为CASE --但是我为-3和3留下了两个单独的约束。这里的好处是DBMS将自行处理数据，您将能够按原样传递数据，而不必担心范围。但是--也有缺点:在第一种情况下，您只需更改查询文本以调整所需的范围，而在第二种情况下，如果要更改该约束(因此，灵活性较小)，则必须再次重新创建触发器。

此外，您可能希望不仅检查UPDATE语句上的数据，而且也检查INSERT上的数据。

Answer 2

使用最伟大和最少代替MAX和MIN

你也可以用一个案例

update table
set someNum = CASE WHEN SomeNum +2 > 3 THEN 3 
                   WHEN SomeNum +2 < -3 THEN -3
                   ELSE someNum + 2
              END)

Answer 3

MySQL中的适当函数是greatest()和least()，而不是max()/min()。但是，我认为用case声明就更清楚了：

UPDATE table
    SET someNum = (case when someNum + 2 < -3 then -3
                        when someNum + 2 > 3 then 3
                        else someNum + 2
                   end) 
   WHERE dataId=78225;