代码方式
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()
BEGINDECLARE 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: LOOPFETCH cur INTO tableName;IF done THENLEAVE 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();"
