在执行以下存储过程时,出现了一个类似“Unknown table‘matched_temp’”的错误。

DELIMITER $$

DROP PROCEDURE IF EXISTS `apms`.`sp_partition_parkinout` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_partition_parkinout`()
BEGIN

DROP TEMPORARY TABLE IF EXISTS MATCHED_TEMP;

CREATE TEMPORARY TABLE MATCHED_TEMP AS
    SELECT ID FROM CARDINOUT
    WHERE (`State` = 2) AND
        (ID NOT IN (SELECT ID FROM CARDINOUT_MATCHED))
    LIMIT 200;

INSERT INTO CARDINOUT_MATCHED
SELECT * FROM CARDINOUT
WHERE ID IN (SELECT ID FROM MATCHED_TEMP);

DELETE FROM CARDINOUT
WHERE ID IN (SELECT ID FROM MATCHED_TEMP);

END $$

DELIMITER ;

我想定期执行它,将数据从cardinout移动到cardinout_matched。使表cardinout尽可能小的目的是因为经常查询cardinout。
有没有其他的选择来实现这一点?
谢谢
博特姆

最佳答案

CREATE TEMPORARY TABLE MATCHED_TEMP (
    ID INT
);
INSERT INTO MATCHED_TEMP
SELECT ID FROM CARDINOUT
(...)

关于mysql - 备份数据到另一个表,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/9543170/

10-11 20:39