PostgreSQL二维码扫码签到数据库验证问题排查

原SQL的问题分析 #

你当前的INSERT语句存在两个关键问题：

1. 多余的EXISTS子句：外层WHERE已经过滤出qr_code = {{ scanner1.data[0]}}的记录，内层的EXISTS判断完全重复，没有实际作用。
2. 空结果集不触发错误：当扫码的qr_code在persons表中不存在时，SELECT id会返回空结果集，此时INSERT操作会插入0行数据，PostgreSQL会判定执行成功（返回INSERT 0 0），但实际没有完成签到逻辑，这就是你遇到“执行成功但不符合预期”的原因。

解决方案：用PL/pgSQL函数封装完整逻辑 #

要实现你预期的三个验证分支，单纯的INSERT语句无法完成条件判断和错误提示，建议用PL/pgSQL编写一个函数，封装所有验证和操作逻辑：

方案1：返回提示文本（适合应用层根据返回值处理） #

CREATE OR REPLACE FUNCTION handle_check_in(p_qr_code TEXT)
RETURNS TEXT AS $$
DECLARE
    v_person_id INT;
BEGIN
    -- 第一步：检查QR码是否存在于参与者表
    SELECT id INTO v_person_id
    FROM persons
    WHERE qr_code = p_qr_code;

    IF NOT FOUND THEN
        RETURN 'Qr Code Invalid';
    END IF;

    -- 第二步：检查该参与者是否已签到
    IF EXISTS (SELECT 1 FROM check_ins WHERE person_id = v_person_id) THEN
        RETURN 'Qr Code Already scanned';
    END IF;

    -- 第三步：执行签到插入，自动填充当前时间
    INSERT INTO check_ins (person_id, check_in_time)
    VALUES (v_person_id, NOW());

    RETURN 'Qr code valid';
END;
$$ LANGUAGE plpgsql;

调用方式：

SELECT handle_check_in({{ scanner1.data[0]}});

应用层只需获取函数返回的文本，即可显示对应的提示信息。

方案2：抛出异常（让SQL执行失败，符合你“查询失败”的预期） #

如果你希望在验证不通过时直接让SQL执行失败（触发应用层的错误捕获逻辑），可以将返回文本改为抛出异常：

CREATE OR REPLACE FUNCTION handle_check_in(p_qr_code TEXT)
RETURNS TEXT AS $$
DECLARE
    v_person_id INT;
BEGIN
    SELECT id INTO v_person_id
    FROM persons
    WHERE qr_code = p_qr_code;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Qr Code Invalid';
    END IF;

    IF EXISTS (SELECT 1 FROM check_ins WHERE person_id = v_person_id) THEN
        RAISE EXCEPTION 'Qr Code Already scanned';
    END IF;

    INSERT INTO check_ins (person_id, check_in_time)
    VALUES (v_person_id, NOW());

    RETURN 'Qr code valid';
END;
$$ LANGUAGE plpgsql;

当QR码无效或已签到时，函数会抛出对应的异常，SQL执行会标记为失败，应用层捕获异常后即可弹出对应提示。

额外优化建议 #

• 为persons.qr_code添加唯一约束：确保每个QR码只对应一个参与者，避免重复数据。

  ALTER TABLE persons ADD CONSTRAINT unique_qr_code UNIQUE (qr_code);
  

• 为check_ins.person_id添加唯一约束：确保同一个参与者只能签到一次（如果你的业务逻辑不允许多次签到）。

  ALTER TABLE check_ins ADD CONSTRAINT unique_person_checkin UNIQUE (person_id);
  

内容的提问来源于stack exchange，提问作者Massimo Maimeri