在存储过程中使用动态 SQL 的解决方法是什么

问题描述

存储过程

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.在生成一个 INSERT 时，存储函数或触发器中不允许使用动态 SQL 错误消息，暗示会执行触发器和存储过程.我假设问题是这里的动态 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;

我环顾四周，有一个线程 on 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?

推荐答案

对于 MySQL 函数中缺少动态 SQL 没有好的解决方法，只有笨拙的障碍.有些事情仍然完全无法混淆，例如在 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 的解决方法是什么的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！