mysql 触发器 动态sql,在存储过程中使用动态SQL的解决方法是什么

The Stored Procedure

DELIMITER $$

CREATE PROCEDURE `lms`.`leads_to_bak` ()

BEGIN

SET @table1 = (SELECT `tabler_name` FROM `sets` WHERE `on_off`=0 LIMIT 1);

SET @table2 = CONCAT(@table1, '_bak');

SET @SQL1 = CONCAT('INSERT INTO ',@table2, '(', (SELECT REPLACE(GROUP_CONCAT(COLUMN_NAME), 'lead_id,', '') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table2), ')', ' SELECT ', (SELECT REPLACE(GROUP_CONCAT(COLUMN_NAME), 'lead_id,', '') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table1), ' FROM ', @table1);

PREPARE stmt FROM @sql1;

EXECUTE stmt;

END$$

DELIMITER ;

The Trigger

DELIMITER $$

USE `lms`$$

CREATE TRIGGER `lms`.`after_insert_into_leads`

AFTER INSERT ON `sets` FOR EACH ROW

BEGIN

CALL lms.leads_to_bak();

END$$

DELIMITER ;

The problem

I get a Error Code: 1336. Dynamic SQL is not allowed in stored function or trigger error message when making an INSERT which by implication would execute the trigger and the stored procedure. I am assuming the problem is the Dynamic SQL here:

PREPARE stmt FROM @sql1;

EXECUTE stmt;

I've looked around and there is a thread on stackoverflow on the problem, but no answer. Does anyone have any suggestions for a plausible workaround?

解决方案

There is no good workaround for the absense of Dynamic SQL in MySQL functions, just klunky cludges. Some things still remain downright impossible to cludge, such as using a dynamically-calculated field name or table name in a SQL query. Yes, once in a while there is a need for doing this sort of thing!

And don't try cheat by putting the Dynamic SQL in a stored procedure and wrapping in a function or trigger, as the question poser tried - MySQL is too clever and will give you the usual obscure error message. Believe me, I have been around all the houses.

Coming from an Oracle PL/SQL and MS SQL Server background, I sorely miss the richness that PL/SQL and (to a small extent) T-SQL offers for writing procedural SQL.