SQL批量插入与表更新技术求助：从Customer_Table到Trade_Table

Hey Eric, I see you’ve got the calculation part sorted, but bulk operations can be tricky—let’s fix that. Below is a complete, transaction-safe PHP solution using PDO (better for security and batch handling than mysqli) that covers all three of your requirements:

Step 1: Fetch Eligible Customers & Compute Values #

First, we’ll pull all customers with Balance > 100 and calculate the required metrics (Stake, Turnover, ROI) in one query. We’ll also generate the random Multiplying_Factor (5 < x < 12) upfront for the later update.

Step 2: Bulk Insert into Trade_Table #

Instead of inserting one row at a time, we’ll use prepared statements with batch binding to insert all records efficiently. For the 6-digit unique TradeID, we’ll use a database-generated auto-increment value formatted to 6 digits—this is the most reliable way to avoid duplicates without extra code.

Step 3: Bulk Update Customer_Table #

We’ll use a single UPDATE query with CASE WHEN to update all eligible customers in one go—way faster than looping through each record individually.

Complete PHP Code Example #

<?php
// Database connection (adjust credentials to your setup)
$dsn = 'mysql:host=your_host;dbname=your_db;charset=utf8mb4';
$username = 'your_user';
$password = 'your_pass';

try {
    $pdo = new PDO($dsn, $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo->beginTransaction(); // Ensure all operations succeed or fail together

    // Step 1: Fetch eligible customers and compute values
    $fetchQuery = "
        SELECT 
            CustomerID,
            Balance,
            Multiplying_Factor,
            Balance / 2 AS Stake,
            (Balance / 2) * Multiplying_Factor AS Turnover,
            ((Balance / 2) * Multiplying_Factor) - (Balance / 2) AS ROI,
            FLOOR(RAND() * 7) + 6 AS New_Multiplying_Factor -- Random integer 6-11 (fits 5 < x <12)
        FROM Customer_Table
        WHERE Balance > 100
    ";
    $stmt = $pdo->query($fetchQuery);
    $customers = $stmt->fetchAll(PDO::FETCH_ASSOC);

    if (empty($customers)) {
        echo "No eligible customers found.";
        $pdo->rollBack();
        exit;
    }

    // Step 2: Bulk insert into Trade_Table
    // Prepare insert statement (assumes Trade_Table has columns: CustomerID, Stake, Turnover, ROI, Status, Timestamp)
    // TradeID is handled by the database (see note below)
    $insertQuery = "
        INSERT INTO Trade_Table (CustomerID, Stake, Turnover, ROI, Status, Timestamp)
        VALUES (:customer_id, :stake, :turnover, :roi, :status, :timestamp)
    ";
    $insertStmt = $pdo->prepare($insertQuery);

    $currentTime = date('Y-m-d H:i:s');
    $status = 'Pending';

    // Bind and execute for each customer (safe from SQL injection)
    foreach ($customers as $customer) {
        $insertStmt->bindParam(':customer_id', $customer['CustomerID']);
        $insertStmt->bindParam(':stake', $customer['Stake']);
        $insertStmt->bindParam(':turnover', $customer['Turnover']);
        $insertStmt->bindParam(':roi', $customer['ROI']);
        $insertStmt->bindParam(':status', $status);
        $insertStmt->bindParam(':timestamp', $currentTime);
        $insertStmt->execute();
    }

    // Step 3: Bulk update Customer_Table
    $balanceCases = [];
    $factorCases = [];
    $customerIds = [];
    $updateParams = [];

    foreach ($customers as $customer) {
        $idPlaceholder = ":id_{$customer['CustomerID']}";
        $balanceCases[] = "WHEN {$idPlaceholder} THEN :stake_{$customer['CustomerID']}";
        $factorCases[] = "WHEN {$idPlaceholder} THEN :factor_{$customer['CustomerID']}";
        $customerIds[] = $idPlaceholder;

        // Bind update parameters
        $updateParams[$idPlaceholder] = $customer['CustomerID'];
        $updateParams[":stake_{$customer['CustomerID']}"] = $customer['Stake'];
        $updateParams[":factor_{$customer['CustomerID']}"] = $customer['New_Multiplying_Factor'];
    }

    $updateQuery = "
        UPDATE Customer_Table
        SET 
            Balance = CASE CustomerID " . implode(' ', $balanceCases) . " END,
            Multiplying_Factor = CASE CustomerID " . implode(' ', $factorCases) . " END
        WHERE CustomerID IN (" . implode(', ', $customerIds) . ")
    ";
    $updateStmt = $pdo->prepare($updateQuery);
    $updateStmt->execute($updateParams);

    // Commit all changes
    $pdo->commit();
    echo "Successfully processed " . count($customers) . " customers.";

} catch (PDOException $e) {
    $pdo->rollBack();
    die("Error: " . $e->getMessage());
}
?>

Key Notes: #

• Transaction Safety: The beginTransaction(), commit(), and rollBack() methods ensure no partial updates or inserts—either all operations succeed, or none do.
• 6-Digit TradeID Setup: To let MySQL auto-generate unique 6-digit IDs, run this SQL on your Trade_Table:

  -- Add an auto-increment integer column (if not exists)
  ALTER TABLE Trade_Table ADD COLUMN Trade_Int INT AUTO_INCREMENT PRIMARY KEY;
  -- Add a computed 6-digit TradeID column
  ALTER TABLE Trade_Table ADD COLUMN TradeID CHAR(6) AS (LPAD(Trade_Int, 6, '0')) STORED UNIQUE;
  

  This removes the need to handle ID generation in PHP entirely.
• Random Multiplying Factor: The query uses FLOOR(RAND() *7)+6 to get integers between 6 and 11. If you want decimal values, use RAND()*7 +5 for a range of 5.0 to 11.999....

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