如何在PHP与PostgreSQL中每年重置receipt_id？

嘿，你这个按年份重置receipt_id的需求很常见，先聊聊你当前实现的潜在问题，再给你几个更健壮的数据库层面解决方案：

你用PHP查询当年最大receipt_id再加一的方式，在低并发场景下没问题，但遇到多请求同时插入同一年份数据时，很容易出现并发冲突——两个请求同时拿到相同的MAX值，插入时就会触发唯一键约束报错。而且这种逻辑依赖应用层，如果有其他服务/脚本也要插入数据，还得重复写一遍，容易出现不一致。

把自增逻辑放到数据库里，既能避免并发问题，也能统一维护逻辑，推荐两种方案：

方案1：触发器动态计算自增值（适合中小数据量） #

这种方式不需要额外维护序列，每次插入时自动计算当年的下一个receipt_id：

第一步：创建计算自增值的函数 #

CREATE OR REPLACE FUNCTION get_next_receipt_id()
RETURNS TRIGGER AS $$
DECLARE
    next_id BIGINT;
BEGIN
    -- 当年没数据时，COALESCE会把NULL转为0，加一后就是1
    SELECT COALESCE(MAX(receipt_id), 0) + 1 INTO next_id
    FROM your_table
    WHERE receipt_year = NEW.receipt_year;
    
    NEW.receipt_id := next_id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

第二步：绑定触发器到插入操作 #

CREATE TRIGGER trigger_set_receipt_id
BEFORE INSERT ON your_table
FOR EACH ROW
EXECUTE FUNCTION get_next_receipt_id();

之后你插入数据时，完全不用管receipt_id，触发器会自动帮你填充：

INSERT INTO your_table (col1, col2, receipt_year) VALUES ('lol', 'lpl', 2019);

方案2：按年份创建独立序列（适合大数据量） #

如果你的表数据量很大，每次插入都查MAX会有性能损耗，可以给每个年份单独建一个序列，插入时直接取序列值：

第一步：创建生成序列名的辅助函数 #

CREATE OR REPLACE FUNCTION get_yearly_receipt_seq(year INT)
RETURNS TEXT AS $$
BEGIN
    RETURN 'receipt_seq_' || year;
END;
$$ LANGUAGE plpgsql;

第二步：创建自动维护序列的触发器 #

CREATE OR REPLACE FUNCTION trigger_set_yearly_receipt_id()
RETURNS TRIGGER AS $$
DECLARE
    seq_name TEXT;
BEGIN
    seq_name := get_yearly_receipt_seq(NEW.receipt_year);
    
    -- 如果对应年份的序列不存在，自动创建
    IF NOT EXISTS (SELECT 1 FROM pg_class WHERE relname = seq_name) THEN
        EXECUTE 'CREATE SEQUENCE ' || seq_name || ' START WITH 1';
    END IF;
    
    -- 获取序列的下一个值作为receipt_id
    EXECUTE 'SELECT nextval(''' || seq_name || ''')' INTO NEW.receipt_id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_set_yearly_receipt_id
BEFORE INSERT ON your_table
FOR EACH ROW
EXECUTE FUNCTION trigger_set_yearly_receipt_id();

这种方式性能更好，因为序列是PostgreSQL原生的自增机制，比查MAX高效得多。

要是你不想改数据库逻辑，那一定要给查询加锁，避免并发冲突：

$pdo->beginTransaction();
try {
    $current_year = date('Y');
    
    -- 用FOR UPDATE锁定查询结果，防止其他请求同时读取
    $stmt = $pdo->prepare("SELECT MAX(receipt_id) AS receipt_id FROM your_table WHERE receipt_year = :year FOR UPDATE");
    $stmt->execute(['year' => $current_year]);
    $result = $stmt->fetch(PDO::FETCH_ASSOC);
    $next_receipt_id = $result['receipt_id'] ? $result['receipt_id'] + 1 : 1;
    
    -- 插入数据
    $insertStmt = $pdo->prepare("INSERT INTO your_table (col1, col2, receipt_id, receipt_year) VALUES (:col1, :col2, :receipt_id, :year)");
    $insertStmt->execute([
        'col1' => 'lol',
        'col2' => 'lpl',
        'receipt_id' => $next_receipt_id,
        'year' => $current_year
    ]);
    
    $pdo->commit();
} catch (Exception $e) {
    $pdo->rollBack();
    throw $e;
}

这里的FOR UPDATE会锁定查询到的行（如果当年有数据），确保同一时间只有一个请求能获取并更新receipt_id，避免重复值。

内容的提问来源于stack exchange，提问作者Deepak Kumar