oldwu's Blog - jdbc
type: channel
link: https://blog.oldwu.top/index.php/tag/jdbc/
items: 使用JDBC获取Mysql数据库中符合条件的备份表并清理n天之前的表

使用JDBC获取Mysql数据库中符合条件的备份表并清理n天之前的表
type: item
date: 2022-06-20T15:41:53+08:00
description: 使用JDBC获取Mysql数据库中符合条件的备份表并清理n天之前的表在执行同步操作之后会备份原来的表，导致线上数据库表越来越多，需要写一个定时清理这边定时清理使用xxl-job作为执行器，当然也可以当成正常方法来手动触发获取当前数据库名称select database() as dbname获取这个数据库中存在的符合条件的表我这边获取BACKUP_开头的表select table_name as name from information_schema.tables where table_schema= '" + dbname + "' and table_name like '" + "BACKUP_ + "%' order by table_name至于为什么不直接用show tables，这玩意貌似没法加like条件或者as xxx字段，有可能是我没找到方法完整代码如下/** * 定时清理备份的表 * @param needClear 需要清理的原始表名，多个表以逗号分隔 * @param clearDay 清理期限 * @throws Exception exception */ @XxlJob("定时清理备份的表") public void clearExpireBackupTable(String needClear, int clearDay) throws Exception { String jobParam = XxlJobHelper.getJobParam(); if (StringUtils.isNotBlank(jobParam)) { //使用xxl-job传递的参数：system_user,system_dept,system_user_dept|20 String[] split = jobParam.split("\\|"); needClear = split[0]; clearDay = Integer.parseInt(split[1]); } //清理的表只能以BACKUP为前缀 String[] split = needClear.split(","); //首先拿到所有backup开头的表 //获取主数据库名称 Map<String, Object> database = baseDao.findOneJdbc("select database() as name"); String dbname = (String) database.get("name"); //获取所有前缀匹配的表 String sql = "select table_name as name from information_schema.tables where table_schema= '" + dbname + "' and table_name like '" + BACKUP_PREFIX + "%' order by table_name"; List<Map<String, Object>> backupTableList = baseDao.findListJdbc(sql); for (Map<String, Object> stringObjectMap : backupTableList) { String tableName = (String) stringObjectMap.get("name"); //获取到改备份表的原始表名，例如：BACKUP_system_dept_2022_06_13_11_43_25 String regx = BACKUP_PREFIX + "([\\s\\S]+)_([\\d]{4}_[\\d]{2}_[\\d]{2}_[\\d]{2}_[\\d]{2}_[\\d]{2})"; String tableRealName = ReUtil.get(regx, tableName, 1); if (StringUtils.isNotBlank(tableRealName) && StringUtils.equalsAny(tableRealName, split)) { String tableCreateTimeStr = ReUtil.get(regx, tableName, 2); DateTime tableCreateTime = DateUtil.parse(tableCreateTimeStr, "yyyy_MM_dd_HH_mm_ss"); //判断这个表的创建时间是否超过设定期限 if (DateUtil.betweenDay(tableCreateTime, new Date(), false) > clearDay) { /…