UPDATE users SET age = 30 WHERE name = 'Alice'; UPDATE users SET age = 25 WHERE name = 'Bob'; UPDATE users SET age = 35 WHERE name = 'Charlie';
以上代码会导致并发性问题,因为多个更新语句可能会同时执行,导致数据错乱。
解决办法:
1、使用锁定
LOCK TABLES users WRITE; UPDATE users SET age = 30 WHERE name = 'Alice'; UPDATE users SET age = 25 WHERE name = 'Bob'; UPDATE users SET age = 35 WHERE name = 'Charlie'; UNLOCK TABLES;
2、使用事务
START TRANSACTION; UPDATE users SET age = 30 WHERE name = 'Alice'; UPDATE users SET age = 25 WHERE name = 'Bob'; UPDATE users SET age = 35 WHERE name = 'Charlie'; COMMIT;
一般使用事务,PHP操作:
$sqls=["START TRANSACTION"];//开启事务避免出错 for($datas as $d){ $sql="............"; array_push($sqls,$sql); } array_push($sqls,"COMMIT"); $sqls=implode(";",$sqls); $result=$pdo->query($sqls);
或者:
$db->query('START TRANSACTION'); $db->query('update member set money=money+'.$money.' where memberId='.$memberId); $db->query('insert into mem_log(money) values('.$money.')'); $db->query('commit');
或者:
$stmt = $pdo->prepare("INSERT INTO a_salary_test (name,dpname,year,month) VALUES (?,?,?,?)"); $stmt->execute(array("ha","ddd",2000,1)); $stmt->execute(array("ha2","ddd2",2002,2)); //批量 $pdo->beginTransaction(); $stmt->execute(array("ha3","ddd",2000,1)); $stmt->execute(array("ha4","ddd2",2002,2)); $pdo->commit(); //execute是执行预处理 //$pdo->exec是直接执行
try { // 开启事务 $pdo->beginTransaction(); // 执行一些SQL操作 $pdo->exec("INSERT INTO table1 (column1) VALUES ('value1')"); $pdo->exec("UPDATE table2 SET column2 = 'value2' WHERE id = 1"); // 提交事务 $pdo->commit(); } catch (Exception $e) { // 发生错误,回滚事务 $pdo->rollBack(); // 处理错误,例如打印错误信息 echo "Error: " . $e->getMessage(); }
大量插入建议:
<?php // 数据库连接配置 $dsn = 'mysql:host=localhost;dbname=test'; $username = 'root'; $password = ''; try { // 创建PDO对象 $pdo = new PDO($dsn, $username, $password); // 数据准备 $data = [ ['name' => '张三', 'age' => 20], ['name' => '李四', 'age' => 25], ['name' => '王五', 'age' => 30], ]; // 开启事务 $pdo->beginTransaction(); // 预处理插入语句 $stmt = $pdo->prepare('INSERT INTO users (name, age) VALUES (:name, :age)'); // 执行批量插入 foreach ($data as $item) { $stmt->bindValue(':name', $item['name']); $stmt->bindValue(':age', $item['age']); $stmt->execute(); } // 提交事务 $pdo->commit(); echo "批量插入成功!"; } catch (PDOException $e) { // 回滚事务 $pdo->rollBack(); echo "插入失败:" . $e->getMessage(); }
<?php $stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (?, ?)"); $stmt->bindParam(1, $name); $stmt->bindParam(2, $value); // 插入一行 $name = 'one'; $value = 1; $stmt->execute(); // 用不同的值插入另一行 $name = 'two'; $value = 2; $stmt->execute(); ?>
大量修改建议:
<?php // 数据库连接配置 $dsn = 'mysql:host=localhost;dbname=test'; $username = 'root'; $password = ''; try { // 创建PDO对象 $pdo = new PDO($dsn, $username, $password); // 数据准备 $data = [ ['id' => 1, 'name' => '张三'], ['id' => 2, 'name' => '李四'], ['id' => 3, 'name' => '王五'], ]; // 开启事务 $pdo->beginTransaction(); foreach ($data as $item) { // 预处理更新语句 $stmt = $pdo->prepare('UPDATE users SET name = :name WHERE id = :id'); $stmt->bindValue(':name', $item['name']); $stmt->bindValue(':id', $item['id']); $stmt->execute(); } // 提交事务 $pdo->commit(); echo "批量更新成功!"; } catch (PDOException $e) { // 回滚事务 $pdo->rollback(); echo "更新失败:" . $e->getMessage(); }