使用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) {
                //清理掉这个表
                XxlJobHelper.log("清理表：" + tableName);
                logger.info("清理表：" + tableName);
                baseDao.saveOrUpdateJdbc("drop table " + tableName);
            }
        }
    }
    XxlJobHelper.log("清理备份表任务正常结束！");
}