批量删除数据脚本

#!/bin/bash
set -euo pipefail

########################### 【配置区，请修改】###########################
DB_HOST="127.0.0.1"
DB_PORT="3306"
DB_USER="root"
DB_PASS="123456"
DB_NAME="test"
TABLE_LIST=(

"table1"
"table2"
)

########################################################################

LOG_FILE="./clean_data_$(date +%Y%m%d).log"
echo "==================== 任务启动 $(date '+%Y-%m-%d %H:%M:%S') ====================" | tee -a "${LOG_FILE}"

exec_mysql() {
    local sql="$1"
    mysql -h"${DB_HOST}" -P"${DB_PORT}" -u"${DB_USER}" -p"${DB_PASS}" "${DB_NAME}" -s -e "${sql}"
}

clean_table() {
    local table=$1
    echo "【开始处理表】${table}" | tee -a "${LOG_FILE}"

    loop_count=1
    while true; do
        del_sql="DELETE FROM ${table} WHERE date <= DATE_SUB(NOW(),INTERVAL 6 MONTH) LIMIT 50000;"
        echo "第${loop_count}轮: ${del_sql}" >> "${LOG_FILE}"

        affected=$(exec_mysql "
        ${del_sql};
        SELECT ROW_COUNT();
        ")

        affected=$(echo "${affected}" | tail -1)
        echo "第${loop_count}轮本次删除行数: ${affected}" | tee -a "${LOG_FILE}"

        if [[ ${affected} -le 0 ]]; then
            echo "表${table}清理完成" | tee -a "${LOG_FILE}"
            break
        fi

        loop_count=$((loop_count + 1))
        sleep 1
    done
    echo "---------------------------------------------------------" | tee -a "${LOG_FILE}"
}

for tb in "${TABLE_LIST[@]}"; do
    clean_table "${tb}"
done

echo "==================== 全部任务完成 $(date '+%Y-%m-%d %H:%M:%S') ====================" | tee -a "${LOG_FILE}"