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

您正在寻找“UPDATE first NULL column”.我认为这可以使用MySQL用户变量来实现.

你能试试吗？

架构&数据

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, NULL, NULL),

(10, 32, NULL, NULL, NULL), (10, 1, 2, NULL, NULL),

(10, 1, 2, 3, NULL), (10, 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, 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');

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, @UPDATED := 'Susan', BET2),

BET3 = IF(@UPDATED != 'Susan' AND BET3 IS NULL, @UPDATED := 'Susan', BET3),

BET4 = IF(@UPDATED != 'Susan' AND BET4 IS NULL, @UPDATED := 'Susan', 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, @UPDATED := 'Susan', BET1),

BET2 = IF(@UPDATED = '' AND BET2 IS NULL, @UPDATED := 'Susan', BET2),

BET3 = IF(@UPDATED = '' AND BET3 IS NULL, @UPDATED := 'Susan', BET3),

BET4 = IF(@UPDATED = '' AND BET4 IS NULL, @UPDATED := 'Susan', BET4)

WHERE pk_col = $pk_column

");

}

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