存储过程中的 MySQL PREPARE 语句

2021-12-20 00:00:00 mysql prepare stored-procedures

我有这个 sql 文件:

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

# 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

我做错了什么?

推荐答案

您只能准备和执行 字符串文字 或 用户定义的变量，包含语句的文本.

You can only prepare and execute SQL that's a string literal or a user-defined variable that contains the text of the statement.

试试这个:

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 ;

相关文章