调试排错 - 线程Dump分析妥

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 ... 如果冲突数据已存在就用新值更新已有的那条记录。 “置换”看到店里已有同样的商品就用你手里的新款替换掉旧款。语法1DO UPDATE SETINSERT INTO 表名 (列1, 列2, ...)VALUES (值1, 值2, ...)ON CONFLICT (冲突列[可以多个])DO UPDATE SET列1 EXCLUDED.列1,列2 EXCLUDED.列2,...;语法2DO NOTHINGINSERT 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 (aliceexample.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 SETlast_login_ip EXCLUDED.last_login_ip, -- 使用本次尝试插入的新IPlast_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 status1 1001 101 2024-01-01 active2 1001 102 2024-01-02 active3 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 SETselected_at EXCLUDED.selected_at, -- 更新时间戳status EXCLUDED.status, -- 更新状态id student_courses.id -- 保持原id不变避免主键冲突RETURNING *; -- 返回更新后的行-- 结果不会创建新行而是更新id1的记录-- 将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 SETselected_at EXCLUDED.selected_at,status refreshedRETURNING 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 SETrating EXCLUDED.rating,review EXCLUDED.review,rated_at NOW();关键要点总结语法格式ON CONFLICT (column1, column2, ...) 用括号包含多个列约束要求这些列必须已定义组合唯一约束可以是复合主键或复合唯一约束冲突检测只有当所有指定列的值都完全匹配时才被认为是冲突常见场景多对多关系表、时间-资源组合、用户-实体关联表等这种多列约束特别适合处理业务层面的组合唯一性要求而不仅仅是技术上的主键唯一性。回到顶部4、特殊参数解析冲突列[可以多个]ON CONFLICT 后面必须指定一个唯一约束主键也可以字段多个字段唯一也可以关键机制冲突目标ON CONFLICT 后面必须指定一个唯一约束通常是主键或唯一索引。当插入的数据在这个约束上与已有数据冲突时就会触发 UPDATE 操作。约束要求这些列必须已定义组合唯一约束可以是复合主键或复合唯一约束EXCLUDED 伪表在 DO UPDATE SET 子句中你可以使用 EXCLUDED.列名 来引用本次尝试插入但发生了冲突的那些值这是实现“用新值覆盖旧值”的关键。虐嗽死翘