mysql更新语句中的用户变量

时间:2021-07-14 23:01:18

Let t be a mysql table and n be an integer column in it. I would be interested if the following query can be modified so that the expression n - 10 is calculated only once.

设t是mysql表,n是其中的整数列。如果可以修改以下查询,我感兴趣的是,表达式n - 10只计算一次。

UPDATE t SET n = if(n - 10 < 1, 1, n - 10) WHERE n = 5;

The query does not make sense, I know, but I need to use the same pattern in the real application where values 10 and 5 would be php variables.

我知道,查询没有意义,但我需要在实际应用程序中使用相同的模式,其中值10和5是php变量。

2 个解决方案

#1


3  

Allright, i have finally found the right syntax. The parentheses around @tmp := n - 10 are crucial.

好吧,我终于找到了正确的语法。@tmp:= n - 10的括号是关键。

UPDATE t SET n = if((@tmp := n - 10) < 1, 1, @tmp) WHERE n = 5;

#2


0  

You can just do such logic in php:

你可以用php做这样的逻辑:

$value = $n - 10;
$value = $value < 1 ? 1 : $value;
$sql = "UPDATE t SET n = $value WHERE n = $n";

#1


3  

Allright, i have finally found the right syntax. The parentheses around @tmp := n - 10 are crucial.

好吧,我终于找到了正确的语法。@tmp:= n - 10的括号是关键。

UPDATE t SET n = if((@tmp := n - 10) < 1, 1, @tmp) WHERE n = 5;

#2


0  

You can just do such logic in php:

你可以用php做这样的逻辑:

$value = $n - 10;
$value = $value < 1 ? 1 : $value;
$sql = "UPDATE t SET n = $value WHERE n = $n";