分钟搞懂深度学习AI：实操篇：Attention

1、普通的insert into

如果（主键/唯一建）存在，则会报错

新需求：就算冲突也不报错，用其他处理逻辑

回到顶部

2、基本语法（INSERT INTO ... ON CONFLICT (...) DO (UPDATE SET ...)/(NOTHING)）

语法图

“保留旧数据，

静默跳过”

“用新数据替换或修改旧数据”

“是，只更新部分字段”

“是，需满足条件才更新”

“否，全量覆盖”

开始: INSERT发生主键/唯一冲突

冲突后的期望是?

使用 ON CONFLICT DO NOTHING

使用 ON CONFLICT DO UPDATE SET

需要精细控制吗?

在SET中仅指定目标字段

添加WHERE子句

使用EXCLUDED.*或指定所有字段

?? 两种核心处理逻辑

为了方便你对比和理解，我将它们总结在下表中：

处理逻辑 关键字 核心行为与目的 类比

1. 静默放弃 DO NOTHING 如果冲突（数据已存在），就什么也不做，静默地保留现有数据，并让语句成功结束。 “无视”：看到店里已有同样的商品，就决定不放了，直接离开。

2. 更新覆盖 DO UPDATE SET ... 如果冲突（数据已存在），就用新值更新已有的那条记录。 “置换”：看到店里已有同样的商品，就用你手里的新款替换掉旧款。

语法1：DO UPDATE SET

INSERT INTO 表名 (列1, 列2, ...)

VALUES (值1, 值2, ...)

ON CONFLICT (冲突列[可以多个])

DO UPDATE SET

列1 = EXCLUDED.列1,

列2 = EXCLUDED.列2,

...;

语法2：DO NOTHING

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.列名 来引用本次尝试插入但发生了冲突的那些值，这是实现“用新值覆盖旧值”的关键。平倏怂级