如何编写MySQL/PHP脚本实现每小时批量拉取100条数据用于Cron任务

Alright, let's solve this problem so you can reliably pull 100 rows every hour without repeating data. Your current query grabs the latest 100 rows each time, which will duplicate data if run hourly—so we need a way to track which rows you've already pulled. Here's a step-by-step solution using a shell script and cron:

Core Idea #

We need to track the last ID you pulled so each subsequent run grabs the next batch of 100 rows (either working through existing data or pulling new rows as they're added). We'll store this ID in a simple text file (easy for scripts to read/write) and update it after each pull.

Step 1: Choose Your Pull Strategy #

First, pick the scenario that fits your needs:

Scenario 1: Batch Pull All Existing Data (e.g., 600+ rows over 6+ hours) #

If you want to work through all existing rows from newest to oldest (matching your original query's ORDER BY id DESC), we'll start with the largest ID and pull batches of older rows each hour.

Scenario 2: Pull Newly Added Data Ongoing #

If you want to only pull rows added since your last run (great for tables that keep growing), we'll track the highest ID pulled so far and grab any new rows (up to 100) each hour.

Step 2: Write the Shell Script #

We'll use a bash script with MySQL commands (adjust for PostgreSQL/SQLite/etc. if needed).

First, create a state file to track the last pulled ID:

• For Scenario 1: Initialize with the largest ID in your table

  # Run this once to set up
  mysql -u your_username -p your_database -se "SELECT MAX(id) FROM your_table" > /path/to/last_pulled_id.txt
  

• For Scenario 2: Initialize with 0 (assuming IDs start at 1)

  echo 0 > /path/to/last_pulled_id.txt
  

Now create the script pull_data.sh:

#!/bin/bash

# Configuration - update these values!
DB_USER="your_username"
DB_NAME="your_database"
TABLE_NAME="your_table"
STATE_FILE="/path/to/last_pulled_id.txt"
BATCH_SIZE=100
OUTPUT_DIR="/path/to/save/pulled_data"

# Create output directory if it doesn't exist
mkdir -p "$OUTPUT_DIR"

# Read the last pulled ID
LAST_ID=$(cat "$STATE_FILE")

##### Choose the query for your scenario #####

# Scenario 1: Pull next oldest 100 rows (newest to oldest)
QUERY="SELECT * FROM $TABLE_NAME WHERE id < $LAST_ID ORDER BY id DESC LIMIT $BATCH_SIZE"

# Scenario 2: Pull newest 100 rows added since last run
# QUERY="SELECT * FROM $TABLE_NAME WHERE id > $LAST_ID ORDER BY id DESC LIMIT $BATCH_SIZE"

# Run query and save to timestamped file
mysql -u "$DB_USER" -p "$DB_NAME" -se "$QUERY" > "$OUTPUT_DIR/pulled_data_$(date +%Y%m%d_%H%M).txt"

# Update state file with new last ID
# For Scenario 1: Use smallest ID from this batch (moving backward)
NEW_LAST_ID=$(mysql -u "$DB_USER" -p "$DB_NAME" -se "SELECT MIN(id) FROM ($QUERY) AS temp")

# For Scenario 2: Use largest ID from this batch (moving forward)
# NEW_LAST_ID=$(mysql -u "$DB_USER" -p "$DB_NAME" -se "SELECT MAX(id) FROM ($QUERY) AS temp")

# Only update if we got results (avoid NULL if no rows left)
if [ -n "$NEW_LAST_ID" ]; then
    echo "$NEW_LAST_ID" > "$STATE_FILE"
else
    echo "No more rows to pull. Exiting." >> "$OUTPUT_DIR/pull_logs.log"
    exit 0
fi

# Optional: Log confirmation
echo "Pulled batch at $(date): $(wc -l "$OUTPUT_DIR/pulled_data_$(date +%Y%m%d_%H%M).txt") rows" >> "$OUTPUT_DIR/pull_logs.log"

Security Note #

Don't hardcode your database password! Instead, create a ~/.my.cnf file (set permissions to 600 so only you can read it):

[client]
user=your_username
password=your_database_password
database=your_database

Then remove the -p flag from the mysql commands in the script.

Step 3: Make the Script Executable #

Run this to grant execution permissions:

chmod +x /path/to/pull_data.sh

Step 4: Set Up the Cron Job #

Open your crontab editor:

crontab -e

Add this line to run the script every hour on the hour:

0 * * * * /path/to/pull_data.sh >> /path/to/pull_logs.log 2>&1

This logs both output and errors to help debug issues.

Bonus Tips #

• To reset the pull (start over), just update the last_pulled_id.txt file with your desired starting ID.
• For PostgreSQL, replace mysql with psql (adjust query syntax slightly, e.g., subqueries work similarly but ensure you use valid PostgreSQL syntax).
• If you want to display data instead of saving it, replace the output redirection with echo or pipe to a tool like less.

内容的提问来源于stack exchange，提问作者Jay Smoke