PHP中MySQL UPDATE语句如何为IN子句元素设对应不同字段值

Great question! When you need to update multiple rows with different values per row instead of applying the same values to all matching rows, your original single UPDATE statement won't cut it. Let's break down the most efficient and secure ways to handle this in PHP with SQL:

Method 1: Use CASE WHEN for Conditional Updates #

This approach builds a conditional UPDATE statement that maps each unit_ID to its specific currentTime and mapID values. It works with most SQL databases and doesn't require special constraints on your table.

First, structure your update data into an array where each entry contains the unit_ID and its corresponding values:

// Example structured update data (replace with your actual values)
$unitUpdates = [
    ['unit_id' => 12, 'currentTime' => 1699999999, 'mapID' => 3],
    ['unit_id' => 45, 'currentTime' => 1700000000, 'mapID' => 7],
    ['unit_id' => 78, 'currentTime' => 1700000100, 'mapID' => 2],
    // Add up to 100 entries here
];

// Your existing shared values
$attackStartTime = 123456789;
$enemy = 98765;

Then build the conditional CASE clauses and execute the query with parameter binding (critical for avoiding SQL injection):

// Extract unit IDs for the WHERE clause
$unitIds = array_column($unitUpdates, 'unit_id');

// Build CASE segments for move_end and map_ID
$caseMoveEnd = [];
$caseMapId = [];
$params = [];

foreach ($unitUpdates as $update) {
    // Add CASE conditions for move_end
    $caseMoveEnd[] = "WHEN ? THEN ?";
    $params[] = $update['unit_id'];
    $params[] = $update['currentTime'];
    
    // Add CASE conditions for map_ID
    $caseMapId[] = "WHEN ? THEN ?";
    $params[] = $update['unit_id'];
    $params[] = $update['mapID'];
}

// Assemble the full CASE statements
$moveEndClause = "move_end = CASE unit_ID " . implode(' ', $caseMoveEnd) . " ELSE move_end END";
$mapIdClause = "map_ID = CASE unit_ID " . implode(' ', $caseMapId) . " ELSE map_ID END";

// Add shared values to parameters
$params[] = $attackStartTime;
$params[] = $enemy;

// Build the full SQL query with placeholders for unit IDs
$sql = "UPDATE Units 
        SET $moveEndClause, 
            $mapIdClause, 
            attacking = ?, 
            unit_ID_affected = ?, 
            updated = NOW() 
        WHERE unit_ID IN (" . implode(',', array_fill(0, count($unitIds), '?')) . ")";

// Attach unit IDs to the parameter list
$params = array_merge($params, $unitIds);

// Execute with PDO (adjust for mysqli if you use that)
$stmt = $pdo->prepare($sql);
$stmt->execute($params);

How This Works: #

The CASE statements check each row's unit_ID and apply the corresponding currentTime/mapID only if there's a match. The ELSE move_end ensures rows not in your update list keep their original values. Parameter binding eliminates SQL injection risks (a big issue with your original string-concatenated approach).

Method 2: Use INSERT ... ON DUPLICATE KEY UPDATE (Requires Unique/Primary Key) #

If unit_ID is a primary key or has a unique index on your Units table, this method is more concise. It leverages MySQL's (and some other databases') ability to update existing rows when an insert would cause a duplicate key conflict.

Using the same $unitUpdates array from above:

// Define columns to insert/update
$columns = ['unit_ID', 'move_end', 'map_ID', 'attacking', 'unit_ID_affected', 'updated'];
$valuePlaceholders = [];
$params = [];

foreach ($unitUpdates as $update) {
    $valuePlaceholders[] = "(?, ?, ?, ?, ?, NOW())";
    $params[] = $update['unit_id'];
    $params[] = $update['currentTime'];
    $params[] = $update['mapID'];
    $params[] = $attackStartTime;
    $params[] = $enemy;
}

// Build the SQL query
$sql = "INSERT INTO Units (" . implode(',', $columns) . ")
        VALUES " . implode(',', $valuePlaceholders) . "
        ON DUPLICATE KEY UPDATE
            move_end = VALUES(move_end),
            map_ID = VALUES(map_ID),
            attacking = VALUES(attacking),
            unit_ID_affected = VALUES(unit_ID_affected),
            updated = NOW()";

// Execute with PDO
$stmt = $pdo->prepare($sql);
$stmt->execute($params);

How This Works: #

We first attempt to insert all the rows with their respective values. When a unit_ID already exists, the ON DUPLICATE KEY UPDATE clause kicks in and replaces the specified fields with the values we tried to insert. This is cleaner code and performs well for batch updates.

Key Notes: #

• Security: Never concatenate dynamic values directly into SQL strings. Always use parameter binding (as shown above) to prevent SQL injection. Your original code's direct use of $attackingUnits in the query is a security risk—these fixes eliminate that.
• Performance: Both methods handle 100 rows efficiently. The CASE WHEN method is more flexible if you don't have a unique constraint on unit_ID, while the INSERT method is more concise when you do.
• Preserving Existing Values: In the CASE WHEN method, the ELSE clause ensures non-targeted rows don't have their values overwritten. In the INSERT method, only the fields you list in the UPDATE clause will be modified.

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