数据操作：INSERT、UPDATE、DELETE 与事务的第一现场

7 min中级2026/10/11

以外卖骑手调度系统为练习场，动手掌握 INSERT/UPDATE/DELETE、UPSERT 两种方言写法、批量导入、事务与保存点，并养成"改数据先想三秒"的安全习惯。

1. 场景：你是外卖平台”闪电送”的调度系统工程师

查询是”看”，DML（Data Manipulation Language，数据操作语言）是”动手改”。你负责的调度系统每天要写几百万次数据：

骑手注册、批量入职导入；
骑手上线、接单，状态实时流转；
每一单完成，骑手的当日统计要加一；
注销的骑手要清理，跑批产生的日志表要定期清空；
最要命的：一次写操作改坏数据，几分钟后客服电话就被打爆。

这一篇就围绕这些真实动作展开。先建表：

sql

CREATE TABLE riders (
  id           INT PRIMARY KEY,
  name         VARCHAR(50) NOT NULL,
  phone        VARCHAR(20) UNIQUE,
  city         VARCHAR(30) NOT NULL,
  status       VARCHAR(10) NOT NULL DEFAULT 'offline',  -- online/offline/rest
  rating       DECIMAL(3,2) NOT NULL DEFAULT 5.00,
  today_orders INT NOT NULL DEFAULT 0,     -- 当日完成单量
  version      INT NOT NULL DEFAULT 0      -- 乐观锁版本号，第 8 节用到
);

CREATE TABLE orders (
  id        INT PRIMARY KEY,
  rider_id  INT,                            -- 未派单时为 NULL
  status    VARCHAR(12) NOT NULL DEFAULT 'pending',
            -- pending/accepted/delivering/delivered/canceled
  fee       DECIMAL(8,2) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE rider_daily_stats (            -- 骑手当日统计，UPSERT 主战场
  stat_date  DATE NOT NULL,
  rider_id   INT NOT NULL,
  order_cnt  INT NOT NULL DEFAULT 0,
  income     DECIMAL(10,2) NOT NULL DEFAULT 0,
  PRIMARY KEY (stat_date, rider_id)
);

示例数据：

sql

INSERT INTO riders (id, name, phone, city) VALUES
  (1, '老吴',   '13800001111', '杭州'),
  (2, '小雨',   '13800002222', '杭州'),
  (3, '钢铁直男', '13800003333', '上海'),
  (4, '夜风',   NULL,           '上海');

INSERT INTO orders (id, rider_id, status, fee) VALUES
  (9001, NULL, 'pending',   5.50),
  (9002, 1,    'delivering', 6.00),
  (9003, 2,    'delivered',  5.00),
  (9004, 3,    'accepted',   7.50);

2. INSERT：把新数据放进来

2.1 三种基本形态

sql

-- 单行插入：显式列名是铁律
INSERT INTO riders (id, name, phone, city)
VALUES (5, '阿蛮', '13800004444', '广州');

-- 多行插入：一次语句，多个元组
INSERT INTO riders (id, name, phone, city) VALUES
  (6, '大熊', '13800005555', '广州'),
  (7, '闪电', '13800006666', '深圳');

-- 没写的列走默认值：阿蛮这条的 status 就是 'offline'，rating 是 5.00

始终写列名，不要 INSERT INTO riders VALUES (...)。表结构加一列（哪怕带默认值），省略列名的老代码立刻报错；列名写全的代码毫发无伤。

2.2 拿回数据库生成的主键

应用插入后通常立刻需要新行的 id：

sql

-- PostgreSQL / SQLite / MariaDB：RETURNING，一条语句搞定
INSERT INTO riders (id, name, phone, city)
VALUES (8, '海燕', '13800007777', '深圳')
RETURNING id, name;

-- MySQL：插完再查会话级的 LAST_INSERT_ID()
INSERT INTO riders (id, name, phone, city)
VALUES (9, '北岸', '13800008888', '北京');
SELECT LAST_INSERT_ID();

-- SQL Server：OUTPUT 子句
INSERT INTO riders (id, name, phone, city)
OUTPUT INSERTED.id, INSERTED.name
VALUES (10, '南山', '13800009999', '北京');

RETURNING 不只属于 INSERT，UPDATE、DELETE 同样支持（PostgreSQL 风格），后面都会见到。

2.3 INSERT … SELECT：用查询结果当数据源

招聘批次到了，从”意向骑手表”把杭州的候选人转正：

sql

INSERT INTO riders (id, name, phone, city)
SELECT id, name, phone, city
FROM rider_candidates
WHERE city = '杭州' AND passed = TRUE;

INSERT 与 SELECT 的列必须一一对应。它还能配合 GROUP BY 直接写入汇总表——数据迁移、归档、报表落表全靠这一招。

3. UPDATE：改数据，WHERE 是生死线

3.1 早晚高峰的状态流转

sql

-- 老吴上线接单
UPDATE riders SET status = 'online' WHERE id = 1;

-- 订单 9001 被老吴抢单：一行改两个值
UPDATE orders
SET rider_id = 1, status = 'delivering'
WHERE id = 9001;

-- 全站结算费率上调 10%：基于自身值更新
UPDATE orders SET fee = fee * 1.1 WHERE created_at >= '2026-09-01';

SET fee = fee * 1.1 这种”引用旧值算新值”的写法是 UPDATE 的日常。每一句 UPDATE 都必须自问：WHERE 呢？ 没有 WHERE 的 UPDATE 是全表灾难，而且在你按下回车前不会有任何确认弹窗。

3.2 一句 UPDATE 做出差异化调价

下雨天给小额订单多补一点，用 CASE WHEN 一条语句按行分发不同金额（比应用层循环单发 N 条快一个数量级）：

sql

UPDATE orders
SET fee = fee + CASE
  WHEN fee < 5.5  THEN 1.00
  WHEN fee < 7.0  THEN 0.50
  ELSE 0.30
END
WHERE status = 'pending' AND created_at >= '2026-09-01';

CASE 逐行求值，每行拿自己的 fee 算出自己的加价。WHERE 先筛掉大半行，CASE 只在幸存者里生效——执行顺序（先 WHERE 后 SELECT/表达式）在这里同样适用。

3.3 跨表更新：方言分叉点

“给上海金牌骑手（rating >= 4.8）的进行中订单每单加 2 元”，三个数据库三种写法：

sql

-- MySQL：UPDATE + JOIN + SET
UPDATE orders o
JOIN riders r ON o.rider_id = r.id
SET o.fee = o.fee + 2.00
WHERE r.city = '上海' AND r.rating >= 4.8
  AND o.status = 'delivering';

-- PostgreSQL：UPDATE ... FROM
UPDATE orders o
SET fee = o.fee + 2.00
FROM riders r
WHERE o.rider_id = r.id
  AND r.city = '上海' AND r.rating >= 4.8
  AND o.status = 'delivering';

-- 通用兜底：子查询（三大家都能跑，数据量大时可能更慢）
UPDATE orders
SET fee = fee + 2.00
WHERE status = 'delivering'
  AND rider_id IN (
    SELECT id FROM riders WHERE city = '上海' AND rating >= 4.8
  );

面试和迁移中最常考的方言差异之一。记牢各自形状，切换数据库时心里不慌。

4. UPSERT：存在就更新，不存在就插入

调度系统每完成一单，要更新”骑手当日统计”。骑手今天第一单时行还不存在，第二单起就要累加——“插入或更新”二合一，这就是 UPSERT。手写”先 SELECT 判断再 INSERT/UPDATE”在并发下必然出错（两个请求同时判断”不存在”，插出两行），必须用数据库原子保证。

4.1 老吴完成一单，统计落库

sql

-- PostgreSQL：ON CONFLICT 冲突目标明确，推荐
INSERT INTO rider_daily_stats (stat_date, rider_id, order_cnt, income)
VALUES (CURRENT_DATE, 1, 1, 6.00)
ON CONFLICT (stat_date, rider_id) DO UPDATE SET
  order_cnt = rider_daily_stats.order_cnt + EXCLUDED.order_cnt,
  income    = rider_daily_stats.income    + EXCLUDED.income;
-- EXCLUDED 是"本次想插入但被冲突拦下的那行"，即新值；不带表限定名的是表中旧值

-- MySQL 8.0.20+：ON DUPLICATE KEY UPDATE + 行别名（旧写法 VALUES(col) 已废弃）
INSERT INTO rider_daily_stats (stat_date, rider_id, order_cnt, income)
VALUES (CURRENT_DATE, 1, 1, 6.00) AS new_row
ON DUPLICATE KEY UPDATE
  order_cnt = order_cnt + new_row.order_cnt,
  income    = income    + new_row.income;

只想去重、冲突时不动原数据：

sql

-- PostgreSQL
INSERT INTO riders (id, name, phone, city)
VALUES (1, '老吴', '13800001111', '杭州')
ON CONFLICT (phone) DO NOTHING;

-- MySQL
INSERT INTO riders (id, name, phone, city)
VALUES (1, '老吴', '13800001111', '杭州')
AS new_row
ON DUPLICATE KEY UPDATE id = id;   -- 空操作惯用法

注意”冲突”的判定依据是主键或唯一约束（riders 的 phone UNIQUE、统计表的联合主键），不是随便哪列相同。标准 SQL 的 MERGE 语句（SQL Server / Oracle 支持，PostgreSQL 15 起支持）语法更繁但能力更强，专门对比在 UPSERT 与 MERGE。

5. DELETE 与 TRUNCATE：删数据的两种姿势

5.1 DELETE：精确打击

sql

-- 骑手注销：删人，同时把他名下待处理订单置回待派单（两句放一个事务，见第 7 节）
DELETE FROM riders WHERE id = 4;

-- 清理 30 天前的取消单
DELETE FROM orders
WHERE status = 'canceled' AND created_at < CURRENT_TIMESTAMP - INTERVAL '30 days';

-- 删除从未被派过单的订单（子查询版，通用写法）
DELETE FROM orders
WHERE id NOT IN (SELECT DISTINCT rider_id FROM orders WHERE rider_id IS NOT NULL);

5.2 TRUNCATE：整表清空

跑批日志表 dispatch_log 攒了一个月几千万行，要清空重来：

sql

TRUNCATE TABLE dispatch_log;                          -- 秒级清空
TRUNCATE TABLE dispatch_log RESTART IDENTITY;         -- PostgreSQL：自增计数一并归零
TRUNCATE TABLE dispatch_log, audit_log CASCADE;       -- 连带被外键引用的表一起清

两者关键差异：

特性	DELETE	TRUNCATE
类型	DML，逐行删、记日志	DDL，直接重置数据文件
速度	行数多时很慢	几乎与表大小无关，极快
WHERE	支持	不支持，只能全清
回滚	事务内可回滚	PostgreSQL/SQL Server 可回滚；MySQL 隐式提交，不可回滚
自增计数	不重置	MySQL 重置；PostgreSQL 需 RESTART IDENTITY
触发器	触发行级触发器	不触发行级触发器

MySQL 里 TRUNCATE 不可回滚这一点务必刻在脑子里：对生产表执行前，先确认没有外键引用、确认真的要全清。

6. 批量操作：量一大，姿势决定生死

6.1 批量插入

sql

-- 推荐：多行 VALUES，一次网络往返
INSERT INTO orders (id, rider_id, status, fee) VALUES
  (9010, NULL, 'pending', 5.50),
  (9011, NULL, 'pending', 6.00),
  (9012, NULL, 'pending', 4.80);

-- 反面教材：循环里逐条 INSERT，100 万行 = 100 万次网络往返

更大规模（百万级初始化、日切导入）用专用通道：

sql

-- PostgreSQL：COPY，走流式解析，比 INSERT 快一个量级
COPY riders (name, phone, city)
FROM '/data/riders.csv' WITH (FORMAT csv, HEADER true);

-- MySQL：LOAD DATA
LOAD DATA INFILE '/data/riders.csv'
INTO TABLE riders
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

6.2 批量更新：临时表 JOIN

App 端上传了 1 万条骑手资料修改，别拼 1 万条 UPDATE。先把变更灌进临时表，再一次 JOIN 更新：

sql

CREATE TEMPORARY TABLE rider_updates (rider_id INT, new_name VARCHAR(50), new_phone VARCHAR(20));
INSERT INTO rider_updates VALUES (1, '吴师傅', '13800001111'), (2, '小雨点', '13800002222');

-- PostgreSQL 形状
UPDATE riders r
SET name  = u.new_name,
    phone = u.new_phone
FROM rider_updates u
WHERE r.id = u.rider_id;

-- MySQL 形状
UPDATE riders r
JOIN rider_updates u ON r.id = u.rider_id
SET r.name = u.new_name, r.phone = u.new_phone;

“临时表 + JOIN 更新”是数据订正的标准范式，CASE WHEN 拼超长 SQL 的写法只适合几十条的小批量。

7. 事务：要么全做，要么全不做

骑手注销要”删骑手 + 退回他未完成的订单”两步。如果删完骑手程序崩了，订单就会永远挂在一个不存在的人身上。事务把多条语句打包成不可分割的单元：

sql

BEGIN;                                              -- MySQL 也可写 START TRANSACTION

UPDATE orders SET rider_id = NULL, status = 'pending'
WHERE rider_id = 4 AND status IN ('accepted', 'delivering');

DELETE FROM riders WHERE id = 4;

COMMIT;                                             -- 两步同时生效

中途发现不对，ROLLBACK 让前面所有语句如同没发生过。还有保存点支持”回滚到中途”：

sql

BEGIN;
UPDATE orders SET fee = fee * 1.1 WHERE id = 9002;
SAVEPOINT after_price;
DELETE FROM riders WHERE id = 4;
ROLLBACK TO SAVEPOINT after_price;   -- 只撤销 DELETE，调价保留
COMMIT;

不加 BEGIN 时，每条语句都是一个独立事务，自动提交——这也是”误 UPDATE 全表当场生效”的原因之一。把相关联的写操作包进事务，是数据库编程的第一纪律。

ACID 四特性、四种隔离级别、脏读/不可重复读/幻读的逐个演示，在事务 ACID、隔离级别与并发异常有完整专题，此处先建立”事务 = 原子开关”的直觉即可。

8. 坑点清单：改数据先想三秒

生产事故里 DML 占比远高于 DDL。以下是保命习惯：

UPDATE/DELETE 前先用同样条件 SELECT 一遍，看看会命中哪些行、多少行：

sql

SELECT COUNT(*) FROM orders WHERE status = 'canceled';  -- 先数
DELETE FROM orders WHERE status = 'canceled';           -- 再删

重要订正放进事务：BEGIN 后执行，确认影响行数符合预期再 COMMIT，否则 ROLLBACK。
并发改同一行用乐观锁：带版本号条件更新，改的人互相看得见。
sql
```
UPDATE riders
SET status = 'rest', version = version + 1
WHERE id = 1 AND version = 7;
-- 应用层检查影响行数：0 行说明别人先改了（version 已不是 7），重新读取再决定
```
与之相对的悲观锁（SELECT ... FOR UPDATE 先锁行再改）适合冲突激烈的场景，对比见乐观锁与悲观锁。
TRUNCATE 在 MySQL 不可回滚，执行前二次确认表名——多表会话里手滑清错表的案例并不罕见。
UPSERT 别手写”查了再插”，并发下必然重复；交给 ON CONFLICT / ON DUPLICATE KEY。
两个事务按相反顺序更新两行会死锁（A 锁 1 等 2，B 锁 2 等 1）。预防：所有事务按同一顺序访问行，事务尽量短。数据库会检测死锁并回滚其中一方，但报错重试的成本不如预防。

9. 练习

基于闪电送三张表：

新骑手”风间”（id=11，上海，手机号 13811112222）注册，插入后返回他的 id。（分别用 PostgreSQL 和 MySQL 两种思路写。）
把上海所有 offline 骑手批量置为 online，一句 UPDATE 完成。
订单 9004 被骑手 3 转给了骑手 4：一条 UPDATE 同时改 rider_id 和 status（改为 delivering）。
用 UPSERT 完成：骑手 2 今日第 2 单完成（fee 5.00），rider_daily_stats 的 order_cnt 加 2、income 加 10.00。分别写 PostgreSQL 和 MySQL 版本。
骑手 5 注销：在一个事务里先把他未完成订单置回 pending，再删除骑手。
（思考题）DELETE FROM orders WHERE rider_id IS NULL AND created_at < NOW() - INTERVAL '7 days' 与 TRUNCATE TABLE orders 的区别是什么？各适合什么场景？

下一步

UPSERT 与 MERGE：三种 UPSERT 方言与 MERGE 的完整能力对比。
TCL 事务控制：BEGIN/COMMIT/ROLLBACK/SAVEPOINT 的完整语义。
事务 ACID：事务四特性的数据库实现视角。
锁机制：写并发之下的行锁、间隙锁与死锁处理。

数据操作：INSERT、UPDATE、DELETE 与事务的第一现场

1. 场景：你是外卖平台”闪电送”的调度系统工程师#

2. INSERT：把新数据放进来#

2.1 三种基本形态#

2.2 拿回数据库生成的主键#

2.3 INSERT … SELECT：用查询结果当数据源#

3. UPDATE：改数据，WHERE 是生死线#

3.1 早晚高峰的状态流转#

3.2 一句 UPDATE 做出差异化调价#

3.3 跨表更新：方言分叉点#

4. UPSERT：存在就更新，不存在就插入#

4.1 老吴完成一单，统计落库#

5. DELETE 与 TRUNCATE：删数据的两种姿势#

5.1 DELETE：精确打击#

5.2 TRUNCATE：整表清空#

6. 批量操作：量一大，姿势决定生死#

6.1 批量插入#

6.2 批量更新：临时表 JOIN#

7. 事务：要么全做，要么全不做#

8. 坑点清单：改数据先想三秒#

9. 练习#

下一步#