MYSQL数据库的可变数量更新

Question

I have a PHP page that is interacting with a MYSQL database using PDO. I have a function that updates numerous fields in the database as requested. Since I do not know how many fields will be updated in advance, it is tricky to write a single query. Which of these methods is preferable (or is there another better way I do not know about)?

Query Building

$query = "UPDATE users SET ";
foreach ($changes as $field => $new_value)
{
    $valid_field = validate_field($field);
    $query .= "${valid_field} = :${valid_field} ";
}
$query = "WHERE id = :id ;";
// Prepare statement, bind values, execute, check for errors, etc

From what I have heard, this is not preferable. I tend to agree; this looks kinda ugly.

Transaction

$pdo_object->beginTransaction();
foreach ($changes as $field => $new_value)
{
    $valid_field = validate_field($field);
    $query = "UPDATE users SET ${valid_field} = :${valid_field} WHERE id = :id";
    // Prepare statement, bind values, execute, check for errors, etc
}
$pdo_object->commit();

This seems safer to me, but the way it is written it looks like it searches the table for the row with that ID many times rather than just once like the other query.

Is the Query Building method faster than the Transaction method? Should the Transaction method be used despite slower speed for safety/security reasons?

Answer 1

Actually, transactions (which, in MySQL, requires use of "InnoDB" tables ...) are quite efficient. The database engine will (probably ...) lock all of the storage pages that are covered by the query, and might delay actually writing the pages back to the store until after the transaction COMMITs.

My suggestion is simply: (a) don't be afraid of transactions when they seem to be called for, and (b), "just focus on simplicity and clarity." Write code that is obvious, easy to read, and easy to maintain. Then, presume that the SQL engine knows how to do its job. :-)