在存储过程中使用动态SQL的解决方法是什么 [英] What is the workaround for using dynamic SQL in a 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 ;

触发

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 ;

问题

我收到一个错误代码：1336。动态SQL不允许在存储函数或触发器错误消息，当做 INSERT 这意味着将执行触发器和存储过程。我假设问题是动态SQL在这里：

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;

我环顾四周，有一个线程对stackoverflow的问题，但没有回答。

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?

推荐答案

没有一个好的解决方法，缺乏动态SQL在MySQL功能，只是klunky cludges。有些事情仍然是不可能的，例如在SQL查询中使用动态计算的字段名或表名。是的，偶尔有需要做这样的事情！

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!

并且不要通过把动态SQL放在一个存储过程和包装一个函数或触发器，作为问题尝试 - MySQL是太聪明，会给你通常的模糊的错误消息。相信我，我已经在所有的房子。

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.

来自Oracle PL / SQL和MS SQL Server背景，我非常想念PL / SQL和在很小程度上）T-SQL提供编写过程SQL。

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.

这篇关于在存储过程中使用动态SQL的解决方法是什么的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！