存储过程中的MySQL PREPARE语句

Question

I have this sql file:

USE mydb;

DROP PROCEDURE IF EXISTS execSql;
DELIMITER //
CREATE PROCEDURE execSql (
                     IN sqlq VARCHAR(5000)
                      ) COMMENT 'Executes the statement'
BEGIN
  PREPARE stmt FROM sqlq;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END //
DELIMITER ;          

当我尝试使用以下命令运行：

# cat file.sql | mysql -p

我会得到以下错误：

ERROR 1064 (42000) at line 6: You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to use near 'sqlq;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
  END' at line 5

我做错了什么？

- Lucio Crusca

Answer 1

你只能准备和执行SQL语句，这些语句是 字符串文字 或包含语句文本的用户定义变量。

尝试这样做：

USE mydb;

DROP PROCEDURE IF EXISTS execSql;
DELIMITER //

CREATE PROCEDURE execSql (IN sqlq VARCHAR(5000)) COMMENT 'Executes the statement'
BEGIN
  SET @sqlv = sqlq;
  PREPARE stmt FROM @sqlv;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END //

DELIMITER ;      

- mpf

---

请注意，在游标中，FETCH [foo] INTO y，在游标内部，您必须创建一个新的@yValue = y并将其用于预处理语句。 - Russell Fox

Answer 2

你可以使用以下的CONCATENATE、PREPARE和EXECUTE语句：

CREATE DEFINER=`products`@`localhost` PROCEDURE `generateMeritList`(
   IN `mastercategory_id` INT(11), 
   IN `masterschools_id` INT(11)
) NO SQL
BEGIN

  DECLARE total INT DEFAULT 0;
  DECLARE conditions varchar(255) DEFAULT ''; 
  DECLARE finalQuery varchar(60000) DEFAULT '';

  IF mastercategory_id > 0 THEN
    SET conditions =  CONCAT(' AND app.masterschools_id = ', mastercategory_id);
  END IF;

  SET @finalQuery = CONCAT(
    "SELECT *
    FROM applications app
    INNER JOIN masterschools school ON school.id = app.masterschools_id
    WHERE app.status = 'active' ", conditions, " LIMIT ", total); 

   PREPARE stmt FROM @finalQuery;
   EXECUTE stmt;
   DEALLOCATE PREPARE stmt;

END

- Dinesh Vaitage

---

感谢您的努力，但除了我已经在5年前接受了mpf的答案这一事实外，我并不明白您的帖子是如何回答我的问题的。 - Lucio Crusca

@Dinesh Vaitage - 很抱歉，这并不能防止SQL注入攻击。在语句的准备过程中没有参数可用。换句话说，@conditions在哪里？ - Nandostyle