MySQL触发器怎么变相使用动态SQL

MySQL触发器动态sql： CREATE TRIGGER trigger1 after INSERT ON cdr FOR EACH ROW BEGIN DECLARE id BIGINTDECLARE vname VARCHAR(200)DECLARE destination VARCHAR(200) DEFAULT 'null'DECLARE rows1 BIGINT DEFAULT 0DECLARE sql1 VARCHAR(2000)SELECT v.vendorid,v.name, ve.DestinationID INTO id, vname, destination FROM (select name, v.VendorID from vendor v natural join vendorendpoints where endpoint=NEW.Field27) v NATURAL JOIN (SELECT * FROM vendorextraction order by length desc) ve WHERE substr(NEW.Field8 , 1 ,ve.length)=substr(CONCAT(ve.Prefix,ve.OperatorList),1,ve.length) limit 0,1SET @@sql_mode='ansi'SET sql1 =CONCAT('insert into `',CONCAT(vname,id),'` values(',id,',"',vname,'","',NEW.Field5,'","',NEW.Field35,'","',NEW.Field12,'","',NEW.Field8,'","',NEW.Field0,'")')IF (id >0) THEN call testing(sql1)ELSE SET sql1 =CONCAT('insert into `unknown` values(',id,',"',vname,'","',NEW.Field5,'","',NEW.Field35,'","',NEW.Field12,'","',NEW.Field8... MySQL触发器动态sql：

CREATE TRIGGER trigger1 after INSERT ON cdr

FOR EACH ROW

BEGIN

DECLARE id BIGINT

DECLARE vname VARCHAR(200)

DECLARE destination VARCHAR(200) DEFAULT 'null'

DECLARE rows1 BIGINT DEFAULT 0

DECLARE sql1 VARCHAR(2000)

SELECT v.vendorid,v.name, ve.DestinationID INTO id, vname, destination

FROM (select name, v.VendorID from vendor v natural join vendorendpoints where endpoint=NEW.Field27) v NATURAL JOIN (SELECT * FROM vendorextraction order by length desc) ve

WHERE

substr(NEW.Field8 , 1 ,ve.length)=substr(CONCAT(ve.Prefix,ve.OperatorList),1,ve.length) limit 0,1

SET @@sql_mode='ansi'

SET sql1 =CONCAT('insert into `',CONCAT(vname,id),'` values(',id,',"',vname,'","',NEW.Field5,'","',NEW.Field35,'","',NEW.Field12,'","',NEW.Field8,'","',NEW.Field0,'")')

IF (id >0) THEN

call testing(sql1)

ELSE

SET sql1 =CONCAT('insert into `unknown` values(',id,',"',vname,'","',NEW.Field5,'","',NEW.Field35,'","',NEW.Field12,'","',NEW.Field8,'","',NEW.Field0,'")')

call testing(sql1)

END IF

MySQL的存储过程在5.0后的版本中支持了动态sql语句。也就是说我们可以通过传的参数不同拼接查询不同的表或执行不同的语句。

如：需要根据不同的游戏ID去增加 TGameData_ID 表中玩家对应的金币数。

如：需要根据不同的游戏ID去 TGameData_ID 表中查询玩家对应的金币数，数量足够减去，数量不够不减。

---