mysql - 如何在mysql触发器中按条件跳过行？

Question

如何在游标循环中跳过一行？

我提到的是继续；在上面的片段中。我想跳过 if name='siva'。

CREATE TRIGGER `vdata_after_insert` AFTER INSERT ON `vdata`
BEGIN

declare v_name varchar(100);
declare v_address varchar(100);
declare v_city varchar(50);
declare v_IdentityNO varchar(20)
declare v_clientNo int


declare cur1 cursor for 
        select name,Address,City,IdentityNO,clientNo
        from temptable;
declare continue handler for not found set done=1;

    set done = 0;
    open cur1;
    igmLoop: loop
        fetch cur1 into v_name,v_Address,v_City,v_IdentityNO,v_clientNo;
        if done = 1 then leave igmLoop; end if;
        if v_name = 'siva' then **CONTINUE**;
        insert into audit(name, data) values(v_name, now())
    end loop igmLoop;
    close cur1;
END

Answer 1

来自MySQL documentation说，使用游标时不可能跳过行:

MySQL supports cursors inside stored programs. The syntax is as in embedded SQL. Cursors have these properties:

Asensitive: The server may or may not make a copy of its result table
Read only: Not updatable
Nonscrollable: Can be traversed only in one direction and cannot skip rows

但是，如果您仔细观察触发器，您会发现还有另一种方法可以继续这里。我认为您可以重新表述您的逻辑，仅在 v_name 不是 'siva' 时才执行 INSERT:

if v_name <> 'siva' then
    insert into audit(name, data) values(v_name, now())
end if;
-- if the name is 'siva' then just flow to the next row fetched from the cursor

即使您的实际代码比这大得多，您也总是可以表述 IF 语句，以便它仅执行所有游标迭代的一部分(如果某些情况)条件为真。