删除表数据的存储过程, 可用于定时任务, 以达到定时清理数据的需求
DROP PROCEDURE procedure_table_clean;
CREATE PROCEDURE procedure_table_clean()
BEGIN
DECLARE no_more_record integer DEFAULT 0;
DECLARE id varchar(36);
DECLARE num integer default 0;
DECLARE cur_record CURSOR FOR SELECT UUID FROM table_1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_record = 1; # 当没有记录时赋值为1
OPEN cur_record;
FETCH cur_record INTO id;
WHILE no_more_record != 1 DO
DELETE FROM table_1 WHERE col_id = id;
IF num > 1000
THEN
COMMIT; SET num = 0;
ELSE
SET num = num + 1;
END IF;
FETCH cur_record INTO t_uuid;
END WHILE;
COMMIT;
CLOSE cur_record;
# 游标释放
END;