如何用脚本批量删除数据库旧记录？

wen 实用脚本 2026-07-24 144

本文目录导读：

如何用脚本批量删除数据库旧记录？

MySQL 脚本
PostgreSQL 脚本
MongoDB 脚本
通用 Python 脚本（支持多种数据库）
Shell 脚本（通用方案）
重要注意事项

我来介绍几种批量删除数据库旧记录的脚本方法,涵盖不同数据库类型。

MySQL 脚本

按时间条件删除

-- 删除30天前的记录
DELETE FROM your_table 
WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);
-- 或指定具体日期
DELETE FROM your_table 
WHERE create_time < '2024-01-01';

分批删除（避免锁表）

#!/bin/bash
# 分批删除脚本 delete_old_records.sh
DB_HOST="localhost"
DB_USER="root"
DB_PASS="password"
DB_NAME="your_database"
TABLE="your_table"
BATCH_SIZE=10000
SLEEP_INTERVAL=1
while true; do
    # 删除一批旧记录
    DELETE_SQL="DELETE FROM $TABLE 
                WHERE created_at < DATE_SUB(NOW(), INTERVAL 90 DAY) 
                LIMIT $BATCH_SIZE"
    RESULT=$(mysql -h$DB_HOST -u$DB_USER -p$DB_PASS $DB_NAME -e "$DELETE_SQL" 2>&1)
    # 检查受影响行数
    AFFECTED=$(echo $RESULT | grep -oP 'Rows matched: \d+' | grep -oP '\d+')
    if [ "$AFFECTED" -eq 0 ]; then
        echo "删除完成，没有更多记录"
        break
    fi
    echo "删除了 $AFFECTED 条记录"
    sleep $SLEEP_INTERVAL
done

PostgreSQL 脚本

使用存储过程

CREATE OR REPLACE FUNCTION batch_delete_old_records()
RETURNS void AS $$
DECLARE
    batch_size INTEGER := 10000;
    deleted_count INTEGER;
BEGIN
    LOOP
        DELETE FROM your_table
        WHERE ctid IN (
            SELECT ctid FROM your_table
            WHERE created_at < NOW() - INTERVAL '30 days'
            LIMIT batch_size
        );
        GET DIAGNOSTICS deleted_count = ROW_COUNT;
        IF deleted_count = 0 THEN
            EXIT;
        END IF;
        RAISE NOTICE 'Deleted % records', deleted_count;
        -- 短暂暂停
        PERFORM pg_sleep(0.1);
    END LOOP;
END;
$$ LANGUAGE plpgsql;
-- 执行
SELECT batch_delete_old_records();

MongoDB 脚本

Node.js 批量删除脚本

// delete_old_records.js
const { MongoClient } = require('mongodb');
async function batchDeleteOldRecords() {
    const uri = 'mongodb://localhost:27017';
    const client = new MongoClient(uri);
    try {
        await client.connect();
        const db = client.db('your_database');
        const collection = db.collection('your_collection');
        const cutoffDate = new Date();
        cutoffDate.setDate(cutoffDate.getDate() - 30); // 30天前
        let deletedCount = 0;
        const batchSize = 1000;
        while (true) {
            // 查找要删除的文档
            const docsToDelete = await collection
                .find({ createdAt: { $lt: cutoffDate } })
                .limit(batchSize)
                .project({ _id: 1 })
                .toArray();
            if (docsToDelete.length === 0) {
                console.log(`删除完成，共删除 ${deletedCount} 条记录`);
                break;
            }
            // 批量删除
            const ids = docsToDelete.map(doc => doc._id);
            const result = await collection.deleteMany({
                _id: { $in: ids }
            });
            deletedCount += result.deletedCount;
            console.log(`已删除 ${deletedCount} 条记录`);
            // 短暂暂停
            await new Promise(resolve => setTimeout(resolve, 100));
        }
    } finally {
        await client.close();
    }
}
batchDeleteOldRecords().catch(console.error);

通用 Python 脚本（支持多种数据库）

使用 SQLAlchemy

# batch_delete.py
import time
from datetime import datetime, timedelta
from sqlalchemy import create_engine, text
def batch_delete_old_records():
    # 数据库连接配置
    db_config = {
        'mysql': 'mysql+pymysql://user:password@localhost/database',
        'postgresql': 'postgresql://user:password@localhost/database',
        # 其他数据库连接字符串
    }
    # 选择数据库类型
    db_type = 'mysql'
    engine = create_engine(db_config[db_type])
    # 参数配置
    table_name = 'your_table'
    date_column = 'created_at'
    days_old = 30
    batch_size = 10000
    cutoff_date = datetime.now() - timedelta(days=days_old)
    total_deleted = 0
    with engine.connect() as conn:
        while True:
            # 执行批量删除
            if db_type == 'mysql':
                sql = text(f"""
                    DELETE FROM {table_name} 
                    WHERE {date_column} < :cutoff_date 
                    LIMIT :batch_size
                """)
            elif db_type == 'postgresql':
                sql = text(f"""
                    DELETE FROM {table_name}
                    WHERE ctid IN (
                        SELECT ctid FROM {table_name}
                        WHERE {date_column} < :cutoff_date
                        LIMIT :batch_size
                    )
                """)
            result = conn.execute(sql, {
                'cutoff_date': cutoff_date,
                'batch_size': batch_size
            })
            affected_rows = result.rowcount
            total_deleted += affected_rows
            if affected_rows == 0:
                print(f"删除完成，共删除 {total_deleted} 条记录")
                break
            print(f"已删除 {total_deleted} 条记录")
            # 避免数据库压力过大
            time.sleep(0.5)
    return total_deleted
if __name__ == '__main__':
    batch_delete_old_records()

Shell 脚本（通用方案）

带日志和错误处理的完整脚本

#!/bin/bash
# safe_batch_delete.sh
# 配置
DB_TYPE="mysql"    # mysql, postgresql
DB_HOST="localhost"
DB_PORT="3306"
DB_USER="root"
DB_PASS="password"
DB_NAME="your_db"
TABLE="your_table"
DATE_COL="created_at"
DAYS_OLD=90
BATCH_SIZE=5000
SLEEP_SEC=1
LOG_FILE="/var/log/db_cleanup.log"
# 日志函数
log() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a $LOG_FILE
}
# 错误处理
handle_error() {
    log "错误: $1"
    exit 1
}
# 检查配置
if [ -z "$DB_PASS" ]; then
    handle_error "数据库密码未设置"
fi
# 计算截止日期
CUTOFF_DATE=$(date -d "$DAYS_OLD days ago" +%Y-%m-%d)
log "开始删除 $TABLE 表中 $DAYS_OLD 天前的记录（截止日期：$CUTOFF_DATE）"
TOTAL_DELETED=0
while true; do
    # 构建删除SQL
    if [ "$DB_TYPE" == "mysql" ]; then
        DELETE_SQL="DELETE FROM $TABLE WHERE $DATE_COL < '$CUTOFF_DATE' LIMIT $BATCH_SIZE;"
    elif [ "$DB_TYPE" == "postgresql" ]; then
        DELETE_SQL="DELETE FROM $TABLE WHERE ctid IN (SELECT ctid FROM $TABLE WHERE $DATE_COL < '$CUTOFF_DATE' LIMIT $BATCH_SIZE);"
    else
        handle_error "不支持的数据库类型: $DB_TYPE"
    fi
    # 执行删除
    if [ "$DB_TYPE" == "mysql" ]; then
        RESULT=$(mysql -h$DB_HOST -P$DB_PORT -u$DB_USER -p$DB_PASS $DB_NAME -e "$DELETE_SQL" 2>&1)
    elif [ "$DB_TYPE" == "postgresql" ]; then
        RESULT=$(psql -h$DB_HOST -p$DB_PORT -U$DB_USER -d$DB_NAME -c "$DELETE_SQL" 2>&1)
    fi
    # 检查错误
    if [ $? -ne 0 ]; then
        handle_error "删除失败: $RESULT"
    fi
    # 提取删除数量
    if [ "$DB_TYPE" == "mysql" ]; then
        AFFECTED=$(echo "$RESULT" | grep -oP 'Rows matched: \d+' | grep -oP '\d+')
    else
        AFFECTED=$(echo "$RESULT" | grep -oP 'DELETE \d+' | grep -oP '\d+')
    fi
    if [ -z "$AFFECTED" ] || [ "$AFFECTED" -eq 0 ]; then
        log "删除完成，共删除 $TOTAL_DELETED 条记录"
        break
    fi
    TOTAL_DELETED=$((TOTAL_DELETED + AFFECTED))
    log "本轮删除 $AFFECTED 条记录，累计删除 $TOTAL_DELETED 条"
    sleep $SLEEP_SEC
done
log "清理任务完成"

重要注意事项

备份数据

# 删除前备份
mysqldump -u root -p your_db your_table > backup_$(date +%Y%m%d).sql

测试环境验证

先在测试库运行,确认不影响业务。

监控数据库性能

-- MySQL 查看当前连接
SHOW PROCESSLIST;
-- PostgreSQL 查看活动查询
SELECT * FROM pg_stat_activity;

建议的时间窗口

业务低峰期执行（凌晨2-5点）
避免在高峰时段进行大量删除

选择哪种脚本取决于你的数据库类型、表大小、业务需求等因素，对于大表（千万级以上），建议使用分批删除方式以避免锁表。

本文地址： https://www.bmjup.com/post/28896.html