mysql - SQL-将值增加1时设置最大数

我希望增量difficulty为1，但difficulty的值不应超过3。我该怎么做？
这是我的问题

$query=mysqli_query($con,"UPDATE ticket
                          SET status = 'open',
                          difficulty = difficulty + 1
                          WHERE ticketid = '$_POST[ticketid]'")

最佳答案

架构

create table ticket
(   ticketid int auto_increment primary key,
    status varchar(20) not null,
    difficulty int not null
);
insert ticket(status,difficulty) values (0,0),(0,0);    -- 2 rows

多次运行此查询：

update ticket
set status ='open',
difficulty=least(difficulty+1,3)
where ticketid=2;

现在看看数据

select * from ticket;
+----------+--------+------------+
| ticketid | status | difficulty |
+----------+--------+------------+
|        1 | 0      |          0 |
|        2 | open   |          3 |
+----------+--------+------------+

见Mysql Comparison functions

关于mysql - SQL-将值增加1时设置最大数，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/33879587/