---

1、bash腳本手動清空mysql表數據

#!/bin/bash# 配置區域（修改此處）
MYSQL_USER="root"
MYSQL_PASSWORD="123456"
MYSQL_HOST="localhost"
DATABASES=("hps-base:base_test_item"        # 格式：數據庫名:表名1,表名2#"db2:tableA,tableB"#"db3:*"                    # *表示清空所有表
)LOG_FILE="./cleanup_$(date +%Y%m%d_%H%M%S).log"
echo "清理日志已創建: $LOG_FILE" | tee -a $LOG_FILE# 檢查MySQL連接
check_mysql() {mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -h$MYSQL_HOST -e "SELECT 1;" &> /dev/nullif [ $? -ne 0 ]; thenecho "錯誤: 無法連接到MySQL服務器" | tee -a $LOG_FILEexit 1fi
}escape_db_name() {local db_name="$1"# 用反引號包裹整個數據庫名（保留連字符等字符）echo "\`$db_name\`"
}# 清理指定數據庫
cleanup_db() {local raw_db_name=$1local db_name=$(escape_db_name "$raw_db_name")  # 關鍵轉義處理#local db_name=$1local tables_str=$2IFS=',' read -r -a tables <<< "$tables_str"  # 將逗號分隔轉為數組echo "========== 正在清理數據庫: $db_name ==========" | tee -a $LOG_FILE# 禁用外鍵檢查mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -h$MYSQL_HOST -e "SET FOREIGN_KEY_CHECKS=0;" 2>> $LOG_FILEfor table in "${tables[@]}"; doif [ "$table" == "*" ]; then# 清空所有表echo "清空數據庫 $db_name 所有表..." | tee -a $LOG_FILEALL_TABLES=$(mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -h$MYSQL_HOST -N -B -e "SHOW TABLES FROM $db_name")for t in $ALL_TABLES; doecho "TRUNCATE TABLE $db_name.$t" | tee -a $LOG_FILEmysql -u$MYSQL_USER -p$MYSQL_PASSWORD -h$MYSQL_HOST -e "TRUNCATE TABLE $db_name.$t" 2>> $LOG_FILEdoneelseecho "清空表 $db_name.$table" | tee -a $LOG_FILEmysql -u$MYSQL_USER -p$MYSQL_PASSWORD -h$MYSQL_HOST -e "TRUNCATE TABLE $db_name.$table" 2>> $LOG_FILEfidone# 恢復外鍵檢查mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -h$MYSQL_HOST -e "SET FOREIGN_KEY_CHECKS=1;" 2>> $LOG_FILE
}# 主執行流程
main() {check_mysqlfor db_config in "${DATABASES[@]}"; doIFS=':' read -r db_name db_tables <<< "$db_config"  # 拆分數據庫名和表名cleanup_db "$db_name" "$db_tables"doneecho "清理完成，日志保存在: $LOG_FILE" | tee -a $LOG_FILE
}main