如何在SQL插入查询中添加IF NOT EXISTS避免重复插入参会学生记录

前置准备 #

首先需要对meeting1表做两处调整，适配你的需求：

1. 新增签到时间字段SIGNIN_TIME，用于判断记录时间差，默认值设为当前时间：

-- PostgreSQL 语法
ALTER TABLE meeting1 ADD COLUMN SIGNIN_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

2. 确定重复记录的判定规则，比如「同一个学生同一天签到算重复」，则新增对应唯一约束：

ALTER TABLE meeting1 ADD CONSTRAINT unique_stu_sign_date UNIQUE(STUDENTID, DATE(SIGNIN_TIME));
-- 如果需要按小时粒度去重，就改成 UNIQUE(STUDENTID, DATE(SIGNIN_TIME), HOUR(SIGNIN_TIME))
-- 如果只要完全相同时间的同学生记录才算重复，直接改成 UNIQUE(STUDENTID, SIGNIN_TIME) 即可

方案1：ON CONFLICT 控制逻辑（PostgreSQL优先用这个，性能更高） #

你用的是PostgreSQL数据库（语句中的CUBE运算符是PostgreSQL专属特性），直接在INSERT语句末尾加冲突处理逻辑即可，修改后的insert_script如下：

insert_script = """
INSERT INTO meeting1(STUDENTID,STUDENTNAME,STUDENTEMAIL,SIGNIN_TIME) 
SELECT STUDENTID,STUDENTNAME,STUDENTEMAIL,CURRENT_TIMESTAMP 
FROM encodings 
WHERE sqrt(power(CUBE(array[{}]) <-> ENCODINGS, 2)) <= {} 
ORDER BY sqrt(power(CUBE(array[{}]) <-> ENCODINGS, 2)) ASC 
LIMIT 1
ON CONFLICT (STUDENTID, DATE(SIGNIN_TIME)) DO NOTHING
""".format(','.join(str(s) for s in encodeFace), threshold, ','.join(str(s) for s in encodeFace))

这里ON CONFLICT ... DO NOTHING就会在命中唯一约束（同学生同天已经签到）的时候跳过插入，时间不同时不会命中约束，会正常插入新记录。

方案2：WHERE NOT EXISTS 写法（兼容所有SQL数据库） #

如果你不想提前建唯一约束，也可以直接在SELECT逻辑里加不存在判断，修改后的语句如下：

insert_script = """
INSERT INTO meeting1(STUDENTID,STUDENTNAME,STUDENTEMAIL,SIGNIN_TIME) 
SELECT t.STUDENTID, t.STUDENTNAME, t.STUDENTEMAIL, CURRENT_TIMESTAMP
FROM (
    SELECT STUDENTID,STUDENTNAME,STUDENTEMAIL 
    FROM encodings 
    WHERE sqrt(power(CUBE(array[{}]) <-> ENCODINGS, 2)) <= {} 
    ORDER BY sqrt(power(CUBE(array[{}]) <-> ENCODINGS, 2)) ASC 
    LIMIT 1
) t
WHERE NOT EXISTS (
    SELECT 1 FROM meeting1 m 
    WHERE m.STUDENTID = t.STUDENTID 
    AND DATE(m.SIGNIN_TIME) = CURRENT_DATE -- 同天判定重复，按需调整时间粒度即可
)
""".format(','.join(str(s) for s in encodeFace), threshold, ','.join(str(s) for s in encodeFace))

内容的提问来源于stack exchange，提问作者Dancun Gerald