数据库存储过程

需求：2022-10-10以后，设备事件条目超过2w条的，删除2w条记录

方案：通过编写存储过程循环执行删除

delimiter;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `deldata`(IN `delnum` int,IN `filter` int)
BEGIN
	DECLARE dev  VARCHAR(50);     ##定义执行满足删除条件的设备ID
	DECLARE cnt  INT DEFAULT 0;   ##定义满足删除条件的设备数量，直到为0时退出
	SELECT  count(tmp.device_id) into cnt 
	FROM (
	       SELECT device_id 
	       FROM (
	              SELECT device_id,COUNT(1) num 
	              FROM   soc_eventdata 
	              WHERE  event_time > '2022-10-10' GROUP BY device_id
	              ) t 
   	       WHERE  t.num > 2
   	     ) tmp;
	WHILE cnt > 0 do 
	      SELECT device_id into dev 
	      FROM (
	            SELECT device_id,COUNT(1) num 
	            FROM   soc_eventdata 
	            WHERE  event_time > '2022-10-10' 
	            GROUP BY device_id
	            ) t 
	      WHERE  t.num > filter limit 1;
	     
	      DELETE FROM soc_eventdata 
	      WHERE  device_id = dev and event_time > '2022-10-10' 
	      LIMIT  delnum;
	END WHILE;
END
;;
delimiter;