mysql 触发器 sql_mysql动态SQL的运用 （trigger、function、procedure）

mysql中，当你在trigger、function中编写动态的sql时，编译时就会出现“Error 1336: Dynamic

SQL is not allowed in stored function or trigger“

trigger：触发器，在执行动作前后触发

function：方法，函数，可以在SQL、存储过程中调用

procedure：存储过程，可以编写比较复杂的逻辑

动态的SQL语句：就是单纯的SQL语句中，含有变量。

出现上述异常的原因就是:

1、 在function或者是trigger里面含有并执行了动态的SQL语句

2、 在function或者是trigger里调用了含有动态SQL语句的存储过程

=> 若单独在存储过程中执行动态的SQL语句是不会报错的，是允许的。

目前最新的mysql版本来说，function、trigger还是不支持动态sql语句，你硬要将动态语句写入trigger、function的话，我建议可以考虑写在存储过程中，然后用程序去调用存储过程。

我之所以写这文档，主要是我遇到了这问题，最后我就只能写存储过程了，具体demo如下：

CREATE DEFINER=`luo`@`%`

PROCEDURE `P_INSERT_ORIGINAL`(IN gatewayID

int,

in customerID long, in version varchar(40))

BEGIN

DECLARE isexist int; -- 标志位

DECLARE calcTable varchar(50);

DECLARE commTable varchar(50);

DECLARE STMT varchar(2000);

DECLARE existStmt varchar(2000);

DECLARE stmt2 varchar(2000);

DECLARE stmtdel varchar(2000);

DECLARE count INT; -- 计数

DECLARE recvDateShort date; -- "YYYY-MM-DD"

SET recvDateShort = recvDate;

SELECT

c.id into commID

FROM

base_proprietor p,

base_community c

WHERE and p.communityName =

concat(c.id,'：',c.name) ;

if commID > 0 THEN

SET calcTable =

concat(commID,'_calc_meterdata'); SET commTable =

concat(commID,'_comm_meterdata');

-- 定义占位符的传值量

set @gatewayID =

gatewayID;

set @customerID =

customerID;

set @version = version;

-- select customerID;

SET @existStmt :=

CONCAT('select count(*) into @count from ', calcTable,

' where

customerID = ? ');

PREPARE existStmt FROM

@existStmt; EXECUTE existStmt using

@customerID;

select @count;

if @count > 0 then

select @count

as feifei;

set @stmt2 :=

concat("insert into ", commTable, " (gatewayID,

customerID, version) select

gatewayID, customerID,

version

from ", calcTable,

"

where customerID = ? ");

PREPARE stmt2

FROM @stmt2; EXECUTE stmt2

using @customerID;

set @stmtdel

:= concat("delete from ", calcTable,

"

where customerID = ? ");

prepare

stmtdel from @stmtdel;

EXECUTE

stmtdel using @customerID; end if; SET @STMT = concat("INSERT INTO

", calcTable,

"(gatewayID, customerID,

version)

VALUES (?, ?,

?);");

PREPARE STMT

FROM @STMT; -- 预取

EXECUTE STMT USING @gatewayID,

@customerID, @version;

else

INSERT INTO

getdata_originalmeterdata

(gatewayID, customerID,

version)

VALUES

(gatewayID, customerID,

version);

end if;

END