/(NOTHING))
1、普通的insert into
- 如果（主键/唯一建）存在，则会报错
- 新需求：就算冲突也不报错，用其他处理逻辑
2、基本语法（INSERT INTO ... ON CONFLICT (...) DO (UPDATE SET ...)/(NOTHING)）
- 语法图
flowchart TD A[开始: INSERT发生主键/唯一冲突] --> B{冲突后的期望是?} B -->|“保留旧数据，<br>静默跳过”| C[使用 ON CONFLICT DO NOTHING] B -->|“用新数据替换或修改旧数据”| D[使用 ON CONFLICT DO UPDATE SET] D --> E{需要精细控制吗?} E -->|“是，只更新部分字段”| F[在SET中仅指定目标字段] E -->|“是，需满足条件才更新”| G[添加WHERE子句] E -->|“否，全量覆盖”| H[使用EXCLUDED.*或指定所有字段]
- 🔀 两种核心处理逻辑
为了方便你对比和理解，我将它们总结在下表中：
| 处理逻辑 | 关键字 | 核心行为与目的 | 类比 |
|---|---|---|---|
| 1. 静默放弃 | DO NOTHING | 如果冲突（数据已存在），就什么也不做，静默地保留现有数据，并让语句成功结束。 | “无视”：看到店里已有同样的商品，就决定不放了，直接离开。 |
| 2. 更新覆盖 | DO UPDATE SET ... | 如果冲突（数据已存在），就用新值更新已有的那条记录。 | “置换”：看到店里已有同样的商品，就用你手里的新款替换掉旧款。 |
- 语法1：DO UPDATE SET
/* by yours.tools - online tools website : yours.tools/zh/autoformat.html */ INSERT INTO 表名 (列1, 列2, ...) VALUES (值1, 值2, ...) ON CONFLICT (冲突列[可以多个]) DO UPDATE SET 列1 = EXCLUDED.列1, 列2 = EXCLUDED.列2, ...;- 语法2：DO NOTHING
/* by yours.tools - online tools website : yours.tools/zh/autoformat.html */ INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...) ON CONFLICT (冲突列[可以多个]) DO NOTHING;3、示例
3.1、简单示例
-- 示例1: DO NOTHING - 确保数据唯一，重复则忽略 -- 场景：收集用户邮箱，同一邮箱只记录第一次出现 INSERT INTO user_emails (email, collected_at, source) VALUES ('alice@example.com', NOW(), '官网抽奖') ON CONFLICT (email) DO NOTHING; -- 如果邮箱已存在，则静默跳过，不报错 -- 示例2: DO UPDATE SET - 用最新信息覆盖旧记录 -- 场景：更新用户的最后登录状态 INSERT INTO user_sessions (user_id, last_login_ip, last_login_time, login_count) VALUES (123, '192.168.1.100', NOW(), 1) ON CONFLICT (user_id) DO UPDATE SET last_login_ip = EXCLUDED.last_login_ip, -- 使用本次尝试插入的新IP last_login_time = EXCLUDED.last_login_time, -- 更新时间 login_count = user_sessions.login_count + 1; -- 在原有次数上累加3.2、ON CONFLICT 多列组合唯一约束示例
场景说明
假设我们有一个学生选课记录表，设计逻辑是：
- 单个学生可以选多门课
- 单门课程可以被多个学生选
- 但一个学生不能重复选同一门课（即
(student_id, course_id)组合必须唯一）
示例表结构
CREATE TABLE student_courses ( -- 自增主键，但不是业务唯一键 id SERIAL PRIMARY KEY, student_id INT NOT NULL, course_id INT NOT NULL, selected_at TIMESTAMP DEFAULT NOW(), status VARCHAR(20) DEFAULT 'active', -- 关键：为(student_id, course_id)创建组合唯一约束 CONSTRAINT unique_student_course UNIQUE (student_id, course_id) );示例数据
假设表中已有数据：
| id | student_id | course_id | selected_at | status |
|---|---|---|---|---|
| 1 | 1001 | 101 | 2024-01-01 | active |
| 2 | 1001 | 102 | 2024-01-02 | active |
| 3 | 1002 | 101 | 2024-01-03 | active |
场景1：尝试重复选课 → 使用 DO NOTHING
学生1001想再次选择课程101（已存在），我们静默拒绝：
INSERT INTO student_courses (student_id, course_id, selected_at) VALUES (1001, 101, NOW()) -- (1001,101)组合已存在！ ON CONFLICT (student_id, course_id) -- 指定两列组合为冲突目标 DO NOTHING; -- 什么都不做，防止重复选课 -- 结果：语句执行成功，但没有插入新行 -- 表数据保持不变场景2：尝试重复选课 → 使用 DO UPDATE SET
学生1001重复选课101，但我们允许更新选择时间和状态：
INSERT INTO student_courses (student_id, course_id, selected_at, status) VALUES (1001, 101, NOW(), 'renewed') -- 再次尝试选择已选课程 ON CONFLICT (student_id, course_id) -- 检测(student_id, course_id)组合冲突 DO UPDATE SET selected_at = EXCLUDED.selected_at, -- 更新时间戳 status = EXCLUDED.status, -- 更新状态 id = student_courses.id -- 保持原id不变，避免主键冲突 RETURNING *; -- 返回更新后的行 -- 结果：不会创建新行，而是更新id=1的记录 -- 将selected_at更新为当前时间，status更新为'renewed'场景3：混合情况处理
批量插入选课记录，处理各种冲突情况：
INSERT INTO student_courses (student_id, course_id, selected_at) VALUES (1001, 103, NOW()), -- 新组合：插入成功 (1001, 101, NOW()), -- 已存在组合：触发ON CONFLICT (1002, 102, NOW()) -- 新组合：插入成功 ON CONFLICT (student_id, course_id) DO UPDATE SET selected_at = EXCLUDED.selected_at, status = 'refreshed' RETURNING student_id, course_id, selected_at;输出结果可能：
student_id | course_id | selected_at ------------+-----------+------------------------- 1001 | 103 | 2024-06-15 10:30:00.000 -- 新插入 1001 | 101 | 2024-06-15 10:30:00.000 -- 更新（冲突处理） 1002 | 102 | 2024-06-15 10:30:00.000 -- 新插入3.3、其他多列唯一约束示例
示例1：会议室预订系统
-- 确保同一会议室在同一时间段不被重复预订 -- 唯一约束：(room_id, date, time_slot) INSERT INTO room_bookings (room_id, date, time_slot, booker_name) VALUES (101, '2024-06-20', '09:00-10:00', '张三') ON CONFLICT (room_id, date, time_slot) DO NOTHING; -- 时间段冲突则直接拒绝示例2：用户-产品评分表
-- 确保一个用户对同一产品只能评分一次 -- 唯一约束：(user_id, product_id) INSERT INTO product_ratings (user_id, product_id, rating, review) VALUES (5001, 3005, 5, '非常好用') ON CONFLICT (user_id, product_id) DO UPDATE SET rating = EXCLUDED.rating, review = EXCLUDED.review, rated_at = NOW();关键要点总结
- 语法格式：
ON CONFLICT (column1, column2, ...)用括号包含多个列 - 约束要求：这些列必须已定义组合唯一约束（可以是复合主键或复合唯一约束）
- 冲突检测：只有当所有指定列的值都完全匹配时，才被认为是冲突
- 常见场景：多对多关系表、时间-资源组合、用户-实体关联表等
这种多列约束特别适合处理业务层面的组合唯一性要求，而不仅仅是技术上的主键唯一性。
4、特殊参数解析：冲突列[可以多个]
- ON CONFLICT 后面必须指定一个：唯一约束（主键也可以）字段
- 多个字段唯一也可以
关键机制：
冲突目标：
ON CONFLICT后面必须指定一个唯一约束，通常是主键或唯一索引。当插入的数据在这个约束上与已有数据冲突时，就会触发UPDATE操作。约束要求：这些列必须已定义组合唯一约束（可以是复合主键或复合唯一约束）
EXCLUDED 伪表：在
DO UPDATE SET子句中，你可以使用EXCLUDED.列名来引用本次尝试插入但发生了冲突的那些值，这是实现“用新值覆盖旧值”的关键。
)
)
