笔记整理 之 ERROR 1336 (0A000)问题记录

且看执行存储过程报错：

mysql> DELIMITER //
mysql> CREATE PROCEDURE ps(IN table_name VARCHAR(200))
-> BEGIN
-> SET @newname = table_name;
-> SET @s = CONCAT('create table ',@newname,'(resourceid varchar(200),',@newname,' varchar(200),time date)');
-> PREPARE stmt FROM @s;
-> EXECUTE stmt;
-> DEALLOCATE PREPARE stmt;
-> END
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE zs_resourcegroup(group_name VARCHAR(20));
-> ;
-> //
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER //
mysql> CREATE TRIGGER ts AFTER INSERT ON zs_resourcegroup
-> FOR EACH ROW
-> BEGIN
-> CALL ps(new.group_name);
-> END
-> //
Query OK, 0 rows affected (0.00 sec)

mysql> insert into zs_resourcegroup select 'dba';
-> //
ERROR 1336 (0A000): Dynamic SQL is not allowed in stored function or triggermysql>
mysql>
mysql> call ps("cui");
-> //
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
-> //
+------------------+
| Tables_in_test |
+------------------+
| cui |
| t |
| zs_resourcegroup |
+------------------+
3 rows in set (0.00 sec)
ERROR 1336 (0A000): Dynamic SQL is not allowed in stored function or triggermysql>
mysql>
mysql> call ps("cui");
-> //
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
-> //
+------------------+
| Tables_in_test |
+------------------+
| cui |
| t |
| zs_resourcegroup |
+------------------+
3 rows in set (0.00 sec)

上网查了一下，发现触发器中不支持动态sql，单独执行存储过程还是可以支持的。