mysql - mysql中需要重新准备prepared statement

Question

我正在尝试从 View 访问数据并将其插入存储过程内的临时表中，下面是存储过程。当我尝试执行 sp 时，出现“准备好的语句需要重新准备”错误。

有人可以建议如何修复 mysql 中的这个错误。

程序:

DROP PROCEDURE IF EXISTS getFilteredData;

CREATE PROCEDURE getFilteredData()
BEGIN

DECLARE sql2 VARCHAR(5000);
DROP TEMPORARY TABLE IF EXISTS TempTbl;
CREATE TEMPORARY TABLE TempTbl
(
ID VARCHAR(50),
Name VARCHAR(500),
Status INT
);
SET sql2 = 'INSERT INTO TempTbl Select ID,Name,Id as Status from      v_release;';

SET @SWV_Stmt = sql2;
PREPARE SWT_Stmt FROM @SWV_Stmt;   
EXECUTE SWT_Stmt;
DEALLOCATE PREPARE SWT_Stmt;

SELECT * FROM TempTbl;
END;

Answer 1

我无法重现您报告的问题。

mysql> SELECT VERSION();
+-----------------+
| VERSION()       |
+-----------------+
| 5.5.22-0ubuntu1 |
+-----------------+
1 row in set (0.00 sec)

mysql> DELIMITER //

mysql> DROP TABLE IF EXISTS `release`//
Query OK, 0 rows affected (0.00 sec)

mysql> DROP VIEW IF EXISTS `v_release`//
Query OK, 0 rows affected (0.00 sec)

mysql> DROP PROCEDURE IF EXISTS `getFilteredData`//
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `release` (
    ->   `id` VARCHAR(50),
    ->   `name` VARCHAR(500),
    ->   `status` INT
    -> )//
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE VIEW `v_release` AS
    -> SELECT `id`, `name`, `status`
    -> FROM `release`//
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `release`
    ->   (`id`, `name`, `status`)
    -> VALUES
    ->   ('1', 'NAME1', 1),
    ->   ('2', 'NAME2', 2),
    ->   ('3', 'NAME1', 3)//
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> CREATE PROCEDURE `getFilteredData`()
    -> BEGIN
    ->   DROP TEMPORARY TABLE IF EXISTS `temptbl`;
    ->   CREATE TEMPORARY TABLE `temptbl` (
    ->     `id` VARCHAR(50),
    ->     `name` VARCHAR(500),
    ->     `status` INT);
    ->   PREPARE `stmt` FROM 'INSERT INTO `temptbl`
    '>                        SELECT `id`, `name`, `status`
    '>                        FROM `v_release`';
    ->   EXECUTE `stmt`;
    ->   DEALLOCATE PREPARE `stmt`;
    ->   SELECT `id`, `name`, `status`
    ->   FROM `temptbl`;
    ->   DROP TEMPORARY TABLE IF EXISTS `temptbl`;
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

mysql> CALL `getFilteredData`;
+------+-------+--------+
| id   | name  | status |
+------+-------+--------+
| 1    | NAME1 |      1 |
| 2    | NAME2 |      2 |
| 3    | NAME1 |      3 |
+------+-------+--------+
3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

SQL Fiddle demo