php - MySql "IN"不影响重复值

Question

我的mysql表如下:

+----+---------+---------+-------+
| id | column1 | column2 | count |
+----+---------+---------+-------+
|  1 |      23 |      50 |    10 |
+----+---------+---------+-------+

查询如下:

UPDATE `count_table` 
SET `count`=`count`-1 
WHERE (`column1`, `column2`) 
IN ((23,50),(23,50))

当我执行查询时，我希望“计数”列的值为 8，但结果为 9。

是否有任何替代查询可以按预期影响查询。

注意:我不想更改像 count=count-2 这样的语句

下面是一段代码

// this array is program generated ,it can hold max 300 values
$a=["(23,50)","(23,50)","(24,13)","(30,23)","(32,52)"];

$in_values = "(";

foreach ($a as $value) 
{
    $in_values .= $value . ",";
}

$in_values = substr($in_values, 0, -1) . ")";

$update_query = "UPDATE `count_table` SET `count`=`count`-1  WHERE (`column1`, `column2`)  IN  " . $in_values;

Answer 1

试试这个，

UPDATE count_table SET count=count-1 WHERE coloumn1 in (23,50) OR coloumn2 IN (23,50)

试一试，这应该有效。

编辑

你可以这样试试，

$a = ["23,50", "23,50", "24,13", "30,23", "32,52"];
$column1 = $column2 = [];
foreach ($a as $v){
    $temp = explode(',',$v);
    $column1[] = $temp[0];
    $column2[] = $temp[1];
}
$update_query = "UPDATE `count_table` SET `count`=`count`-1    
WHERE coloumn1` IN  (".implode(',', $column1).")   
OR coloumn2` IN  (".implode(',', $column2).")";