使用折半查找法删除
DELIMITER //
DROP PROCEDURE IF EXISTS `PRO_BinarySearch_DeleteData` ;
CREATE PROCEDURE PRO_BinarySearch_DeleteData(IN p_tab VARCHAR(50),IN p_key1 VARCHAR(30), IN p_key2 VARCHAR(30),IN p_date DATETIME)
BEGIN
/*折半查找最大主键ID*/
/*Auther:Gerald*/
SET @v_str =
CONCAT('select
max(',p_key1,') into @v_maxid
from
',p_tab);
PREPARE stmt FROM @v_str;
EXECUTE stmt;
SET @v_str=
CONCAT('SELECT
min(',p_key1,') INTO @v_minid
FROM
',p_tab);
PREPARE stmt FROM @v_str;
EXECUTE stmt;
SELECT UNIX_TIMESTAMP(p_date) INTO @v_date;
SET @v_id=@v_minid;
WHILE @v_minid<=@v_maxid DO
SELECT CEIL((@v_minid+@v_maxid)/2) INTO @v_midid;
SET @v_str =CONCAT('select ',p_key2,' into @v_created_at from ',p_tab,' where ',p_key1,'>=@v_midid order by 1 limit 1');
PREPARE stmt FROM @v_str;
EXECUTE stmt;
/*right*/
IF @v_created_at<=@v_date THEN
SET @v_minid=@v_midid+1;
END IF;
/*left*/
IF @v_created_at>=@v_date THEN
SET @v_maxid=@v_midid-1;
END IF;
END WHILE;
/*分批次删除历史数据*/
SET @rowcnt=0;
START TRANSACTION;
cursor_loop:LOOP
set @v_str =CONCAT('DELETE FROM ',p_tab,' WHERE ',p_key1,'=',@v_id);
PREPARE stmt FROM @v_str;
EXECUTE stmt;
SET @rowcnt=@rowcnt+1;
IF @rowcnt=1000 THEN
SET @rowcnt =0 ;
COMMIT;
START TRANSACTION;
END IF ;
SET @v_id=@v_id+1;
IF @v_id > @v_midid THEN
LEAVE cursor_loop ;
END IF ;
END LOOP cursor_loop ;
COMMIT ;
SELECT @v_midid;
END;
//
DELIMITER ;
DROP PROCEDURE IF EXISTS `PRO_BinarySearch_DeleteData` ;
CREATE PROCEDURE PRO_BinarySearch_DeleteData(IN p_tab VARCHAR(50),IN p_key1 VARCHAR(30), IN p_key2 VARCHAR(30),IN p_date DATETIME)
BEGIN
/*折半查找最大主键ID*/
/*Auther:Gerald*/
SET @v_str =
CONCAT('select
max(',p_key1,') into @v_maxid
from
',p_tab);
PREPARE stmt FROM @v_str;
EXECUTE stmt;
SET @v_str=
CONCAT('SELECT
min(',p_key1,') INTO @v_minid
FROM
',p_tab);
PREPARE stmt FROM @v_str;
EXECUTE stmt;
SELECT UNIX_TIMESTAMP(p_date) INTO @v_date;
SET @v_id=@v_minid;
WHILE @v_minid<=@v_maxid DO
SELECT CEIL((@v_minid+@v_maxid)/2) INTO @v_midid;
SET @v_str =CONCAT('select ',p_key2,' into @v_created_at from ',p_tab,' where ',p_key1,'>=@v_midid order by 1 limit 1');
PREPARE stmt FROM @v_str;
EXECUTE stmt;
/*right*/
IF @v_created_at<=@v_date THEN
SET @v_minid=@v_midid+1;
END IF;
/*left*/
IF @v_created_at>=@v_date THEN
SET @v_maxid=@v_midid-1;
END IF;
END WHILE;
/*分批次删除历史数据*/
SET @rowcnt=0;
START TRANSACTION;
cursor_loop:LOOP
set @v_str =CONCAT('DELETE FROM ',p_tab,' WHERE ',p_key1,'=',@v_id);
PREPARE stmt FROM @v_str;
EXECUTE stmt;
SET @rowcnt=@rowcnt+1;
IF @rowcnt=1000 THEN
SET @rowcnt =0 ;
COMMIT;
START TRANSACTION;
END IF ;
SET @v_id=@v_id+1;
IF @v_id > @v_midid THEN
LEAVE cursor_loop ;
END IF ;
END LOOP cursor_loop ;
COMMIT ;
SELECT @v_midid;
END;
//
DELIMITER ;