存储过程中的MySQL PREPARE语句 [英] MySQL PREPARE statement in stored procedures

问题描述

我有这个sql文件:

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

# 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

我在做什么错了?

推荐答案

您只能准备和执行包含字符串文字或包含语句文本的 user 变量的sql.尝试

you can only prepare and execute sql that's a string literal or a user variable that contains the text of the statement. try

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 ;      

这篇关于存储过程中的MySQL PREPARE语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！