MySQL中如何实现数据的批量插入与更新？-mysql教程-PHP中文网

mysql批量插入和更新应避免单条循环，优先采用多值insert、insert on duplicate key update、case when或临时表join等方案；每批控制在1000行内，配合事务、禁用约束及load data infile可大幅提升性能。

mysql中如何实现数据的批量插入与更新？

MySQL批量插入和更新不能靠循环单条执行，否则性能断崖式下跌，锁表时间长，还容易被DBA盯上。

INSERT ... VALUES 多行插入最常用也最容易翻车

这是开发中最先想到的方式，语法简单：INSERT INTO t (a, b) VALUES (1,'x'), (2,'y'), (3,'z');。但要注意三点：

单条 INSERT 语句最多支持约 1000 行（取决于 max_allowed_packet 和字段长度），超了会报错 Packets larger than max_allowed_packet are not allowed
如果表有唯一索引或主键冲突，整条语句会失败，不会部分插入 —— 想忽略重复就用 INSERT IGNORE，想覆盖就用 ON DUPLICATE KEY UPDATE
字段值里含单引号、反斜杠等特殊字符时，必须由客户端做转义（如 PHP 的 mysqli_real_escape_string 或 PDO 的预处理），直接拼字符串极易 SQL 注入

INSERT ... ON DUPLICATE KEY UPDATE 是安全更新的主力

当你要“有则更新、无则插入”，又不想写事务+SELECT判断，INSERT ... ON DUPLICATE KEY UPDATE 是首选。例如：

INSERT INTO users (id, name, score) VALUES (101, 'Alice', 85), (102, 'Bob', 92)
ON DUPLICATE KEY UPDATE name = VALUES(name), score = VALUES(score);

关键点：

只对触发唯一约束（PRIMARY KEY 或 UNIQUE KEY）的冲突行生效，普通 WHERE 不起作用
VALUES(col) 表示本次 INSERT 中该列打算插入的值，不是当前行旧值
不支持在 UPDATE 子句中引用其他列的旧值做计算（比如 score = score + 10），得改用 UPDATE ... JOIN 或临时表
如果表没定义任何唯一约束，这条语句就退化成普通 INSERT，冲突逻辑完全不触发

UPDATE + CASE WHEN 适合小批量条件更新

当你有一组 ID 和对应的新值（比如前端传来的排序序号），且数量在几百以内，CASE WHEN 是最轻量、原子性最强的做法：

MySQL

编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。

下载

UPDATE products 
SET sort_order = CASE id 
  WHEN 1001 THEN 1 
  WHEN 1002 THEN 2 
  WHEN 1003 THEN 3 
END 
WHERE id IN (1001, 1002, 1003);

注意：

WHERE id IN (...) 不是可选项，而是必须项 —— 否则全表扫描+全表更新，性能灾难
每个 WHEN 分支只能更新一个字段的值；要更新多个字段，得为每个字段单独写一套 CASE，比如同时更新 name 和 status
超过 1000 个 ID 时，SQL 字符串过长、解析慢，建议拆成多批或换临时表方案

临时表 + JOIN 更新适合万级以上的数据修正

真正大批量更新（比如 5 万条用户状态重置），别硬拼 SQL 字符串。建临时表再关联更新更稳：

CREATE TEMPORARY TABLE tmp_updates (id BIGINT PRIMARY KEY, new_status TINYINT);
INSERT INTO tmp_updates VALUES (1001, 2), (1002, 1), ..., (50000, 3);
UPDATE users u JOIN tmp_updates t ON u.id = t.id SET u.status = t.new_status;

这个流程的关键细节：