在存储过程中使用动态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:

我得到一个错误代码：1336。在进行INSERT时，不允许在存储函数或触发错误消息中使用动态SQL，暗示将执行触发器和存储过程。我假设问题是这里的动态SQL：

PREPARE stmt FROM @sql1;
EXECUTE stmt;

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

我环顾四周，问题上有一个关于*的线程，但没有答案。有没有人对合理的解决方法有任何建议？

2 个解决方案

#1

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!

没有很好的解决方法可以解决MySQL函数中缺少动态SQL的问题，只有klunky cludges。有些事情仍然无法进行修改，例如在SQL查询中使用动态计算的字段名称或表名。是的，偶尔需要做这种事情！

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.

并且不要通过将动态SQL放在存储过程中并包装在函数或触发器中来尝试作弊，正如问题所提出的那样--MySQL太聪明并且会给你通常的模糊错误消息。相信我，我一直在所有的房子周围。

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.

来自Oracle PL / SQL和MS SQL Server背景，我非常怀念PL / SQL和（在很小程度上）T-SQL为编写过程SQL提供的丰富性。

#2

Within the procedure definition, you need to store all your IN/OUT variables.

在过程定义中，您需要存储所有IN / OUT变量。

Change:

更改：

CREATE PROCEDURE `lms`.`leads_to_bak` ()

to:

至：

CREATE PROCEDURE `lms`.`leads_to_bak` (
    IN table1 varchar(32),
    IN table2 varchar(32),
)

Then call doing this:

然后调用这样做：

CALL `lms`.`leads_to_bak`('table1', 'table2')

replacing the strings with your own.

用你自己的字符串替换字符串。

The purpose of using stored procedures is to prevent SQL injection using strictly typed data. You don't technically need to prepare it in the stored procedure if you ONLY send strictly typed input variables in the parameter list.

使用存储过程的目的是使用严格类型化的数据来防止SQL注入。如果只在参数列表中发送严格类型的输入变量，则在技术上不需要在存储过程中进行准备。

This way, you handle the string operations prior to the stored procedure call. Keep your stored procs skinny!

这样，您可以在存储过程调用之前处理字符串操作。保持你的存储过程瘦！

Here's an example of one of my stored procedures:

这是我的一个存储过程的示例：

DELIMITER ;
DROP PROCEDURE IF EXISTS `save_player`;
DELIMITER //

CREATE PROCEDURE `save_player` (
IN uid int(15) UNSIGNED,
IN email varchar(100),
IN name varchar(100),
IN passwd char(96),
IN state ENUM('active','suspended','deleted'),
IN user_role ENUM('gamemaster','moderator','player'),
IN locale ENUM('en','fr'),
IN lvl tinyint(1),
IN hp bigint(20),
IN reborn tinyint(1),
IN cross_ref varchar(12),
IN email_verified tinyint(1),
OUT new_id  int(15) UNSIGNED
)
BEGIN
   DECLARE date_deleted timestamp DEFAULT NULL;
   IF uid > 0 AND EXISTS (SELECT id FROM user WHERE `id`= uid) THEN
      IF state = 'deleted' THEN
        SET date_deleted = CURRENT_TIMESTAMP;
      END IF ;
      UPDATE `user` SET
        `email` = email,
        `name` = name,
        `passwd` = passwd,
        `state` = state,
        `user_role` = user_role,
        `locale` = locale,
        `lvl` = lvl,
        `hp` = hp,
        `reborn` = reborn,
        `cross_ref` = cross_ref,
        `email_verified` = email_verified,
        `date_deleted` = date_deleted
      WHERE `id` = uid;
      SET new_id = uid;
   ELSE
      INSERT INTO user (`email`, `name`, `passwd`, `state`, `user_role`, `locale`, `lvl`, `hp`, `reborn`, `cross_ref`, `email_verified`, `date_created`)
             VALUES (email, name, passwd, state, user_role, locale, lvl, hp, reborn, cross_ref, email_verified, NOW());
      SELECT LAST_INSERT_ID()  INTO new_id;
   END IF;
 END //
DELIMITER ;

#1