Mysql PREPARE和EXECUTE语句不能在触发器调用的存储过程中使用.结果将是错误代码:1336.存储函数或触发器中不允许动态SQL.
有谁知道这方面的合理工作?
解决方法:
您无法从TRIGGER内部运行PREPARE / EXECUTE,但您可以从EVENT运行(如果您运行的是MySQL 5.5或更高版本).
以下是从EVENT运行PREPARE / EXECUTE的示例:
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 DETERMINISTIC
MODIFIES SQL DATA
proc: BEGIN
DECLARE b_not_found BOOL DEFAULT FALSE;
DECLARE i_id INT UNSIGNED;
DECLARE t_cmd TEXT;
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 ROW
BEGIN
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 evnt
ON SCHEDULE
EVERY 1 SECOND
DO
BEGIN
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,或者等待第二个左右触发它,您可以在每个可能导致触发TRIGGER的命令之后添加一个CALL proc().