mysql 使用 IF NOT NULL 作为事务的一部分

Question

我正在使用 MySQL 事务。如果初始 where myHistoryNum 结果(匹配名称+rowActive)不为 NULL，则继续事务。

我尝试了各种组合，但是在设置 myHistoryNum 时无法触发 insert 语句。

history | uuid | name  | rowActive
24      | abcd | Art   | 1          <<< is match, trigger an insert
999     | zxcv | Art   | 0          <<< no match, do not trigger insert

start transaction; 
select @myHistNum := max(history), @olduuid := uuid 
from mytable WHERE (name=art AND rowActive=1);

# START IS NOT NULL test
CASE WHEN @myHistNum IS NOT NULL THEN
set @histNum = @histNum  + 1;
INSERT INTO mytable 
.....
END;                                      <<<ALT, tried END AS;
commit;

也尝试过

IF @myHistNum IS NOT NULL
 THEN 
   .....
END IF;
commit;

Answer 1

如果我没记错的话，控制流语句(如 IF、WHEN)can only be used within stored programs in MySQL