php – 满足条件时的Sql-Exit CASE语句

你能试试吗？

架构&数据

DROP TABLE IF EXISTS `TIMER-BOARD1`;
CREATE TABLE `TIMER-BOARD1` (
  `ID` int(11) NOT NULL,`BET1` int(11) DEFAULT NULL,`BET2` int(11) DEFAULT NULL,`BET3` int(11) DEFAULT NULL,`BET4` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DELETE FROM `TIMER-BOARD1`;
INSERT INTO `TIMER-BOARD1` VALUES (10,NULL,NULL),(10,32,1,2,3,4);

初始数据

SELECT * FROM `TIMER-BOARD1`;
+----+------+------+------+------+
| ID | BET1 | BET2 | BET3 | BET4 |
+----+------+------+------+------+
| 10 | NULL | NULL | NULL | NULL |
| 10 |   32 | NULL | NULL | NULL |
| 10 |    1 |    2 | NULL | NULL |
| 10 |    1 |    2 |    3 | NULL |
| 10 |    1 |    2 |    3 |    4 |
+----+------+------+------+------+

UPDATE

UPDATE `TIMER-BOARD1`
set 
    ID = @UPDATED := 10,-- user defined variable which keep track WHETHERE CHANGED OR NOT
    BET1 = case when BET1 IS NULL THEN @UPDATED:= 32 else BET1 end,BET2 = case when @UPDATED = 10 AND BET2 IS NULL THEN @UPDATED:=32 else BET2 end,BET3 = case when @UPDATED = 10 AND BET3 IS NULL THEN @UPDATED:=32 else BET3 end,BET4 = case when @UPDATED = 10 AND BET4 IS NULL THEN @UPDATED:=32 else BET4 end
WHERE ID = 10;

更新的数据

SELECT * FROM `TIMER-BOARD1`;
mysql> SELECT * FROM `TIMER-BOARD1`;
+----+------+------+------+------+
| ID | BET1 | BET2 | BET3 | BET4 |
+----+------+------+------+------+
| 10 |   32 | NULL | NULL | NULL |
| 10 |   32 |   32 | NULL | NULL |
| 10 |    1 |    2 |   32 | NULL |
| 10 |    1 |    2 |    3 |   32 |
| 10 |    1 |    2 |    3 |    4 |
+----+------+------+------+------+

问题

当你想要更新10时仍然有问题.

更新

1.使用一些string_column

我认为你有两个选择

>使用字符串列而不是ID列

>优点：可以使用一个UPDATE实现
>缺点：如果没有更多的字符串列,则无法使用

>为每行执行UPDATE

> pros：即使没有更多的字符串列也可以使用
> cons：执行几次UPDATE,如果没有PK则不能使用.

我想知道你是否有像string_col这样的字符串列,如下所示.

架构&数据

DROP TABLE IF EXISTS tab;

CREATE TABLE tab (
  `ID` int(11) NOT NULL,`BET1` VARCHAR(100) DEFAULT NULL,`BET2` VARCHAR(100) DEFAULT NULL,`BET3` VARCHAR(100) DEFAULT NULL,`BET4` VARCHAR(100) DEFAULT NULL,`string_col` VARCHAR(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DELETE FROM tab;
INSERT INTO tab VALUES
    (10,'A'),'Alice','B'),'Bob','C'),'Charlie','D'),'Dave','E');

SELECT * FROM tab;
+----+-------+------+---------+------+------------+
| ID | BET1  | BET2 | BET3    | BET4 | string_col |
+----+-------+------+---------+------+------------+
| 10 | NULL  | NULL | NULL    | NULL | A          |
| 10 | Alice | NULL | NULL    | NULL | B          |
| 10 | Alice | Bob  | NULL    | NULL | C          |
| 10 | Alice | Bob  | Charlie | NULL | D          |
| 10 | Alice | Bob  | Charlie | Dave | E          |
+----+-------+------+---------+------+------------+

UPDATE

UPDATE tab
set 
    string_col = @ORIG_STRING_COL := (@UPDATED := string_col),BET1 = IF(BET1 IS NULL,@UPDATED := 'Susan',BET1),BET2 = IF(@UPDATED != 'Susan' AND BET2 IS NULL,BET2),BET3 = IF(@UPDATED != 'Susan' AND BET3 IS NULL,BET3),BET4 = IF(@UPDATED != 'Susan' AND BET4 IS NULL,BET4),string_col = @ORIG_STRING_COL
WHERE ID = 10;

结果

SELECT * FROM tab;
+----+-------+-------+---------+-------+------------+
| ID | BET1  | BET2  | BET3    | BET4  | string_col |
+----+-------+-------+---------+-------+------------+
| 10 | Susan | NULL  | NULL    | NULL  | A          |
| 10 | Alice | Susan | NULL    | NULL  | B          |
| 10 | Alice | Bob   | Susan   | NULL  | C          |
| 10 | Alice | Bob   | Charlie | Susan | D          |
| 10 | Alice | Bob   | Charlie | Dave  | E          |
+----+-------+-------+---------+-------+------------+

2.为每条记录执行一次UPDATE

使用以下伪代码,您可以实现它.

$res = mysqli_query(SELECT pk_column FROM tab WHERE ID = 10);

while ($row = mysqli_fetch_assoc($res))
{
    $pk_col = $row['pk_column'];

    mysqli_query("SET @UPDATED := ''");

    mysqli_query("UPDATE tab
        SET 
            BET1 = IF(BET1 IS NULL,BET2 = IF(@UPDATED = '' AND BET2 IS NULL,BET3 = IF(@UPDATED = '' AND BET3 IS NULL,BET4 = IF(@UPDATED = '' AND BET4 IS NULL,BET4)
        WHERE pk_col = $pk_column
    ");

}

前面的代码可以实现客户端或存储过程.