回答

SQL 提供了多种更新或插入数据到表中的方式：

UPDATE — 修改符合条件的现有记录。
MERGE（或 UPSERT）— 将插入和更新的逻辑合并到一个命令中：如果找到行 — 则更新；如果没有 — 则添加。
INSERT ... ON DUPLICATE KEY UPDATE（MySQL）或 INSERT ... ON CONFLICT DO UPDATE（PostgreSQL）— 插入新行，在键冲突时更新现有行。

何时使用什么：

如果确切知道记录尚不存在 — 使用 INSERT。
要更新现有记录 — 使用带条件的 UPDATE。
如果不知道记录是否存在，更有效的方法是使用 MERGE 或 UPSERT — 这可以减少复杂性并减少 SQL 查询的数量。
大量记录时，UPSERT 操作通常更快，并且将竞争减少到最小。

示例（PostgreSQL）：

INSERT INTO employees(id, name, salary)
VALUES (1, 'Ivan', 100000)
ON CONFLICT (id) DO UPDATE SET salary = EXCLUDED.salary;

对性能和完整性的影响：

多次单独的 UPDATE/INSERT 可能导致竞争和锁定。
MERGE/UPSERT 在大规模迁移时更有效，并支持原子性。

有陷阱的问题

如果尝试在唯一字段上执行 UPSERT，但在同一事务中同时插入和更新同一行会发生什么？

正确答案： 可能发生锁定/冲突错误，因为事务会尝试两次修改同一行。操作不会完成或会因 deadlock 错误而失败，这取决于数据库管理系统。

示例：

BEGIN;
INSERT INTO users(id, name) VALUES (1, 'Oleg') ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;
UPDATE users SET name = 'Petr' WHERE id = 1;
COMMIT;

在错误的顺序和隔离级别下，可能会发生变更冲突。

故事 №1

在某个数据迁移逻辑中，使用了 SELECT，然后是 INSERT 或 UPDATE，这有时导致另一个事务提前设置数据，并发生“重复键”错误。这导致夜间数据导出频繁失败，需要将逻辑替换为 UPSERT。

故事 №2

在 MySQL 项目中，错误使用了 ON DUPLICATE KEY UPDATE，未正确考虑复合唯一键。结果部分数据未更新，出现了重复。问题只在生产环境中被发现。

故事 №3

在项目中使用 UPDATE 而不是 UPSERT 来同步用户数据。在连接故障时，部分数据丢失，因为新用户的更新被跳过。经过分析，完全实施了 MERGE/UPSERT。

解释 SQL 中 UPDATE、MERGE 和 INSERT ... ON DUPLICATE KEY UPDATE 之间的区别。在什么情况下哪种方法更可取，以及这些工具如何影响性能和数据完整性？

回答

有陷阱的问题