在执行以下存储过程时,出现了一个类似“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/