IT不打烊的tdsql_mysql学习记录2

#!/bin/bash

# MySQL 配置

DB_USER="用户名"

DB_PASS="密码"

DB_NAME="testdb"

DB_HOST="实例IP"

DB_PORT="实例端口"

BATCH_SIZE=2000 # 每批次插入量

TOTAL_USERS=1000000 # 总用户数

TOTAL_ORDERS=1000000 # 总订单数

# 进度显示函数

show_progress() {

local current=$1

local total=$2

local type=$3

printf "\r插入%s: %d%% [%d/%d]" "$type" $((current*100/total)) $current $total

}

# 安全执行函数（含错误重试）

safe_exec() {

local sql=$1

for attempt in {1..3}; do

if mysql -h "$DB_HOST" -P "$DB_PORT" -u "$DB_USER" --password="$DB_PASS" "$DB_NAME" -e "$sql" ; then

return 0

else

echo "尝试 $attempt 失败，正在重试..."

sleep $((attempt*2))

fi

done

echo "永久失败: $sql"

exit 1

}

# 插入用户数据（单线程）

echo "开始插入用户数据..."

for ((i=1; i<=TOTAL_USERS; i+=BATCH_SIZE)); do

end=$((i+BATCH_SIZE-1))

((end > TOTAL_USERS)) && end=$TOTAL_USERS

# 构建批量插入SQL

SQL="START TRANSACTION;"

SQL+="INSERT INTO users (user_id, username, phone) VALUES "

for ((j=i; j<=end; j++)); do

SQL+="($j, 'user$j', '123456789${j:0:8}'),"

done

SQL="${SQL%,}; COMMIT;"

safe_exec "$SQL"

show_progress $end $TOTAL_USERS "用户"

done

echo -e "\n用户数据插入完成！"

# 插入订单数据（单线程）

echo "开始插入订单数据..."

for ((i=1; i<=TOTAL_ORDERS; i+=BATCH_SIZE)); do

end=$((i+BATCH_SIZE-1))

((end > TOTAL_ORDERS)) && end=$TOTAL_ORDERS

# 构建批量插入SQL

SQL="START TRANSACTION;"

SQL+="INSERT INTO orders (id, user_id, product_name, amount) VALUES "

for ((j=i; j<=end; j++)); do

user_id=$((1 + RANDOM % TOTAL_USERS)) # 随机用户ID

amount=$((RANDOM % 101)) # 0-100随机整数

SQL+="($j, $user_id, 'product$j', $amount),"

done

SQL="${SQL%,}; COMMIT;"

safe_exec "$SQL"

show_progress $end $TOTAL_ORDERS "订单"

done

echo -e "\n订单数据插入完成！"

# 数据完整性验证

echo -e "\n最终数据校验:"

mysql -h "$DB_HOST" -P "$DB_PORT" -u "$DB_USER" --password="$DB_PASS" "$DB_NAME" <<EOF

SELECT

(SELECT COUNT(*) FROM users) AS total_users,

(SELECT COUNT(*) FROM orders) AS total_orders,

(SELECT COUNT(DISTINCT user_id) FROM orders) AS active_users,

(SELECT MAX(amount) FROM orders) AS max_amount,

(SELECT AVG(amount) FROM orders) AS avg_amount;

EOF