写入与 RETURNING - 面试题

#

★★★

1. INSERT ... ON CONFLICT DO NOTHING / DO UPDATE 的 UPSERT 语义与冲突列（INFERRED INDEX）的指定方式？

问题

解释 PostgreSQL 中 INSERT ... ON CONFLICT 的 UPSERT 语义，以及冲突列如何通过 INFERRED INDEX 指定？

考察点

ON CONFLICT 的冲突检测机制
DO NOTHING 与 DO UPDATE 的行为差异
冲突目标的指定方式（列名/索引表达式/约束）

答案

ON CONFLICT 是 PostgreSQL 9.5+ 提供的 UPSERT（Upsert 即 Update + Insert）语法。当插入的行与唯一索引/唯一约束冲突时，可执行 DO NOTHING（跳过该行）或 DO UPDATE SET ...（更新已有行）。冲突目标通过 ON CONFLICT (col) 或 ON CONFLICT ON CONSTRAINT name 指定，必须能被数据库解析为一个唯一索引（即 INFERRED INDEX）。若省略冲突目标，则任何唯一约束冲突都会触发，但 DO UPDATE 时无法引用冲突行，因为无法推断具体是哪个索引。

解析

冲突推断要求列必须匹配某个唯一索引（含索引表达式、部分索引需带 WHERE）。省略冲突目标时数据库扫描所有唯一索引，但若存在多个匹配则无法确定，需明确指定。

示例代码

-- 指定冲突列
INSERT INTO users (id, name) VALUES (1, 'a')
ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;

-- 指定约束名
INSERT INTO users (id, name) VALUES (1, 'a')
ON CONFLICT ON CONSTRAINT users_pkey DO NOTHING;

-- 省略冲突目标（不允许 DO UPDATE 引用该行）
INSERT INTO users (id, name) VALUES (1, 'a') ON CONFLICT DO NOTHING;

#

★★★

2. INSERT ... RETURNING 子句（PostgreSQL）的语法与典型应用，触发器、序列回填、批量插入后处理？

问题

说明 PostgreSQL INSERT ... RETURNING 的语法及典型应用场景？

考察点

RETURNING 返回插入行的哪些列
序列回填（避免额外 SELECT）
与触发器生成的列交互

答案

RETURNING 子句在 INSERT 执行后返回实际插入的行（可指定列或 *），常配合单条插入返回自增主键，避免额外的 SELECT。典型应用包括：插入后立即拿到自增 ID、批量插入后处理、返回由触发器生成或默认值填充的列。RETURNING 可返回任意列、表达式、甚至整行。

解析

在并发场景下，RETURNING 远比先 INSERT 再 SELECT（依赖 last_insert_id 或 MAX(id)）更可靠。RETURNING 返回的是插入后的值，含默认值、触发器修改后的值。

示例代码

INSERT INTO orders (user_id, total)
VALUES (1, 99.9)
RETURNING id, created_at;

-- 批量插入后处理
INSERT INTO items (order_id, name)
VALUES (1,'a'),(1,'b'),(1,'c')
RETURNING id;

#

★★★

3. INSERT ... SELECT 的锁行为，PostgreSQL 中 INSERT ... SELECT 是否对源表加锁？

问题

分析 PostgreSQL 中 INSERT ... SELECT 对源表与目标表的锁行为？

考察点

INSERT ... SELECT 是否对源表加锁
MVCC 下读快照与并发写
目标表上的锁

答案

在 PostgreSQL 中，INSERT ... SELECT 的源表在单个语句内以语句级快照读取，默认不对源表加排他锁（仅加 ACCESS SHARE 锁，允许并发读），但同一语句内会用自己的快照保证一致性。目标表会加 ROW EXCLUSIVE 级别的锁（与普通 INSERT 相同）。若源表与目标表相同，则需注意自引用的一致性。

解析

与 MySQL 的 INSERT ... SELECT 默认对源表加共享锁（影响并发写）不同，PostgreSQL 依赖 MVCC 快照，不会阻塞源表上的并发 DML。这是两者锁语义的重要差异。

#

★★★

4. INSERT ... VALUES、INSERT ... SELECT、INSERT ... DEFAULT VALUES 三种插入语法的差异？

问题

对比 INSERT VALUES、INSERT SELECT、INSERT DEFAULT VALUES 三种语法的差异？

考察点

各语法的适用场景
默认值处理
单行与多行

答案

INSERT ... VALUES 用于插入一行或多行字面值；INSERT ... SELECT 从查询结果插入（可批量、可跨表）；INSERT ... DEFAULT VALUES 仅插入一行，所有列使用默认值（无默认值则 NULL）。VALUES 适合静态数据，SELECT 适合动态/批量数据，DEFAULT VALUES 用于仅要默认值的行。

解析

三者都是标准插入，区别在于数据来源与能否批量。VALUES 多行用逗号分隔，性能优于逐行 INSERT；SELECT 适合从现有表迁移。

示例代码

INSERT INTO t (a,b) VALUES (1,2),(3,4);
INSERT INTO t (a,b) SELECT x,y FROM src;
INSERT INTO t DEFAULT VALUES;

#

★★★

5. INSERT 触发器（BEFORE/AFTER INSERT、ROW/STATEMENT）的执行时机？

问题

说明 INSERT 触发器 BEFORE/AFTER 与 ROW/STATEMENT 级别的执行时机差异？

考察点

BEFORE 与 AFTER 的先后
ROW 与 STATEMENT 级别的触发次数
触发器修改行的时机

答案

BEFORE 触发器在行插入前执行，可修改 NEW 行的值；AFTER 触发器在行插入后执行。ROW 级别触发器对每个受影响行触发一次；STATEMENT 级别触发器对整条语句触发一次。执行顺序：BEFORE STATEMENT → BEFORE ROW（每行）→ 插入 → AFTER ROW（每行）→ AFTER STATEMENT。

解析

BEFORE ROW 触发器可修改 NEW，从而影响最终插入的数据；AFTER 触发器不能修改 NEW（数据已落库）。STATEMENT 触发器适合做审计/汇总，ROW 触发器适合逐行校验。

#

★★★

6. 批量 INSERT 的三种模式，多行 VALUES、COPY、INSERT ... SELECT 的性能对比？

问题

对比多行 VALUES、COPY、INSERT ... SELECT 三种批量插入的性能？

考察点

各模式的执行路径
WAL 与日志开销
适用场景

答案

性能上一般 COPY > 多行 VALUES > 逐行 INSERT。COPY 是专门的高效批量加载协议，绕过普通插入的逐行解析，可批量写入并减少 WAL/日志开销；多行 VALUES 一次语句插入多行，减少往返与解析开销；INSERT ... SELECT 在数据库内部直接读写，无网络传输，适合同库迁移。三者性能取决于数据量、WAL 配置与索引维护。

解析

COPY 通常最快，因为它批量分发、减少函数调用与日志；INSERT ... SELECT 避免客户端往返但索引维护成本仍在。多行 VALUES 有单条语句大小限制。

#

★★★

7. 自增列在 INSERT 中的处理，DEFAULT、显式 NULL、显式值的差异？

问题

分析自增列在 INSERT 中省略、显式 NULL、显式值三种方式的差异？

考察点

自增列的默认行为
显式 NULL 与显式值的区别
显式值对序列的影响

答案

省略自增列时使用序列/DEFAULT 生成新值；显式插入 NULL 时，MySQL 的 AUTO_INCREMENT 会生成新值，但 PostgreSQL（无论是 SERIAL 还是 GENERATED BY DEFAULT AS IDENTITY）显式插入 NULL 会按 NULL 存储并因 NOT NULL 约束报错，必须省略该列或显式写 DEFAULT 才会走序列；显式插入具体值则使用该值，并可能不推进序列（导致后续空洞）。显式值会打破序列计数，可能造成主键冲突。

解析

MySQL 对自增列显式 NULL 会回退到默认生成，但 PostgreSQL 的自增列（SERIAL/identity）显式 NULL 会因 NOT NULL 报错，需省略列或用 DEFAULT。显式具体值则按给定值存储，且可能造成序列空洞；Oracle 的 identity 列用 DEFAULT 生成，显式 NULL 需谨慎（除非 ON NULL）。

#

★★★

8. 跨方言的 INSERT 写法差异，INSERT IGNORE（MySQL）、INSERT ... ON CONFLICT（PostgreSQL）、MERGE（Oracle/SQL Server）？

问题

对比 MySQL、PostgreSQL、Oracle/SQL Server 的 UPSERT 写法差异？

考察点

各数据库的 UPSERT 语法
冲突处理语义
可移植性

答案

MySQL 用 INSERT IGNORE（忽略冲突保留旧值）或 INSERT ... ON DUPLICATE KEY UPDATE；PostgreSQL 用 INSERT ... ON CONFLICT DO NOTHING/DO UPDATE；Oracle 用 MERGE INTO ... WHEN MATCHED/NOT MATCHED；SQL Server 用 MERGE 或 UPDATE ... SET 后 INSERT。语法差异大，语义细节（如 AUTO_INCREMENT 消耗、冲突判定）也各不相同。

解析

这些语法不可直接移植，需按目标数据库改写。ON CONFLICT 需唯一索引，ON DUPLICATE KEY 由唯一索引触发，MERGE 用显式匹配条件。

#

★★★

9. INSERT ... RETURNING 与 CTE 联用（WITH ins AS (INSERT ... RETURNING ...) SELECT ...）的用法？

问题

说明 INSERT ... RETURNING 与 CTE 联用的数据修改 CTE 用法？

考察点

数据修改 CTE 语法
RETURNING 与后续 SELECT 的衔接
多步数据操作

答案

将 INSERT ... RETURNING 放入 WITH 子句的 CTE 中，然后用主 SELECT 引用其返回的行，形成"数据修改 CTE"。可用于插入后基于返回行继续处理、级联插入、或合并多个操作结果。

解析

PostgreSQL 支持 WITH 中的数据修改语句，返回的行可作为普通表被后续查询引用。注意数据修改 CTE 在单条语句内执行，顺序与可见性需注意。

示例代码

WITH ins AS (
  INSERT INTO orders (user_id, total) VALUES (1, 100)
  RETURNING id
)
INSERT INTO order_items (order_id, name)
SELECT id, 'item' FROM ins;

#

★★★

10. INSERT ... ON CONFLICT DO NOTHING 的副作用（不返回冲突行）？

问题

分析 ON CONFLICT DO NOTHING 的副作用，尤其是冲突行不返回的问题？

考察点

冲突行不返回
RETURNING 与 DO NOTHING 的交互
影响行数

答案

DO NOTHING 在冲突时跳过该行，不执行任何更新，也不返回该行。若配合 RETURNING，只有实际插入的行会返回，冲突行不会出现在结果中。因此无法区分"已存在"与"新插入"，这可能导致行数统计或后续处理遗漏。

解析

若需要知道冲突发生，可改用 DO UPDATE SET col=EXCLUDED.col（哪怕无实质变化）以触发 RETURNING 返回冲突行，或用 xmax 判断。

#

★★★

11. INSERT IGNORE 在 MySQL 中的语义与潜在陷阱？

问题

说明 MySQL INSERT IGNORE 的语义与潜在陷阱？

考察点

忽略哪些错误
其他错误处理
性能与自增消耗

答案

INSERT IGNORE 遇到重复键时忽略该行，不报错；同时也会忽略其他可忽略的错误（如数据转换错误、NOT NULL 违反等）。陷阱包括：会静默吞掉非唯一约束错误、消耗 AUTO_INCREMENT 值（即使插入失败）、难以诊断数据质量问题。

解析

由于会忽略宽泛的错误，INSERT IGNORE 可能掩盖真实的数据问题。建议仅在明确接受"遇到重复即跳过"时使用，并配合严格模式。

#

★★★

12. INSERT INTO ... SELECT FROM 的源表与目标表能否是同一张表？

问题

分析 INSERT FROM SELECT 中源表与目标表相同的情况？

考察点

自引用插入的合法性
快照一致性
无限循环风险

答案

可以，源表与目标表可以是同一张表。语句在快照下读取源行，再插入新行，不会陷入无限循环（因为插入在读取快照之后，新行不会再次被读取）。常用于复制行、行列重排等。

解析

由于是语句级快照，插入的行不会反馈给 SELECT 源，因此安全。但需注意主键/唯一约束冲突，避免插入重复数据。

示例代码

INSERT INTO t (id, val) SELECT id + 100, val FROM t WHERE id < 100;

#

★★★

13. INSERT 时的自动提交（AUTOCOMMIT）行为？

问题

说明 INSERT 在 AUTOCOMMIT 模式下的行为？

考察点

AUTOCOMMIT 默认状态
事务边界
回滚可能性

答案

在 AUTOCOMMIT 模式下，每条 INSERT 语句执行后立即提交，无法回滚。若显式 START TRANSACTION/BEGIN（或禁用 autocommit），则多条语句处于同一事务，可统一 COMMIT 或 ROLLBACK。JDBC 默认 AUTOCOMMIT 开启，但可通过 setAutoCommit(false) 控制。

解析

批量插入时务必关闭 AUTOCOMMIT 以获得事务原子性与性能（减少 fsync 次数）。

#

★★★

14. MySQL 中 INSERT DELAYED 的现代替代？

问题

说明 MySQL 中 INSERT DELAYED 的现代替代方案？

考察点

INSERT DELAYED 的历史与弃用
现代替代（异步队列、缓冲）
适用场景

答案

INSERT DELAYED 曾被用于延迟插入以提升吞吐，但自 MySQL 5.6 起已弃用，8.0 中被移除。现代替代方案是：应用层异步队列（如 Kafka、Redis 队列）、消息中间件、或使用批量插入与批量提交来提升吞吐，而非依赖服务器端延迟。

解析

INSERT DELAYED 的语义（立即返回、后台插入）与可靠性要求冲突，且与 InnoDB 事务模型不兼容，故被移除。现代方案在上层解耦。

#

★★★

15. ON CONFLICT (col) DO UPDATE SET ... 的语法？

问题

说明 ON CONFLICT (col) DO UPDATE SET 的完整语法与 EXCLUDED 引用？

考察点

冲突目标列
EXCLUDED 伪表
WHERE 条件

答案

语法为 INSERT ... VALUES ... ON CONFLICT (col) DO UPDATE SET col = EXCLUDED.col, ... WHERE condition。EXCLUDED 伪表代表"本次尝试插入的行"，可引用其列来更新已有行。可加 WHERE 实现条件更新（仅当满足条件时更新）。

解析

EXCLUDED 是本次插入失败的候选行，DO UPDATE 用它来覆盖已有行。返回值可配合 RETURNING。

示例代码

INSERT INTO counters (id, n) VALUES (1, 1)
ON CONFLICT (id) DO UPDATE SET n = counters.n + EXCLUDED.n
WHERE counters.n < 100;

#

★★

16. PostgreSQL 中 INSERT ... ON CONFLICT DO UPDATE 的 RETURNING 语义？

问题

说明 ON CONFLICT DO UPDATE 配合 RETURNING 的返回语义？

考察点

插入与更新时 RETURNING 返回
冲突行返回
条件更新

答案

DO UPDATE 时，RETURNING 返回更新后的行；DO NOTHING 时冲突行不返回。若 DO UPDATE 的 WHERE 条件不满足（不执行更新），则该行也不返回。因此 RETURNING 结果集包含"实际插入"和"实际更新"的行，不含被跳过或未更新的冲突行。

解析

需要准确区分场景时，可结合 xmax/is_update 判断本行是插入还是更新。

#

★★

17. UPDATE ... FROM other_table 的语法（PostgreSQL）与跨表更新的实现？

问题

说明 PostgreSQL UPDATE ... FROM 跨表更新语法？

考察点

FROM 子句语法
多行匹配的歧义
跨表更新

答案

PostgreSQL 支持 UPDATE t SET col = ... FROM other WHERE t.id = other.id，可基于其他表的数据更新目标表。若 other 有多行匹配目标行，会取其中一个（不确定），需用子查询或聚合保证唯一。

解析

与传统 UPDATE 的区别在于可用 FROM 引入其他表，等价于 MySQL 的多表 UPDATE。

示例代码

UPDATE orders o SET total = s.total
FROM sales s
WHERE o.id = s.order_id;

#

★★

18. UPDATE ... WHERE 子查询的写法，标量子查询、IN、EXISTS 的差异？

问题

对比 UPDATE WHERE 子查询中标量子查询、IN、EXISTS 的差异？

考察点

标量子查询返回单值
IN 与 EXISTS 的语义
NULL 处理

答案

标量子查询（col = (SELECT ...)）返回单个值，用于 SET 或 WHERE 比较；IN 判断列值是否在子查询结果中；EXISTS 判断子查询是否有行。IN 在子查询含 NULL 时可能导致结果为空（NOT IN 陷阱），EXISTS 不受 NULL 影响。

解析

半连接/反连接优化下，EXISTS 与 IN 可等价，但 NULL 语义不同。标量子查询用于逐行取值。

#

★★

19. UPDATE 与 LIMIT（MySQL）的分批更新？

问题

说明 MySQL 中 UPDATE ... LIMIT 的分批更新用法？

考察点

UPDATE 后 LIMIT 语法
分批更新
限制

答案

MySQL 支持 UPDATE ... LIMIT n，一次只更新前 n 行（按物理顺序，非特定顺序）。常用于分批更新大表，避免长时间锁表。但 LIMIT 无排序，导致更新顺序不确定，需配合 WHERE 条件实现分批。

解析

分批更新可减少锁持有时间与回滚段压力，但需注意 LIMIT 无 ORDER BY 语义。

示例代码

UPDATE big_table SET status = 1 WHERE status = 0 LIMIT 1000;

#

★★

20. UPDATE 中引用其他列（SET col2 = col1 * 2）的求值顺序？同一 UPDATE 中能否引用被修改的列？

问题

分析 UPDATE SET 表达式中引用其他列的求值顺序？

考察点

所有 SET 表达式基于原值求值
可引用被修改列
原子性

答案

UPDATE 中所有 SET 表达式基于行的原始值（更新前）求值，因此 SET col2 = col1 * 2 使用 col1 的旧值。同一 UPDATE 中，SET 的多个列赋值相互独立，都基于旧值，不会受同语句中其他 SET 的影响（除非依赖同一列）。

解析

这保证了"col2 = col1*2"和"col1 = col1+1"同时执行时，col2 用的是旧 col1。

#

★★

21. UPDATE 的行锁机制，MySQL InnoDB 的 X 锁、PostgreSQL 的行级锁？

问题

说明 MySQL InnoDB 与 PostgreSQL 的 UPDATE 行锁机制？

考察点

InnoDB 的 X 锁（排他锁）
PostgreSQL 的行级行锁与 MVCC
锁等待与死锁

答案

MySQL InnoDB 更新行时加 X 锁（排他锁），锁定索引记录（含间隙锁可能性），其他事务的读写需等待。PostgreSQL 更新行时创建新版本并加行级锁（基于 xmax 标记），读操作不受阻塞（MVCC），写与写之间互斥。

解析

两者都通过锁保证并发更新正确性，但锁实现不同：InnoDB 用锁记录索引，PostgreSQL 用多版本 + 行标记。

#

★★

22. UPDATE 触发器 BEFORE/AFTER 的语义差异与 OLD、NEW 引用？

问题

说明 UPDATE 触发器 BEFORE/AFTER 与 OLD、NEW 引用？

考察点

BEFORE 可修改 NEW
OLD 表示旧值、NEW 表示新值
AFTER 不可修改 NEW

答案

UPDATE 触发器中，OLD 表示更新前的行，NEW 表示更新后的行。BEFORE UPDATE 触发器可修改 NEW 的值，从而影响最终写入；AFTER UPDATE 触发器在更新后执行，不可修改 NEW（数据已落库）。

解析

利用 OLD/NEW 可做变更审计、增量处理。BEFORE 用于校验/改写，AFTER 用于后续动作。

#

★★

23. UPDATE 语句的执行顺序，WHERE 评估 → 投影 SET 表达式 → 触发器 → 约束检查？

问题

说明 UPDATE 语句的执行顺序？

考察点

WHERE 评估
SET 表达式求值
触发器与约束检查

答案

UPDATE 的执行顺序大致为：先根据 WHERE 条件定位并锁定的目标行，再对 SET 表达式求值（基于旧值），然后执行约束检查（NOT NULL、外键、CHECK、唯一）与触发器（BEFORE UPDATE 在修改前，AFTER UPDATE 在修改后），最后写入新版本。

解析

顺序影响行为：BEFORE 触发器可改写最终值，约束失败则回滚整行修改。

#

★★

24. UPDATE ... FROM 中目标行被源表多行匹配时，PostgreSQL 取哪一个匹配是不确定的（非预期更新），如何用预聚合、DISTINCT ON 或子查询规避？

问题

分析 UPDATE ... FROM 多行匹配的不确定性及规避方法？

考察点

多行匹配的随机选择
预聚合规避
DISTINCT ON 与子查询

答案

当源表多条记录匹配同一目标行时，PostgreSQL 不会报错，而是随机取其中一条进行更新，导致非预期结果。规避方法：先对源表做聚合（如取 SUM/MAX/MIN）保证每目标行唯一匹配，或用 DISTINCT ON 去重，或用相关子查询取单值。

解析

这是 PostgreSQL 的一个已知陷阱，多行匹配时结果不确定。生产上应保证 ON 条件唯一。

示例代码

UPDATE orders o SET total = s.total
FROM (SELECT order_id, SUM(amount) AS total FROM sales GROUP BY order_id) s
WHERE o.id = s.order_id;

#

★★

25. PostgreSQL HOT（Heap-Only Tuple）更新的触发条件（更新列不含任何索引、目标页有足够空闲空间）是什么？更新索引列为何会加剧索引膨胀并阻断 HOT？

问题

说明 PostgreSQL HOT 更新的触发条件及更新索引列的影响？

考察点

HOT 触发条件
索引列更新阻断 HOT
索引膨胀

答案

HOT（Heap-Only Tuple）更新在满足条件时触发：更新不涉及任何索引列（即被更新的列不属于任何索引），且目标页有足够空闲空间容纳新版本。此时新版本通过旧的堆指针链式定位，无需更新索引项。若更新了索引列，无法复用堆指针，需更新索引项，导致索引膨胀并阻断 HOT。

解析

HOT 显著减少索引维护开销与 WAL 量。更新索引列会迫使索引项新增，HOT 失效。

#

★★

26. UPDATE 与索引，WHERE 条件未命中索引时的全表扫描与行锁开销？

问题

说明 UPDATE 未命中索引时全表扫描与锁开销？

考察点

全表扫描
行锁范围
性能影响

答案

若 UPDATE 的 WHERE 条件未命中索引，优化器会全表扫描来定位目标行，扫描所有行。InnoDB 下未命中索引时可能锁定大量行/间隙，造成锁风暴与死锁；PostgreSQL 下扫描开销大但锁为行级。应尽量让 WHERE 命中索引。

解析

全表扫描 + 行锁会显著降低并发与性能，尤其在大表上。条件稳定时应建索引。

#

★★

27. 大批量 UPDATE 的事务边界，分批提交减少回滚段压力？

问题

说明大批量 UPDATE 的分批提交策略？

考察点

单事务过大风险
分批提交
回滚段/undo 压力

答案

大批量 UPDATE 若单事务执行，会占用大量回滚段/undo 与锁，且失败时整体回滚代价高。分批提交（如每 1000 行一个事务）可降低回滚段压力、锁持有时间与 WAL 峰值，提升可用性。

解析

分批需保证可重入（幂等），以避免部分失败导致的不一致。可用 WHERE 条件划分批次。

#

★★

28. 并发 UPDATE 同一行引发锁等待甚至死锁时，如何用 pg_locks + pg_stat_activity 定位持锁会话？分批与固定顺序更新如何降低竞争？

问题

说明并发 UPDATE 死锁的定位与规避？

考察点

pg_locks 与 pg_stat_activity 定位
死锁检测
固定顺序更新

答案

并发 UPDATE 同一行会锁等待，互持锁时可能死锁。PostgreSQL 中可用 pg_locks 查询锁信息，结合 pg_stat_activity 查看等待/阻塞会话的 pid 与 SQL。规避方法：固定更新顺序（如按主键排序）、缩小事务范围、分批更新、减少行锁覆盖。

解析

死锁时数据库会回滚其中一个事务。固定顺序避免循环等待，是根因规避。

#

★★

29. MySQL 多表 UPDATE 的语法？

问题

说明 MySQL 多表 UPDATE 语法？

考察点

UPDATE t1 JOIN t2 SET ...
跨表更新
与 PostgreSQL 的差异

答案

MySQL 支持 UPDATE t1 JOIN t2 ON ... SET t1.col = ...，一次更新多表或基于另一表更新。语法为 UPDATE 后跟多表（用 JOIN 连接），SET 后跟各表列赋值，WHERE 过滤。

解析

等价于 PostgreSQL 的 UPDATE ... FROM，但 MySQL 语法直接用 JOIN。

示例代码

UPDATE orders o JOIN temp t ON o.id = t.id SET o.total = t.total;

#

★★

30. UPDATE ... ORDER BY 的方言支持？

问题

说明 UPDATE ... ORDER BY 的方言支持情况？

考察点

MySQL 支持 ORDER BY
PostgreSQL 不支持
用途

答案

MySQL 支持 UPDATE ... ORDER BY（配合 LIMIT 控制更新顺序与数量）；PostgreSQL 原生 UPDATE 不支持 ORDER BY，需用子查询/CTE 间接实现。

解析

ORDER BY 用于控制更新顺序，在滚动更新、分批处理中常用。

#

★★

31. UPDATE ... RETURNING 的用法？

问题

说明 UPDATE ... RETURNING 用法？

考察点

返回更新后的行
旧值不可直接返回
应用场景

答案

PostgreSQL 的 UPDATE ... RETURNING 返回更新后的行（可指定列，含 OLD 不可直接引用，但可用表达式）。常用于更新后获取新值、归档、审计。

解析

RETURNING 让更新的新值直接返回，常用于更新后取回结果或审计。

示例代码

UPDATE orders SET status = 'paid' WHERE id = 1 RETURNING id, status, updated_at;

#

★★

32. UPDATE ... WHERE CURRENT OF 游标的用法？

问题

说明 UPDATE ... WHERE CURRENT OF 游标用法？

考察点

游标定位更新
逐行处理
事务要求

答案

WHERE CURRENT OF 用于更新游标当前指向的行，语法为 UPDATE t SET ... WHERE CURRENT OF cursor。多用于逐行遍历并更新的场景，需在事务内，且游标声明在事务中。

解析

相比逐行 UPDATE 用主键，WHERE CURRENT OF 更直接且避免重复扫描，但需持有游标。

示例代码

DECLARE c CURSOR FOR SELECT * FROM t;
FETCH FROM c;
UPDATE t SET val = 1 WHERE CURRENT OF c;

#

★★

33. UPDATE ... WHERE col IN (SELECT ...) 的语义？

问题

说明 UPDATE WHERE col IN (子查询) 的语义？

考察点

IN 子查询
NULL 陷阱
半连接

答案

WHERE col IN (SELECT ...) 表示更新那些 col 出现在子查询结果中的行。子查询返回集合，作为匹配条件。注意子查询含 NULL 时，IN 对 NULL 比较不匹配；NOT IN 在子查询含 NULL 时整条匹配失败（陷阱）。

解析

优化器可转为半连接/反连接。确保子查询结果不含无关 NULL 以免逻辑错误。

#

★★

34. UPDATE 与 REPLACE（MySQL）的差异？

问题

对比 MySQL UPDATE 与 REPLACE 的差异？

考察点

REPLACE 是删除+插入
UPDATE 是就地修改
自增与触发器影响

答案

REPLACE 在碰到唯一键冲突时先删除旧行再插入新行，等价于 DELETE + INSERT；UPDATE 是就地修改现有行。REPLACE 会重置自增消耗、触发 DELETE/INSERT 触发器，且可能改变行物理位置；UPDATE 保留行。

解析

REPLACE 语义较重且副作用大，通常用 INSERT ... ON DUPLICATE KEY UPDATE 更可控。

#

★★

35. UPDATE 与子查询的写法 UPDATE ... SET col = (SELECT ...)？

问题

说明 UPDATE SET 子查询写法？

考察点

标量子查询赋值
单值返回
相关子查询

答案

UPDATE t SET col = (SELECT ...) 用标量子查询给列赋值，子查询必须返回单行单列。可为相关子查询（引用外层 t 的列），实现逐行计算。

解析

标量子查询为每行计算一个值，前提是必须返回单行单列。

示例代码

UPDATE products p SET price = (SELECT price FROM price_list pl WHERE pl.id = p.id);

#

★★

36. UPDATE 多列的语法 SET col1=1, col2=2？

问题

说明 UPDATE 多列赋值语法？

考察点

逗号分隔多列
单条语句
原子性

答案

UPDATE 用 SET col1=val1, col2=val2 一次更新多列，逗号分隔。所有赋值在同一语句中完成，基于原值求值，整体原子。

解析

多列赋值在同一语句内基于原值求值，原子完成。

示例代码

UPDATE users SET name='x', age=age+1 WHERE id=1;

#

★★

37. UPDATE 的并发写竞争（Lost Update）？

问题

说明 UPDATE 的 Lost Update（丢失更新）问题？

考察点

读-改-写竞争
乐观/悲观锁
版本校验

答案

Lost Update 指两个事务读同一值，各自修改后写回，后写覆盖先写，导致先写丢失。发生在"读-改-写"非原子场景。规避：用行锁（SELECT ... FOR UPDATE）、乐观锁（版本号/时间戳条件更新）、或原子 UPDATE 表达式。

解析

单条 UPDATE 语句本身是原子的，但应用层的"读值→计算→写回"多步可能丢失更新。

#

★★

38. UPDATE 的影响行数（ROW_COUNT）？

问题

说明 UPDATE 的影响行数（ROW_COUNT）？

考察点

ROW_COUNT 语义
未变化行是否计入
客户端获取

答案

UPDATE 返回受影响行数。MySQL 中默认"未变化的行"不计入（found rows vs changed rows 可配置 CLIENT_FOUND_ROWS）；PostgreSQL 返回匹配并更新的行数。应用可据此判断更新是否成功。

解析

不同数据库与客户端配置下，影响行数含义不同，需注意区分"匹配行"与"实际变化行"。

#

★★

39. DELETE ... RETURNING 的应用，删除并返回被删数据（归档场景）？

问题

说明 DELETE ... RETURNING 的应用？

考察点

返回被删行
归档
级联处理

答案

PostgreSQL 的 DELETE ... RETURNING 返回被删除的行，可用于归档（把被删数据写入归档表）、审计、或基于删除结果做后续处理。

解析

RETURNING 把被删行返回，可配合 CTE 直接写入归档表。

示例代码

WITH del AS (
  DELETE FROM orders WHERE created_at < '2020-01-01' RETURNING *
)
INSERT INTO orders_archive SELECT * FROM del;

#

★★

40. DELETE 与 LIMIT（MySQL）的分批删除？

问题

说明 MySQL DELETE ... LIMIT 分批删除？

考察点

DELETE 后 LIMIT
分批删除
锁释放

答案

MySQL 支持 DELETE ... LIMIT n，一次删除 n 行，用于分批删除大表，减少锁持有时间与 undo 压力。无 ORDER BY 时顺序不确定，需配合 WHERE 分批。

解析

分批删除控制单次影响行数，降低锁与 undo 压力。

示例代码

DELETE FROM logs WHERE created_at < '2024-01-01' LIMIT 1000;

#

★★

41. DELETE 与 TRUNCATE 的根本差异，事务回滚、行级锁、触发器、空间回收？

问题

对比 DELETE 与 TRUNCATE 的差异？

考察点

事务回滚
行级锁 vs 表锁
触发器

答案

DELETE 逐行删除，可回滚，走 MVCC 标记，触发触发器，不立即回收空间（留下死元组）；TRUNCATE 物理删除所有行，不可回滚（PostgreSQL 可回滚），加表级锁，不触发行级触发器，立即释放空间并重置自增。DELETE 保留高水位，TRUNCATE 重置。

解析

选型依据：是否需要回滚、触发器、以及是否要立即释放空间。TRUNCATE 更快但破坏性大。

#

★★

42. DELETE 与外键约束的 CASCADE 行为？

问题

说明 DELETE 与外键 CASCADE 的行为？

考察点

ON DELETE CASCADE
级联删除
约束检查

答案

若外键定义 ON DELETE CASCADE，删除父表行时自动删除引用它的子表行；ON DELETE SET NULL 将子表引用置 NULL；ON DELETE RESTRICT/NO ACTION 阻止删除。级联由数据库保证一致性。

解析

CASCADE 在解除异常时可能大量删除，需谨慎；需考虑性能与事务。

#

★★

43. DELETE 的执行机制，PostgreSQL 的 MVCC 标记 + VACUUM、MySQL InnoDB 的行删除 + undo log？

问题

说明 DELETE 的执行机制？

考察点

PostgreSQL MVCC 标记
VACUUM 回收
InnoDB undo log

答案

PostgreSQL 的 DELETE 不物理删除，而是用 MVCC 标记行删除（xmax 标记），产生死元组，需 VACUUM 回收空间。MySQL InnoDB 的 DELETE 在 undo log 中记录旧版本，行标记删除，需 purge 线程清理。

解析

两者都基于多版本，删除后旧版本仍可能被长事务读取，因此空间回收滞后。

#

★★

44. DELETE 触发器 BEFORE/AFTER 的执行顺序与 OLD 引用？

问题

说明 DELETE 触发器 BEFORE/AFTER 与 OLD 引用？

考察点

BEFORE 在删除前
OLD 表示被删行
无 NEW

答案

DELETE 触发器只有 OLD（被删除的行），没有 NEW。BEFORE DELETE 在删除前执行，可阻止删除（如 RAISE EXCEPTION）；AFTER DELETE 在删除后执行。ROW 级对每行触发。

解析

DELETE 触发器只有 OLD 没有 NEW，BEFORE 阶段可抛异常阻止删除，因此常用来实现软删除强制与审计；理解执行时机与引用可用性才能正确编写触发器逻辑，避免误用 NEW 导致报错。

#

★★

45. 软删除（Soft Delete）的实现模式，deleted_at 列 + 索引的取舍？

问题

说明软删除的实现模式与取舍？

考察点

deleted_at 列
部分索引
查询过滤

答案

软删除用 deleted_at（NULL 表示未删除）或 deleted 标志列标记删除，不物理删除。查询需过滤 deleted_at IS NULL。可用部分索引（WHERE deleted_at IS NULL）加速活跃行查询，但会带来 WHERE 过滤、唯一约束、统计偏差等复杂度。

解析

软删除便于审计与恢复，但增加查询复杂度与存储，唯一约束需处理软删除行。

示例代码

CREATE INDEX idx_active ON users (id) WHERE deleted_at IS NULL;
SELECT * FROM users WHERE deleted_at IS NULL;

#

★★

46. DELETE 后的死元组（Dead Tuples）对查询性能的影响？VACUUM 何时回收？

问题

说明 DELETE 后死元组对性能的影响与 VACUUM 回收？

考察点

死元组
索引膨胀
VACUUM 时机

答案

大量 DELETE 产生死元组，使表与索引膨胀，增加扫描代价，甚至触发索引膨胀与查询变慢。VACUUM 回收死元组并更新可见性映射，但需在无事务引用时。autovacuum 自动触发，也可手动 VACUUM。

解析

死元组过度累积会退化性能，需合理配置 autovacuum 阈值。

#

★★

47. DELETE ... WHERE IN 子查询的陷阱？

问题

说明 DELETE WHERE IN 子查询的陷阱？

考察点

自引用子查询
不能同时删除同一表源
MySQL 限制

答案

陷阱包括：MySQL 不允许 DELETE 目标表出现在子查询中直接引用（需用派生表嵌套）；NOT IN 子查询含 NULL 时结果为空导致误删；子查询与目标表相同需谨慎。

解析

大表删除时注意锁与批处理。子查询含 NULL 是 NOT IN 的经典陷阱。

#

★

48. DELETE 与 GDPR / 数据删除请求的处理？

问题

说明 DELETE 与 GDPR 数据删除请求的处理？

考察点

合规删除
级联/软删除
审计

答案

GDPR 要求"被遗忘权"——用户请求删除数据时需彻底删除。处理需考虑实体完整性（级联删除关联数据）、备份与副本中的删除、日志脱敏。软删除可能不满足"物理删除"要求，需权衡。

解析

合规删除需跨表、跨备份、跨副本执行，并记录审计日志。

#

★

49. DELETE 多表（DELETE t1, t2 FROM t1 JOIN t2）的 MySQL 语法？

问题

说明 MySQL 多表 DELETE 语法？

考察点

DELETE 多表
JOIN 连接
一次删除多表

答案

MySQL 支持 DELETE t1, t2 FROM t1 JOIN t2 ON ... WHERE ...，一次从多个表删除相关行。列出的表都会被删除匹配行。

解析

MySQL 用 JOIN 连接多表后一次删除，是跨表删除的简便写法。

示例代码

DELETE o, i FROM orders o JOIN order_items i ON o.id = i.order_id
WHERE o.created_at < '2020-01-01';

#

★

50. PostgreSQL 中 USING 子句的 DELETE？

问题

说明 PostgreSQL DELETE USING 语法？

考察点

USING 引入其他表
跨表删除
与 MySQL 的差异

答案

PostgreSQL 的 DELETE ... USING other WHERE ... 可基于其他表的数据删除目标表行，等价于 MySQL 的多表 DELETE。USING 引入附加表参与匹配。

解析

USING 引入附加表参与匹配，等价于 MySQL 的多表 DELETE。

示例代码

DELETE FROM orders o USING blacklist b WHERE o.user_id = b.user_id;

#

★

51. 批量删除的最佳实践（分批、LIMIT、循环）？

问题

说明批量删除的最佳实践？

考察点

分批删除
LIMIT 控制
循环

答案

批量删除应分批（每批 LIMIT n，循环执行），避免单事务锁定大量行与占用 undo/回滚段。每批提交释放锁，降低死锁与阻塞风险。可配合 WHERE 条件与索引。

解析

分批删除需幂等，且最好在低峰期执行；必要时配合索引避免全表扫描。

#

★

52. 逻辑删除（deleted=1）的查询过滤？

问题

说明逻辑删除（deleted=1）的查询过滤？

考察点

deleted 标志过滤
默认过滤
索引

答案

逻辑删除用 deleted=1 标记，查询需恒加 WHERE deleted=0 过滤。可通过部分索引优化，或封装视图/ORM 默认过滤。注意唯一约束与统计。

解析

逻辑删除行需恒加过滤条件，可用部分索引优化活跃行查询。

示例代码

SELECT * FROM users WHERE deleted = 0 AND id = 1;

#

★

53. TRUNCATE 的实现，扫描数据页 + 重置自增、序列的行为？

问题

说明 TRUNCATE 的实现与对自增/序列的影响？

考察点

扫描数据页
重置自增
序列行为

答案

TRUNCATE 通过扫描并删除表的数据页快速清空，不逐行删除。MySQL 中重置 AUTO_INCREMENT；PostgreSQL 中默认不重置序列（需显式 RESTART IDENTITY 才会重置），但序列对象本身不删除。

解析

TRUNCATE 快速但会重置自增，且不可恢复（PostgreSQL 可回滚但序列不变）。

#

★

54. 归档表（Archive Table）的分区方案，时间分区、冷热分离？

问题

说明归档表的分区方案与冷热分离？

考察点

时间分区
冷热分离
归档策略

答案

归档大量旧数据可用时间分区（按日期/月份分区），便于按分区删除与查询。冷热分离把热数据放主表、冷数据放归档表/存储，减少主表膨胀。可配合分区裁剪提升查询性能。

解析

时间分区使归档/清理简洁（DROP PARTITION），冷热分离平衡性能与成本。

#

★

55. TRUNCATE ... RESTART IDENTITY、CASCADE 选项？

问题

说明 TRUNCATE 的 RESTART IDENTITY 与 CASCADE 选项？

考察点

RESTART IDENTITY 重置自增
CASCADE 级联截断
默认行为

答案

RESTART IDENTITY 重置自增/序列为起始值；CASCADE 自动截断引用该表的外键表（否则有外键依赖时需显式 CASCADE）。默认 TRUNCATE 不重置自增（PostgreSQL 需 RESTART IDENTITY）。

解析

RESTART IDENTITY 重置自增，CASCADE 级联截断有外键依赖的表。

示例代码

TRUNCATE TABLE orders RESTART IDENTITY CASCADE;

#

★

56. INSERT 与序列的空洞产生？

问题

说明 INSERT 与序列空洞的产生原因？

考察点

序列不回滚
回滚/失败导致空洞
缓存

答案

序列（SEQUENCE）值一旦分配不会回滚，因此事务回滚、插入失败、并发分配都会产生空洞（跳号）。CACHE 预取也导致空洞。这是序列的固有特性，通常不保证连续。

解析

序列保证唯一性，不保证连续性。需要连续编号需用其他机制。

#

★

57. DELETE 是否释放磁盘空间？

问题

说明 DELETE 是否释放磁盘空间？

考察点

DELETE 逐行标记
空间不立即释放
VACUUM/OPTIMIZE

答案

DELETE 不立即释放磁盘空间，只是把行标记为删除（PostgreSQL 死元组、MySQL 标记删除），文件大小不变。需 VACUUM（PostgreSQL）/OPTIMIZE TABLE（MySQL）或等待自动回收才有空间释放。TRUNCATE 才立即释放。

解析

需要立即释放空间时用 TRUNCATE 或重建表。

#

★

58. TRUNCATE 是否走 MVCC？

问题

说明 TRUNCATE 是否走 MVCC？

考察点

MVCC 逐行版本
TRUNCATE 物理删除
事务可见性

答案

TRUNCATE 不走逐行 MVCC，它物理删除数据页（或标记整表删除），因此比 DELETE 快，但也意味着依赖 MVCC 的长事务读取旧快照的能力受限。PostgreSQL 中 TRUNCATE 在事务内可通过 WAL 回滚，但其他会话无法读取被截断的数据。

解析

TRUNCATE 破坏 MVCC 快照隔离，适用需立即清空且不依赖旧版本读取的场景。