MySQL查询结果rewind后无法重复使用问题求助

Hey there! Let's break down why your rewind isn't working and how to fix this, especially given your use case with long-running shell scripts and cron jobs.

First off, the core issue here is that MySQLi's default statement result sets are forward-only—once you iterate through them once, the cursor hits the end, and you can't just rewind it unless you take specific steps. Let's go through the best solutions for your scenario:

1. Load the Entire Dataset into an Array (Simplest Approach) #

If your dataset isn't massive (which it probably shouldn't be for long-running scripts, otherwise you'll hit memory issues anyway), just fetch all rows into a PHP array upfront. This lets you loop through it as many times as you want, no cursor tricks needed.

Here's how to adjust your code:

// Prepare and execute your query
if (!($stmt = $mysqli->prepare("SELECT node, model FROM your_table WHERE ..."))) {
    die("Prepare failed: " . $mysqli->error);
}
$stmt->execute();

// Get the result set and fetch all rows into an array
$result = $stmt->get_result();
$dataset = [];
while ($row = $result->fetch_assoc()) {
    $dataset[] = $row;
}

// Now you can reuse $dataset as much as you need!
// First pass to run your slow script
foreach ($dataset as $row) {
    // Execute your shell script—always escape args to avoid injection!
    $scriptOutput = shell_exec("/path/to/your/script.sh " . escapeshellarg($row['node']) . " " . escapeshellarg($row['model']));
    // Handle output/logging here
}

// Need to run it again later? Just loop the array again
foreach ($dataset as $row) {
    // Repeat processing
}

2. Use a Scrollable Cursor (For Large Datasets) #

If loading everything into memory isn't feasible, you can create a scrollable cursor when preparing your statement. This lets you reset the cursor to the start of the result set. Note that this requires your MySQL storage engine to support scrollable cursors (InnoDB does, MyISAM doesn't), and you need to keep the database connection open the whole time.

Example code:

// Prepare with a scrollable cursor flag
if (!($stmt = $mysqli->prepare("SELECT node, model FROM your_table WHERE ...", MYSQLI_CURSOR_SCROLLABLE))) {
    die("Prepare failed: " . $mysqli->error);
}
$stmt->execute();
$result = $stmt->get_result();

// First iteration
while ($row = $result->fetch_assoc()) {
    // Process row
}

// Rewind to the start of the result set
if (!$result->data_seek(0)) {
    die("Failed to rewind result set: " . $result->error);
}

// Second iteration
while ($row = $result->fetch_assoc()) {
    // Process again
}

3. Add Status Tracking for Your Cron Job Workflow #

Since you mentioned using cron to run the script later and need to mark new datasets, you should add a status column to your table (like status with values unprocessed, processing, processed). This ensures you never reprocess data and your cron job only picks up new, unhandled rows.

Here's a full example combining this with the array approach (safer for long-running scripts, as it avoids keeping a DB connection open the whole time):

$mysqli->begin_transaction();
try {
    // Lock unprocessed rows to prevent cron from grabbing them mid-processing
    $stmt = $mysqli->prepare("SELECT node, model FROM your_table WHERE status = 'unprocessed' FOR UPDATE");
    $stmt->execute();
    $result = $stmt->get_result();
    $dataset = [];
    while ($row = $result->fetch_assoc()) {
        $dataset[] = $row;
    }

    // Mark rows as processing so other scripts don't touch them
    $stmt = $mysqli->prepare("UPDATE your_table SET status = 'processing' WHERE status = 'unprocessed'");
    $stmt->execute();
    $mysqli->commit();

    // Run your long shell script against each row
    foreach ($dataset as $row) {
        $scriptOutput = shell_exec("/path/to/your/script.sh " . escapeshellarg($row['node']) . " " . escapeshellarg($row['model']));
        // Log output or handle errors here
    }

    // Mark rows as processed once done
    $mysqli->begin_transaction();
    $stmt = $mysqli->prepare("UPDATE your_table SET status = 'processed' WHERE status = 'processing'");
    $stmt->execute();
    $mysqli->commit();
} catch (Exception $e) {
    // If something fails, rollback and reset status to unprocessed
    $mysqli->rollback();
    $stmt = $mysqli->prepare("UPDATE your_table SET status = 'unprocessed' WHERE status = 'processing'");
    $stmt->execute();
    die("Error during processing: " . $e->getMessage());
}

This way, your cron job can just query for status = 'unprocessed' each time, and you don't have to worry about overlapping or duplicate processing.

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