mysql自定义日志保留10000条，可通过定时任务去调用（定时任务实现没写，仅删除多余日志语句）

brave_zhao

已于 2023-09-19 10:36:13 修改

阅读量237

点赞数

分类专栏： mysql

于 2021-05-27 08:12:34 首次发布

本文链接：https://blog.csdn.net/zy103118/article/details/117318128

版权

mysql 数据库

mysql 专栏收录该内容

73 篇文章 0 订阅

订阅专栏

方式一：

DELETE from t_send_log WHERE id IN (SELECT * from (SELECT id from t_send_log where _rowid >10000 ORDER BY visit_time DESC ) x )

__________________________________________________________________

方式一不好用的话用方式二：

DELETE from t_interactive_log WHERE id not IN (SELECT * from (SELECT id from t_interactive_log  ORDER BY visit_time DESC limit 10000) x )

方式二的体现

if (countInterActiveLog > (Integer.valueOf(ConstantEnum.LOG_COUNT.getValue()).intValue())) {
    String deleteSqlForInteractiveLog = "DELETE from t_interactive_log WHERE id not IN (SELECT * from (SELECT id from t_interactive_log  ORDER BY visit_time DESC limit " + ConstantEnum.LOG_COUNT.getValue() + ") x )";//保留最新50000条接收日志
   sqlMapper.delete(deleteSqlForInteractiveLog);
}

__________________________________________________________________

方式三

interactiveLogMapper.deleteBeforeDays(365);//保留近365日接收日志

<delete id="deleteBeforeDays" >
      DELETE
      FROM
      t_interactive_log
      WHERE
      t_interactive_log.id IN (
      SELECT
      x.id
      FROM
      (
      SELECT
      id
      FROM
      t_interactive_log
      WHERE
      visit_time <![CDATA[<=]]> DATE_SUB(CURDATE(), INTERVAL #{dayNumber,jdbcType=INTEGER} DAY)
      ORDER BY
      visit_time DESC
      ) x
      )
</delete>