从触发器调用的存储过程中动态语句的解决方法

您不能运行

PREPARE

EXECUTE

TRIGGER

EVENT

这是从中运行

PREPARE

EXECUTE``EVENT

DROp TABLE IF EXISTS tbl1;DROP TABLE IF EXISTS tbl2;DROP TABLE IF EXISTS cmds;DROP PROCEDURE IF EXISTS proc;DROP TRIGGER IF EXISTS trig;CREATE TABLE tbl1 (i INT, v VARCHAr(255));CREATE TABLE tbl2 (i INT, v VARCHAr(255));CREATE TABLE cmds (    id INT UNSIGNED NOT NULL AUTO_INCREMENT,    done BOOL NOT NULL DEFAULT FALSE,    cmd TEXT,    PRIMARY KEY (id),    INDEX (done, id));DELIMITER //CREATE PROCEDURE proc()NOT DETERMINISTICMODIFIES SQL DATAproc: BEGIN    DECLARE b_not_found     BOOL DEFAULT FALSE;    DECLARE i_id INT UNSIGNED;    DECLARE t_cmdTEXT;    DECLARE v_lock_name     VARCHAr(255) DEFAULT 'proc_lock';    DECLARE cur CURSOR FOR        SELECT id, cmd FROM cmds WHERe NOT done ORDER BY id;    DECLARE ConTINUE HANDLER FOR NOT FOUND SET b_not_found = TRUE;    IF (NOT GET_LOCK(v_lock_name, 0)) THEN        LEAVE proc;    END IF;    OPEN cur;    loop1: LOOP        FETCH cur INTO i_id, t_cmd;        IF b_not_found THEN LEAVE loop1;        END IF;        SET @cmd = t_cmd;        PREPARE stmt FROM @cmd;        EXECUTE stmt;        DROp PREPARE stmt;        UPDATE cmds SET done = TRUE WHERe id = i_id;    END LOOP;    CLOSE cur;    DO RELEASE_LOCK(v_lock_name);END;//CREATE TRIGGER trig    BEFORE INSERT ON tbl1    FOR EACH ROWBEGIN    INSERT INTO cmds SET cmd =         CONCAt("INSERT INTO tbl2 SET i = ", -NEW.i, ", v = ", QUOTE(NEW.v));END;//DROP EVENT IF EXISTS evnt //CREATE EVENT evntON SCHEDULE EVERY 1 SEConDDOBEGIN    CALL proc();END;//DELIMITER ;SET GLOBAL event_scheduler = 1;

然后运行：

INSERT INTO tbl1 VALUES (UNIX_TIMESTAMP(), 'ex 1');DO SLEEP(2);INSERT INTO tbl1 VALUES (UNIX_TIMESTAMP(), 'ex 2');DO SLEEP(1);SELECT * FROM tbl2;

将产生以下输出：

+-------------+------+| i| v    |+-------------+------+| -1348550619 | ex 1 || -1348550621 | ex 2 |+-------------+------+2 rows in set (0.00 sec)

如果您不想使用

EVENT

CALL proc()

TRIGGER