好吧,我最后写的:)
我花了一段时间,但在这里:
DELIMITER $$
DROP PROCEDURE IF EXISTS `procedure`.`rotate_events_pro`$$
CREATE DEFINER=`confiq`@`%` PROCEDURE `rotate_events_pro`(p_threshold_rotate_row int,p_days_to_keep int,OUT p_message VARCHAR(200))
BEGIN
DECLARE v_id_to_move INT;
#lets take ID that we need to move
#we can't use variables in LIMIT, how smart MySQL!
SET @v_sql = CONCAT('SELECT max(id)
INTO @v_id_to_move
FROM events
WHERE event_created < DATE_ADD(CURDATE(), INTERVAL - ',p_days_to_keep,' DAY) OR
id < (SELECT min(id) FROM (SELECT id FROM events ORDER BY id DESC LIMIT ?) as id)');
PREPARE stmt1 FROM @v_sql;
SET @param1 = p_threshold_rotate_row;
EXECUTE stmt1 USING @param1;
DEALLOCATE PREPARE stmt1;
#we have IDs that need to to be moved to arhive
IF @v_id_to_move > 0 THEN
START TRANSACTION;
INSERT INTO archived_events SELECT * FROM events WHERE id < @v_id_to_move;
INSERT INTO archived_eventsinfo SELECT * FROM eventsinfo WHERE event_id < @v_id_to_move;
DELETE FROM events WHERE id < @v_id_to_move;
DELETE FROM eventsinfo WHERE event_id < @v_id_to_move;
COMMIT;
SET p_message = CONCAT('moved all events that id is < ',@v_id_to_move);
ELSE
SET p_message = 'Nothing to move';
END IF;
END$$
DELIMITER ;
请随时反馈或编辑以供自己使用:)