MySQLNotes

第 05 章：增删改查进阶

zjc 于 2026-01-05 发布

这是《MySQL 零基础实战指南》的独立章节版。本章从概念、实操和生产排查三个视角展开，代码块保留了原书可直接运行的版本。 基础 CRUD 只解决“能操作数据”。生产系统还要求操作幂等、并发安全、范围可控、失败可恢复。本章深入 INSERT、UPDATE、DELETE 的高级用法，并给出可落地的并发控制模式。

5.1 插入的完整性

普通插入：

INSERT INTO products
  (merchant_id, product_name, category, price, stock)
VALUES
  (3, 'Monitor', 'DIGITAL', 1299.00, 20);

插入前必须知道唯一约束。查看建表语句：

SHOW CREATE TABLE products\G

唯一约束决定重试语义：

  1. 无唯一键：重试可能插入重复数据；
  2. 有唯一键：冲突可以被数据库发现；
  3. 幂等表：用业务 ID 唯一控制重复请求。

5.2 批量插入

批量插入能减少网络往返，但批次不是越大越好。

INSERT INTO products
  (merchant_id, product_name, category, price, stock)
VALUES
  (1, 'Pear', 'FRUIT', 7.20, 60),
  (1, 'Grape', 'FRUIT', 15.80, 45),
  (1, 'Cheese', 'FOOD', 35.00, 25),
  (2, 'Kafka Book', 'BOOK', 99.00, 40),
  (3, 'USB Cable', 'DIGITAL', 29.90, 200);

建议：

  1. 单批几百到几千行，根据行大小压测；
  2. 不要超过 max_allowed_packet；
  3. 避免一个事务无限追加；
  4. 批量导入可临时关闭二级索引再重建；
  5. 程序中使用参数绑定和分批提交。

5.3 INSERT IGNORE

忽略唯一键冲突：

INSERT IGNORE INTO tags (tag_name)
VALUES ('HOT'), ('NEW');

如果 HOT 已存在，这条数据会被跳过。

风险：

  1. 不只忽略唯一键冲突，还可能把某些数据错误降级为警告；
  2. 客户端以为成功，实际没有插入；
  3. 屏蔽业务规则冲突；
  4. 不利于审计。

更推荐先明确冲突处理策略，再选择 ON DUPLICATE KEY UPDATE 或查询后插入。

5.4 ON DUPLICATE KEY UPDATE

存在则更新，不存在则插入：

CREATE TABLE IF NOT EXISTS product_visit_stats (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    stat_date DATE NOT NULL,
    product_id BIGINT UNSIGNED NOT NULL,
    visit_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
    updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3)
      ON UPDATE CURRENT_TIMESTAMP(3),
    PRIMARY KEY (id),
    UNIQUE KEY uk_date_product (stat_date, product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO product_visit_stats
  (stat_date, product_id, visit_count)
VALUES
  ('2026-08-25', 1, 1)
ON DUPLICATE KEY UPDATE
  visit_count = visit_count + 1;

多值插入时，每行冲突都会更新一次。如果同一个键在同一个语句里出现多次，最终结果依赖执行次数，容易被误解。

MySQL 8.0.19 起，VALUES() 在这类语句中逐渐弃用，新语法可以使用别名：

INSERT INTO product_visit_stats
  (stat_date, product_id, visit_count)
VALUES
  ('2026-08-25', 2, 1) AS new_row
ON DUPLICATE KEY UPDATE
  visit_count = product_visit_stats.visit_count + new_row.visit_count;

5.5 REPLACE INTO

REPLACE INTO 的行为是：遇到唯一键冲突时先删除旧行，再插入新行。

REPLACE INTO tags (id, tag_name)
VALUES (1, 'VERY_HOT');

它看似方便，风险却很大：

  1. 会删除再插入，不是原地更新；
  2. 可能触发级联删除或从库副作用；
  3. 未指定的字段会变成默认值；
  4. 自增 ID 可能变化；
  5. 二级索引维护成本更高。

生产中通常优先使用 INSERT ... ON DUPLICATE KEY UPDATE 或显式状态更新。

5.6 安全 UPDATE

简单更新：

UPDATE products
SET price = price * 0.9
WHERE category = 'DIGITAL'
  AND status = 1;

执行前确认影响范围：

SELECT COUNT(*) AS affected_rows
FROM products
WHERE category = 'DIGITAL'
  AND status = 1;

事务内确认：

START TRANSACTION;

UPDATE products
SET price = price * 0.9
WHERE category = 'DIGITAL'
  AND status = 1;

SELECT ROW_COUNT() AS affected_rows;

-- 人工确认后提交
COMMIT;

注意：ROW_COUNT() 应紧跟 DML 执行，中间执行其他语句会导致结果变化。

5.6.1 基于旧值的条件更新

防并发覆盖：

UPDATE products
SET stock = 10
WHERE id = 1
  AND stock = 8;

如果另一个请求已经把库存从 8 改成 7，这条 SQL 影响行数为 0，不会盲目覆盖。

5.6.2 版本号乐观锁

增加版本字段：

ALTER TABLE products
  ADD COLUMN version INT UNSIGNED NOT NULL DEFAULT 0;

更新：

UPDATE products
SET stock = 10,
    version = version + 1
WHERE id = 1
  AND version = 3;

应用判断：

影响行数 = 1：更新成功
影响行数 = 0：数据被其他事务修改，需要重试或提示冲突

乐观锁适合冲突概率不高的后台管理、配置更新和工作流状态变更。

5.7 扣库存模式

错误写法：

UPDATE products
SET stock = stock - 3
WHERE id = 1;

如果库存不足，可能变成负数，或因 UNSIGNED 报错。

正确写法：

UPDATE products
SET stock = stock - 3
WHERE id = 1
  AND stock >= 3;

应用根据影响行数判断：

1：扣减成功
0：库存不足或商品不存在

结合订单事务：

START TRANSACTION;

UPDATE products
SET stock = stock - 3
WHERE id = 1
  AND stock >= 3;

-- 如果上一条影响 0 行，应用立即 ROLLBACK

INSERT INTO orders
  (order_no, user_id, merchant_id, status, total_amount)
VALUES
  ('20260825000002', 1, 1, 'CREATED', 25.50);

COMMIT;

热点商品扣减可能形成行锁排队。常见方案：

  1. Redis 预扣库存，异步或事务内对账；
  2. 库存分片，把一行拆成多行；
  3. 队列串行化请求；
  4. 限流和削峰；
  5. 活动预热与库存分段释放。

无论使用什么方案，最终事实来源和资金相关结果必须清晰。

5.8 多表更新

MySQL 支持用 JOIN 更新：

UPDATE orders AS o
INNER JOIN users AS u ON u.id = o.user_id
SET o.status = 'RISK_HOLD'
WHERE u.status = 2
  AND o.status = 'CREATED';

多表更新可读性较好，但影响范围更难判断。执行前应先改成 SELECT 验证：

SELECT o.id, o.order_no, o.status, u.status AS user_status
FROM orders AS o
INNER JOIN users AS u ON u.id = o.user_id
WHERE u.status = 2
  AND o.status = 'CREATED';

5.9 DELETE 与 TRUNCATE

条件删除：

DELETE FROM order_items
WHERE order_id = 100;

删除全表：

DELETE FROM tags;

清空表：

TRUNCATE TABLE tags;

区别：

项目 DELETE TRUNCATE
条件 支持 WHERE 不支持
事务回滚 InnoDB 下可回滚，但大事务成本高 通常按 DDL 处理，不应依赖回滚
触发器 触发 DELETE 触发器 不触发行级 DELETE
自增 通常保留计数 通常重置
速度 逐行删除 更快
生产建议 分批删除 仅确认可清空时使用

5.10 大批量删除

删除大量历史数据时，不要一条 SQL 删除百万行。

风险：

  1. 长事务；
  2. 大量行锁；
  3. Undo 膨胀；
  4. 主从延迟；
  5. 磁盘空间不会立刻释放；
  6. 回滚时间极长。

分批删除模板：

DELETE FROM operation_logs
WHERE created_at < '2025-01-01'
ORDER BY id
LIMIT 1000;

应用循环执行，直到影响行数为 0。每批之间可以短暂睡眠，减少复制和磁盘压力。

更稳妥的方案是归档后删除：

1. 创建归档表或归档库；
2. 按主键或时间分片读取；
3. 归档端幂等插入；
4. 校验数量和抽样数据；
5. 分批删除在线表；
6. 记录归档任务水位。

5.11 软删除

标记删除：

UPDATE users
SET deleted = 1
WHERE id = 3;

查询时排除：

SELECT id, username
FROM users
WHERE deleted = 0;

软删除优点：

  1. 可恢复；
  2. 保留审计历史；
  3. 避免物理删除带来的关联问题。

缺点：

  1. 所有查询都要带条件；
  2. 唯一约束复杂；
  3. 表持续膨胀；
  4. 索引选择性下降；
  5. 数据保护生命周期更复杂。

更好的长期方案通常是：在线表保留活跃数据，历史数据进入归档表或数仓。

5.12 Upsert 与幂等

支付回调是典型幂等场景。创建支付流水表：

CREATE TABLE payment_records (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    payment_no VARCHAR(64) NOT NULL COMMENT '支付流水号',
    order_no VARCHAR(32) NOT NULL COMMENT '业务订单号',
    pay_status VARCHAR(16) NOT NULL,
    amount DECIMAL(12, 2) NOT NULL,
    finished_at DATETIME(3) NOT NULL,
    created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    PRIMARY KEY (id),
    UNIQUE KEY uk_payment_no (payment_no),
    KEY idx_order_no (order_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

第一次回调：

INSERT INTO payment_records
  (payment_no, order_no, pay_status, amount, finished_at)
VALUES
  ('PAY20260825001', '20260825000001', 'SUCCESS', 25.50, NOW(3));

重复回调：

INSERT INTO payment_records
  (payment_no, order_no, pay_status, amount, finished_at)
VALUES
  ('PAY20260825001', '20260825000001', 'SUCCESS', 25.50, NOW(3))
ON DUPLICATE KEY UPDATE
  payment_no = payment_records.payment_no;

然后根据影响行数或重新读取判断是否继续更新订单。不要仅凭 SQL 未报错就认为这是首次支付成功。

5.13 返回元数据

MySQL 协议会返回影响行数。不同驱动字段名略有差异。

场景 常见返回
插入普通成功 1
批量插入 插入行数
条件不匹配 0
更新后值没变化 取决于客户端配置
Upsert 插入 1
Upsert 更新 2
Upsert 冲突但未修改 0 或 1，取决于客户端配置

Java JDBC 默认通常返回匹配行数而不是实际改变行数。关键业务判断必须结合唯一键和重新读取结果，不应把影响行数当作所有场景的唯一依据。

5.14 DML 生产规范

  1. 所有更新和删除都写 WHERE；
  2. 先确认影响范围；
  3. 高并发写操作控制事务大小；
  4. 大任务分批执行；
  5. 幂等靠唯一键和状态机，不靠概率；
  6. 线上脚本必须可重复执行；
  7. 变更前备份数据；
  8. 保留操作人和操作原因；
  9. 跨表一致性优先本地事务；
  10. 跨库一致性使用明确的事务消息或对账机制。

本章小结

进阶 CRUD 的重点不是记住更多语法，而是控制结果。插入要依赖唯一约束实现幂等；更新要用旧值条件或版本号避免并发覆盖；扣库存必须检查前置条件；大批量修改要分批执行；删除要考虑归档、审计和唯一约束。影响行数是重要信号，但必须理解驱动和 Upsert 场景下的差异。

思考题

  1. INSERT IGNORE、ON DUPLICATE KEY UPDATE、REPLACE INTO 分别适合什么场景？
  2. 为什么扣库存必须在 WHERE 中判断 stock >= quantity？
  3. 乐观锁和悲观锁分别适合什么并发场景？
  4. 百万行历史数据为什么要分批删除？
  5. 设计一个支付回调幂等方案，画出状态流转和异常处理流程。