Mysql通过添加或减去有限制的值来更新以前的值

Question

我有这个问题

UPDATE posts_flags
    INNER JOIN
    users ON (posts_flags.user_id = users.id )
SET 
    users.credibility = users.credibility + 100 /* or -100 */
WHERE posts_flags.content_id = ?;

是否可以将新值限制在一个范围内，例如 0 - 1000？

Answer 1

您可以添加一个CHECK 约束来避免任何超出允许范围的插入/更新。

CREATE TABLE post_flags
(
  credibility INT CHECK (credibility >= 0 AND credibility <= 1000),
);

使用 MAX 给值一个下限，MIN 如果你想在插入/更新时给它一个上限。

UPDATE posts_flags
    INNER JOIN
    users ON (posts_flags.user_id = users.id )
SET 
    users.credibility = MAX(MIN(users.credibility + 100, 1000), 0)
WHERE posts_flags.content_id = ?;

https://www.w3schools.com/sql/sql_check.asp
https://www.w3schools.com/sql/func_mysql_min.asp