优化PDO脚本：改用函数式编写，提升效率并减少顺序执行

Great job getting that DB2-to-MySQL sync script fully tested and functional—full CRUD plus stale record checks is no small feat! Let’s break down how to refactor your sequential PDO code into a more modular, functional structure that’ll boost maintainability and cut down on redundant sequential logic.

Core Principles for the Refactor #

We’ll focus on single-responsibility functions (each function does one thing well) and batch operations (to reduce round-trips to the database, which is a big efficiency win over looping single queries).

Step 1: Split Core Logic into Reusable Functions #

Here’s how to break down your script into focused functions:

1. Fetch Raw Records from DB2

This function handles all DB2 connection and data extraction logic:

function fetchDb2Records(PDO $db2Conn): array
{
    // Adjust your query to fetch the records you need (include any filters here)
    $query = "SELECT id, data_field_1, data_field_2, last_updated FROM your_db2_table";
    $stmt = $db2Conn->prepare($query);
    $stmt->execute();
    
    // Return an array of associative arrays for easy processing
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

2. Process & Validate Records

Handle data transformation, expiry checks, and validation here—keep DB logic separate from data processing:

function processRecords(array $rawRecords): array
{
    $processed = [];
    $expiryThreshold = strtotime("-30 days"); // Example: mark records older than 30 days as stale

    foreach ($rawRecords as $record) {
        // Skip stale records (or flag them for deletion later)
        if (strtotime($record['last_updated']) < $expiryThreshold) {
            continue;
        }

        // Transform DB2 data to match MySQL schema (adjust fields as needed)
        $processed[] = [
            'external_id' => $record['id'],
            'mysql_field_1' => $record['data_field_1'],
            'mysql_field_2' => $record['data_field_2'],
            'sync_timestamp' => date('Y-m-d H:i:s')
        ];
    }

    return $processed;
}

3. Batch UPSERT to MySQL

Instead of looping individual insert/update queries, use a batch UPSERT with ON DUPLICATE KEY UPDATE—this drastically reduces database round-trips:

function batchUpsertMysqlRecords(PDO $mysqlConn, array $processedRecords): bool
{
    if (empty($processedRecords)) {
        return true; // Nothing to do
    }

    // Build batch insert query
    $fields = array_keys($processedRecords[0]);
    $placeholders = rtrim(str_repeat('(' . implode(',', array_fill(0, count($fields), '?')) . '),', count($processedRecords)), ',');
    
    $query = sprintf(
        "INSERT INTO your_mysql_table (%s) VALUES %s ON DUPLICATE KEY UPDATE %s",
        implode(',', $fields),
        $placeholders,
        implode(',', array_map(fn($field) => "$field = VALUES($field)", $fields))
    );

    // Flatten the processed records into a single array for parameter binding
    $params = [];
    foreach ($processedRecords as $record) {
        $params = array_merge($params, array_values($record));
    }

    try {
        $stmt = $mysqlConn->prepare($query);
        return $stmt->execute($params);
    } catch (PDOException $e) {
        // Handle error (log it, rethrow, etc.)
        error_log("Batch UPSERT failed: " . $e->getMessage());
        return false;
    }
}

4. Delete Stale Records in MySQL

Handle stale record cleanup as a separate, focused function:

function deleteStaleMysqlRecords(PDO $mysqlConn, string $expiryThreshold): int
{
    $query = "DELETE FROM your_mysql_table WHERE sync_timestamp < ?";
    $stmt = $mysqlConn->prepare($query);
    $stmt->execute([$expiryThreshold]);

    // Return number of deleted records for logging
    return $stmt->rowCount();
}

Step 2: Assemble the Main Workflow #

Now you can tie everything together with a clean, readable main function—no more tangled sequential code:

function syncDb2ToMysql(PDO $db2Conn, PDO $mysqlConn): void
{
    // Wrap in transaction for atomicity (all changes succeed or fail together)
    $mysqlConn->beginTransaction();

    try {
        // Fetch, process, and sync records
        $rawRecords = fetchDb2Records($db2Conn);
        $processedRecords = processRecords($rawRecords);
        batchUpsertMysqlRecords($mysqlConn, $processedRecords);

        // Clean up stale records (adjust threshold as needed)
        $expiryDate = date('Y-m-d H:i:s', strtotime("-30 days"));
        $deletedCount = deleteStaleMysqlRecords($mysqlConn, $expiryDate);
        error_log("Deleted $deletedCount stale records from MySQL");

        $mysqlConn->commit();
        error_log("Sync completed successfully!");
    } catch (PDOException $e) {
        $mysqlConn->rollBack();
        error_log("Sync failed: " . $e->getMessage());
        throw $e; // Re-throw if you want upstream error handling
    }
}

// Usage example (initialize your PDO connections first)
// $db2Conn = new PDO("ibm:dbname=your_db2_db", "user", "pass");
// $mysqlConn = new PDO("mysql:host=localhost;dbname=your_mysql_db", "user", "pass");
// syncDb2ToMysql($db2Conn, $mysqlConn);

Key Benefits of This Structure #

• Better efficiency: Batch operations cut down on database round-trips compared to looping single queries.
• Easier maintenance: Each function has a clear purpose—debugging or modifying one part won’t break the whole script.
• Testability: You can test individual functions (e.g., processRecords) with mock data without touching the database.
• Reduced redundancy: No more repeated PDO boilerplate across your sequential code.

If you have specific snippets from your original script that you’re unsure how to refactor, feel free to share them and we can tweak this approach further!

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