本文目录导读：
我来介绍几种批量删除数据库旧记录的脚本方法,涵盖不同数据库类型。
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点）
- 避免在高峰时段进行大量删除
选择哪种脚本取决于你的数据库类型、表大小、业务需求等因素，对于大表（千万级以上），建议使用分批删除方式以避免锁表。
