使用定时任务定期删除数据库数据

代码方式

import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;

/**
 * @author: jg
 * @date: 2024-09-03
 * @description: 定期清除数据库中逻辑删除的数据
 */
@Component
@RequiredArgsConstructor
@Slf4j
public class ClearAllTableTask {
    
    private final JdbcTemplate jdbcTemplate;
    
    private final DataSource dataSource;
    
    private static final String DELETECOLUMN = "deleted";
    
    
    @Scheduled(cron = "${fm.clearAllTableTask}")
    public void deleteData() {
        LocalDateTime now = LocalDateTime.now();
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
        log.info("清除数据库已删除的数据任务执行，时间{}", now.format(formatter));
        try (Connection connection = DataSourceUtils.getConnection(dataSource)) {
            //Statement stmt = conn.createStatement();
            //ResultSet tables = stmt.executeQuery("SHOW TABLES");
            DatabaseMetaData metaData = connection.getMetaData();
            String databaseName = metaData.getURL().split("/")[3];
            log.info("getCatalog {} databaseName {}", connection.getCatalog(), databaseName);
            ResultSet tables = metaData.getTables(connection.getCatalog(), null, "%", new String[] {"TABLE"});
            while (tables.next()) {
                String tableName = tables.getString("TABLE_NAME");
                //判断表是否有某字段
                ResultSet columns = metaData.getColumns(null, null, tableName, DELETECOLUMN);            
                if (columns.next()) {
                    String deleteSql = "DELETE FROM " + tableName + " WHERE deleted = 1";
                    jdbcTemplate.update(deleteSql);
                    log.info("Deleted from {}", tableName);
                }
                
            }
        } catch (SQLException e) {
            log.error("执行定时任务删除数据库中逻辑删除的数据，sql 异常{}", e.getMessage());
            throw new RuntimeException(e.getMessage());
        }
    }
    
}

存储过程

定时删除数据库中所有表中字段deleted为1的数据

DELIMITER //
CREATE PROCEDURE DeleteRecordsWithDeletedFlag()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE tableName CHAR(64);
    DECLARE cur CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_schema = 'your_database_name';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO tableName;
        IF done THEN
            LEAVE read_loop;
        END IF;

        SET @query = CONCAT('DELETE FROM ', tableName, ' WHERE deleted = 1');
        PREPARE stmt FROM @query;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;

    CLOSE cur;
END//
DELIMITER ;

调用存储过程

CALL DeleteRecordsWithDeletedFlag();

创建定时任务

windows

你可以使用任务计划程序创建一个新任务，设置触发器为每天凌晨1点，并将操作设置为运行一个批处理文件，该文件包含以下内容：

@echo off
"C:\Program Files\MySQL\MySQL Server 5.7\bin\mysql.exe" -u your_username -pyour_password your_database_name -e "CALL DeleteRecordsWithDeletedFlag();"

linux

你可以在/etc/crontab文件中添加以下行（假设每天凌晨1点执行）

0 1 * * * root /usr/bin/mysql -u your_username -p'your_password' your_database_name -e "CALL DeleteRecordsWithDeletedFlag();"