简介PDF 文档专门讲解 MySQL 批量更新多条记录的操作思路面向需要按不同条件为多行数据逐一赋值的开发者。内容源自作者真实工作场景先使用 INSERT 一次性导入 name 字段后续补充 package 字段时借助 UPDATE 语句与 CASE 表达式根据 id 逐一映射对应值再利用 WHERE IN 限定范围从而实现一次更新多条记录。资料同时给出 PHP 动态拼接 SQL 的示例代码并指出旧版 mysql_* 函数已废弃、建议改用 mysqli 或 PDO可帮助读者避开常见兼容性与安全问题。资源包共 1 个 PDF 文件大小约 40KB内容集中简洁适合需要快速理解 MySQL 批量更新语法、处理文本导入后补录字段的开发人员参考。目前该资源已有 5746 人学习下载说明其解决此类需求的实用价值较高。1. 一次 UPDATE 多条记录批量更新场景下的第一道坎这个标题几乎每个用过 MySQL 的开发者都撞上过后台页面上传了一百条商品的售价业务要求一次性改完。第一反应写循环一百条 UPDATE 逐条执行看起来没问题。等数量变成五千条、五万条循环耗时从一秒变成几十秒数据库连接被反复占用更麻烦的是每执行一条 UPDATE 都等同于开了一个小事务。这才是“一次更新多条记录”真正要解决的问题把多条 UPDATE 合并成一条 UPDATE 语句利用 CASE WHEN 或 JOIN 一次完成写入。下面按实际落地顺序讲语句怎么写、参数怎么调、锁和安全模式有哪些坑、大批量时怎么分片。2. 用 CASE WHEN 合成一次 UPDATE语法、动态构造与适用边界2.1 CASE WHEN 更新多条记录的最小可运行 SQL第一种我见得最多通过CASE WHEN或者 MySQL 里的CASE 字段 WHEN把键值映射写进SET子句。假设有一张商品表要批量改 id1、2、3 的价格-- 目标表product -- 字段id, name, price, updated_at -- 要批量改 id1,2,3 的 price UPDATE product SET price CASE id WHEN 1 THEN 10.50 WHEN 2 THEN 20.00 WHEN 3 THEN 30.00 END WHERE id IN (1,2,3);先看执行逻辑MySQL 在扫描product时会先通过WHERE id IN (1,2,3)锁定目标行再对每一行取id的值进入CASE id WHEN ...匹配。命中的行用新 price 更新未命中的行不会出现在结果集里。这种简单 CASE 语法适合单列多值映射如果更新条件不是等值匹配而是一组逻辑表达式就要换用搜索 CASECASE WHEN 条件 THEN 值 ELSE 原字段 END。在 MySQL update 语法里有一个参数会影响这条语句能否跑通sql_safe_updates。客户端默认可能开启安全更新模式如果WHERE条件里没有使用主键或索引列直接报ERROR 1175。上面这条 SQL 用id主键做条件能绕过这个限制如果为了省事写成WHERE price 0即使逻辑上没错也可能被拦截。第 4 章专门讲这个坑。2.2 用代码拼接 CASE 更新来自后端接口的落地路径实际业务里要更新的数据大概率不是手写 SQL而是从接口或 CSV 来的。这时最常见的做法是用后端代码生成CASE表达式再把参数批量注入。拿 Python 举例import pymysql # 注意实际场景中 ids 和 prices 来自接口或 CSV ids [1, 2, 3] prices [10.50, 20.00, 30.00] # 构建 WHEN id THEN price 片段 case_str .join( fWHEN {id_} THEN {price} for id_, price in zip(ids, prices) ) sql f UPDATE product SET price CASE id {case_str} END WHERE id IN ({,.join(map(str, ids))}) conn pymysql.connect(host127.0.0.1, userapp, password***) cursor conn.cursor() cursor.execute(sql) conn.commit()这里有一层取舍CASE的数值和WHERE id IN (...)需要拼接字符串不能完全参数化。如果价格来自用户输入拼接前必须强校验类型比如强制float(price)否则 SQL 注入风险会直接落到线上。另一个必须关注的参数是 MySQL 端的max_allowed_packet动态拼接出的 SQL 很长默认 4MB 或 64MB 可能直接触发ERROR 1153 Got a packet bigger than max_allowed_packet。一般我会把单条 CASE 更新的记录数控制在 1000 条以内执行前先打印 SQL 预览确认长度后再说。2.3 适用边界记录数量、字段数量与包大小并不是所有“多条更新”都适合无脑 CASE。我习惯先看三个指标更新行数、更新字段数、来源数据形式。指标数量范围建议方案更新行数11000CASE WHEN 足够简单直接更新行数100010000CASE WHEN 可用但注意 max_allowed_packet更推荐 JOIN更新行数10000不要硬拼 CASE改用临时表 UPDATE JOIN 分片执行更新字段数12 个CASE 表达式好写更新字段数3 个以上拼接 CASE 的代价成倍增加考虑 JOIN很多人误以为 CASE WHEN 能覆盖所有批量更新实际上它有两个天然短板第一每条记录都要写一个 WHENSQL 作为文本传输时冗余大第二CASE 更新没法利用数据来源表的结构如果数据来自 Excel十万行拼出的 SQL 可能几十 MB远远超过合理范围。因此 CASE WHEN 更适合“几百条以内、键值对明确、临时一改”的场景再多就要引入 JOIN 方案。3. 用 UPDATE...JOIN 一次更新多条记录映射表与执行计划3.1 先建临时映射表再关联更新当数据来源于一张 CSV或者要更新多个字段时我一般会先把数据放进临时表再用UPDATE ... JOIN完成一次更新。这种写法的好处是更新逻辑和数据结构对齐代码可读性和执行性能都更稳定。-- 第一步建临时表存 id 和新值 CREATE TEMPORARY TABLE tmp_product_price ( id INT PRIMARY KEY, price DECIMAL(10,2) NOT NULL ); -- 第二步把要更新的数据灌进临时表 INSERT INTO tmp_product_price (id, price) VALUES (1, 10.50), (2, 20.00), (3, 30.00); -- 第三步一条 UPDATE 关联两张表 UPDATE product p INNER JOIN tmp_product_price t ON p.id t.id SET p.price t.price;第三步是整个方案的灵魂UPDATE product p INNER JOIN tmp_product_price t ON p.id t.id表示只更新两张表里匹配得上的行不匹配的行碰都不碰。这和 CASE 方案有本质区别CASE 方案如果不写WHERE会扫描全表而JOIN方案的匹配集合天然由临时表记录数限定。临时表有两种存储形式TEMPORARY TABLE存在会话内存连接断开自动消失如果数据量特别大也可以改成普通映射表更新完 DELETE。区别在于普通表可以跨会话复用但需要手动清理。实际上最常见的做法是从外部数据源把 id 和值导入临时表再执行 JOIN 更新如果数据在 CSV 里可以用LOAD DATA LOCAL INFILE直接灌入临时表速度比一条条 INSERT 快很多。3.2 JOIN 更新最怕没有索引执行计划检查UPDATE ... JOIN实现虽然优雅但我见过不少翻车现场几千行临时表 JOIN 几百万行的主表UPDATE 语句直接把主表扫了一遍。原因是 MySQL 对 UPDATE JOIN 的优化逻辑和 SELECT JOIN 不一样如果临时表 JOIN 字段没有索引或者主表没有合适索引优化器会退化成全表扫描。所以执行之前我会先看执行计划。MySQL 8.0 可以EXPLAIN UPDATE低版本不支持的场景就写一条等价的 SELECT-- 通过 EXPLAIN 观察两张表的访问方式 EXPLAIN SELECT p.id, t.price FROM product p INNER JOIN tmp_product_price t ON p.id t.id; -- 重点看 type 列 -- product 最好是 ref/eq_ref且 key 列显示 PRIMARY如果看到typeALL出现在product这一行说明主表在被逐行扫描。第一反应是给临时表加索引ALTER TABLE tmp_product_price ADD PRIMARY KEY (id);product.id本身是主键所以执行计划里product的 ref 通常没问题临时表上的id需要与它形成对照。要注意的是InnoDB 临时表在会话里默认可能没有主键我建表时直接写id INT PRIMARY KEY这一步经常被忽略。提示临时表的索引定义会影响更新性能也会影响优化器是否选择 product 主键去匹配。建表时就把 JOIN 字段索引写好比事后 ALTER 更省事。3.3 多字段批量更新JOIN 比 CASE 更好的理由很多后台需求不是只改一个价格而是价格、库存、状态一起变。CASE 方案需要在一个 SET 里写两个甚至三个 CASE 表达式每个表达式都要重复一遍所有 WHENSQL 瞬间又臭又长。JOIN 方案没有这个烦恼UPDATE product p INNER JOIN tmp_product_update t ON p.id t.id SET p.price t.price, p.stock t.stock, p.status t.status, p.updated_at CURRENT_TIMESTAMP;只要临时表里有id、price、stock、status四个字段更新目标表就照抄字段名。这里有一个容易踩的细节SET子句如果直接引用临时表字段不需要给字段加前缀但如果主表字段名和临时表字段名完全相同就必须给两张表都写别名否则 MySQL 可能报Column price in field list is ambiguous。上面的写法用p.和t.区分开既避免歧义也让后面排查的人一眼看清来源和去向。与逐条子查询UPDATE product SET price (SELECT ... WHERE id product.id)相比JOIN 方案只需扫描匹配范围的索引不会触发每行子查询子查询方式写起来简单但大量行时性能衰减明显而且更容易被SQL_SAFE_UPDATES拦截。我的结论是超过几百条且数据来源结构化时直接选UPDATE...JOIN不要用子查询硬扛。4. 多条记录 update 的避坑清单安全模式、锁与常见报错4.1 避坑一ERROR 1175 是安全模式在保护你不是语句写错现象执行一条多条记录的 UPDATEMySQL 直接报错Error Code: 1175. You are using safe update mode, and you tried to update a table without a WHERE that uses a KEY column.原因MySQL 客户端或 Workbench 默认把sql_safe_updates置为 1禁止不带主键条件的 UPDATE 和 DELETE。这个机制本来是为了防止不带 WHERE 的误操作但很多多行更新写法会被拦下尤其是临时表 JOIN 时如果优化器没把主键条件识别出来也会被同一个规则拒绝。解决优先在 UPDATE 里显式使用主键关联。比如联表条件写成ON p.id t.idWHERE部分可以再加AND p.id IS NOT NULL让优化器明确这是主键范围扫描。如果确认这条 SQL 就是线性的业务操作临时在会话里关闭安全模式SET SESSION sql_safe_updates 0; UPDATE product p INNER JOIN tmp_product_price t ON p.id t.id SET p.price t.price; SET SESSION sql_safe_updates 1;注意必须在同一个会话里做完关闭、执行、再打开别把关闭命令留在脚本里。很多线上事故就是这么来的脚本作者把SET SQL_SAFE_UPDATES0放到全局后面所有不带条件的 DELETE 全部放行。4.2 避坑二CASE 没有 ELSE会把不匹配行更新成 NULL现象用 CASE 更新 1 号和 2 号商品的库存执行成功后查 3 号商品发现它的价格变成 NULL而 3 号明明不在意图里。原因SET price CASE id WHEN 1 THEN 10.00 WHEN 2 THEN 20.00 END这个 CASE 表达式没有 ELSE。MySQL 对不匹配的值默认返回 NULL于是 3 号商品被“顺手”更新成 NULL。问题在于 CASE 表达式不管你是否在 WHERE 里排除该行只要某一行落到 WHERE 范围内表达式就会参与求值。解决所有 CASE 更新统一加 ELSE用原字段兜底UPDATE product SET price CASE id WHEN 1 THEN 10.00 WHEN 2 THEN 20.00 ELSE price -- 保持原值 END WHERE id IN (1,2,3);这属于完全避免风险的习惯凡是在 SET 里写 CASE 的一律写 ELSE 原字段若多个字段都要更新每个字段的 CASE 都必须写 ELSE。如果数据源来自外部映射表CASE 的键值集合可能和 WHERE 的集合不一致宁可多写一个WHERE id IN (...)把范围卡死也不要去赌 CASE 内部逻辑。4.3 避坑三UPDATE...JOIN 时临时表没索引秒变分钟现象用第 3 章的 JOIN 方案更新 5 万行数据第一次执行跑了 4 分钟业务侧直接超时。检查发现目标表product有 100 万行临时表tmp_product_price用了普通内存临时表但id字段没有索引。原因UPDATE product p INNER JOIN tmp_product_price t ON p.id t.idMySQL 要拿product的每一行去临时表里找匹配的id。临时表没有主键时内连接变成嵌套循环在 MySQL 5.7 上每条主表记录都要扫一遍临时表总代价是 100 万乘以 5 万的一个灾难级放大。解决建临时表时就给关联字段定义索引CREATE TEMPORARY TABLE tmp_product_price ( id INT PRIMARY KEY, price DECIMAL(10,2) NOT NULL, ) ENGINEInnoDB;至少保证 JOIN 字段带主键或普通索引。很多人依赖TEMPORARY TABLE的“自动消失”特性却忽略了索引定义同样重要。临时表在会话结束后整个表消失但索引不会自动生成必须建表时指定。注意使用EXPLAIN SELECT检查的时候如果临时表为空优化器可能忽略索引统计插入数据后再看执行计划更接近真实情况。4.4 避坑四长事务放大了锁和主从复制延迟现象凌晨脚本要更新 20 万行执行时间 30 秒期间大量 SELECT 报 lock wait timeout主从架构里 Slave 延迟从 0 涨到 300 秒。原因一个 20 万行的 UPDATE 放在一个事务里InnoDB 会持有 20 万行行锁直到 COMMIT期间任何主键范围重叠的写操作都会排队同时这个单一事务写入 binlog 后复制线程要连续应用很久延迟自然拉高。解决把一条大事务拆成多条小事务也就是分片更新。片和片之间加一个短暂休眠避免复制延迟累积。常见做法是拿到表中要更新的主键 ID分组成 1000 或 5000 一批去执行逐步提交。# 伪代码按 ID 切片每批一个短事务 ids fetch_all_target_ids() for i in range(0, len(ids), 1000): batch ids[i:i 1000] update_batch_by_id(batch) # 每条 SQL 只更新 1000 行 time.sleep(0.1) # 给从库留出应用 binlog 的时间对生产环境来说宁可每批消耗多一点也不要让一个长事务拖垮整个实例。这里用到的思路和版本无关两边的执行计划越简单分片越安全。5. 验证与回滚把多条记录更新做成可重启的批量任务5.1 更新前先做反向核对我自己的习惯是多条记录更新之前所有要改的数据先落在一张临时映射表里执行验证 SELECT。以tmp_product_update为例至少要验证三点-- 1. 临时表有没有数据防止映射表被清空还去执行 UPDATE SELECT COUNT(*) FROM tmp_product_update; -- 2. 临时表里的 id 是否在主表中不存在 SELECT COUNT(*) FROM tmp_product_update t LEFT JOIN product p ON p.id t.id WHERE p.id IS NULL; -- 3. 预演受影响行数和业务预期核对 SELECT COUNT(*) AS affected FROM product p INNER JOIN tmp_product_update t ON p.id t.id;第二个查询尤其重要。结果不为 0 时说明临时表里的 ID 在主表中不存在UPDATE JOIN会自动忽略这些行但业务可能以为全部更新成功最后对账才发现少了几条。5.2 快照表与回滚 SQL如果希望更新过程可回滚我会把要改的字段先备份到一张带日期的快照表而不是直接备份整张业务表CREATE TABLE product_price_backup_20250401 AS SELECT id, price, stock, status, updated_at FROM product;出错时用一条 UPDATE JOIN 把备份表映射回去UPDATE product p INNER JOIN product_price_backup_20250401 b ON p.id b.id SET p.price b.price, p.stock b.stock;这个方案的适用场景是短期、单任务回滚备份表比mysqldump轻量适合只改部分字段的批量更新。5.3 保留的一个习惯给批量更新加切片最后一条是长期踩坑换来的经验。无论 CASE 还是 JOIN大批量更新都必须能停下来。我一般会在后端批量函数里保留一个 batch_size 参数默认 2000执行完一批看进度如果线上主从延迟报警立即把 batch_size 调小或者暂停循环。更新不是一把梭而是分批推进、可随时回滚的过程。希望这点思路能帮你在下一次多条记录 UPDATE 时少走弯路。本文还有配套的精品资源点击获取
