--大規模なテーブルデータの移行、毎朝1クリックして5クリック実行、実行間隔10分、古いデータを履歴テーブルに移行します。
DELIMITER $$ USE `dbx`$$ DROP PROCEDURE IF EXISTS `pro_xx`$$ CREATE PROCEDURE `pro_xx`() BEGIN DECLARE p_oalid INT DEFAULT 0; DECLARE STOP INT DEFAULT 0; DECLARE cur_oalid CURSOR FOR SELECToal.id FROM oal_xxx oal WHERE oal.`ymd` <CONCAT(YEAR(DATE_ADD(NOW(),INTERVAL -1 MONTH)),'-',MONTH(DATE_ADD(NOW(),INTERVAL -1 MONTH )),'-', DAY(DATE_ADD(NOW(),INTERVAL-1 MONTH ))) LIMIT 1000; DECLARE EXIT HANDLER FOR SQLSTATE '02000' /**包含游标not found*/ BEGIN SET STOP=1; INSERTINTO db_logs(log_type,table_name,action_name,log_msg,create_time) SELECT1, 'oal_xxx','pro_oal_log_move',CONCAT('primary key:',p_oalid,' 游标执行正常结束!'),NOW(); END; DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN SETSTOP=1; INSERTINTO db_logs(log_type,table_name,action_name,log_msg,create_time) SELECT2, 'oal_xxx','pro_oal_log_move',CONCAT('primary key:',p_oalid,' 移动执行失败'),NOW(); END; OPEN cur_oalid;
rreeee
- (www.php.cn)!