项目有一个需求表中的数据根据某个固定字段只需要保留最新的10条过期数据可以删掉同时设置定时删除根据该需求建立函数和事件如下MySQL 8.0以上CREATE DEFINERroot% PROCEDURE DeleteSampledataOpcKep() BEGIN DELETE t1 FROM em_sampledata_opc_kep t1 INNER JOIN ( SELECT ID, ROW_NUMBER() OVER(PARTITION BY TagTab ORDER BY DataTime DESC,ID DESC) AS rn FROM em_sampledata_opc_kep ) t2 ON t1.ID t2.ID WHERE t2.rn 5; ENDMySQL5.7DELETE t1 FROM your_table t1 WHERE (SELECT COUNT(*) FROM your_table t2 WHERE t2.TagTab t1.TagTab AND (t2.DateTimet1.DateTime OR (t2.DateTimet1.DateTime AND t2.IDt1.ID))) 10;事件CREATE DEFINERroot% EVENT DeleteSampledataOpcKep ON SCHEDULE EVERY 10 SECOND STARTS 2016-08-01 03:31:05 ON COMPLETION NOT PRESERVE ENABLE DO CALL DeleteSampledataOpcKep
项目有一个需求表中的数据根据某个固定字段只需要保留最新的10条过期数据可以删掉同时设置定时删除根据该需求建立函数和事件如下MySQL 8.0以上CREATE DEFINERroot% PROCEDURE DeleteSampledataOpcKep() BEGIN DELETE t1 FROM em_sampledata_opc_kep t1 INNER JOIN ( SELECT ID, ROW_NUMBER() OVER(PARTITION BY TagTab ORDER BY DataTime DESC,ID DESC) AS rn FROM em_sampledata_opc_kep ) t2 ON t1.ID t2.ID WHERE t2.rn 5; ENDMySQL5.7DELETE t1 FROM your_table t1 WHERE (SELECT COUNT(*) FROM your_table t2 WHERE t2.TagTab t1.TagTab AND (t2.DateTimet1.DateTime OR (t2.DateTimet1.DateTime AND t2.IDt1.ID))) 10;事件CREATE DEFINERroot% EVENT DeleteSampledataOpcKep ON SCHEDULE EVERY 10 SECOND STARTS 2016-08-01 03:31:05 ON COMPLETION NOT PRESERVE ENABLE DO CALL DeleteSampledataOpcKep
)
