PostgreSQL中客户专属编号范围的维护方案问询

我来分享一套经过实践验证的实现方案，刚好之前在项目中处理过类似的客户专属编号管理需求，分几个核心模块来讲：

首先得给每个客户维护好专属的编号范围信息，建议用一张数据库表来存，字段至少要包含：

• customer_id：客户唯一标识（主键）
• start_num：客户指定的编号起始值
• end_num：客户指定的编号结束值
• current_num：当前已分配到的编号（初始值设为start_num - 1，这样第一次分配直接+1就是起始值）
• used_recycled_nums：存储已回收的编号（用JSON数组类型最合适，方便快速读写）

分配编号的时候要分三种情况处理，还要注意并发安全（必须加行锁或者用乐观锁，避免重复分配）：

2.1 正常自增阶段 #

如果当前编号还没到范围上限，直接自增分配：

-- 用行锁锁住当前客户的记录，防止并发冲突
BEGIN TRANSACTION;
SELECT * FROM customer_number_range WHERE customer_id = ? FOR UPDATE;

-- 计算新编号
UPDATE customer_number_range 
SET current_num = current_num + 1 
WHERE customer_id = ? AND current_num < end_num;

-- 获取分配的编号
SELECT current_num FROM customer_number_range WHERE customer_id = ?;
COMMIT;

2.2 编号耗尽后优先复用回收的编号 #

当current_num等于end_num时，先检查used_recycled_nums里有没有已回收的编号，有就直接取一个复用：

# 伪代码示例
def get_recycled_num(customer_id):
    with db.transaction():
        range_info = db.query("SELECT * FROM customer_number_range WHERE customer_id = ? FOR UPDATE", customer_id)
        if range_info.used_recycled_nums:
            # 弹出第一个回收的编号（也可以按FIFO/LIFO，看业务需求）
            recycled_num = range_info.used_recycled_nums.pop(0)
            db.execute("UPDATE customer_number_range SET used_recycled_nums = ? WHERE customer_id = ?", 
                       (range_info.used_recycled_nums, customer_id))
            return recycled_num
        return None

2.3 无回收编号时进入循环复用 #

如果回收列表为空，就回到起始编号重新开始，同时把整个范围的编号（除了刚分配的这个）都存入回收列表，方便后续复用：

BEGIN TRANSACTION;
SELECT * FROM customer_number_range WHERE customer_id = ? FOR UPDATE;

-- 回到起始编号
UPDATE customer_number_range 
SET current_num = start_num,
    used_recycled_nums = JSON_ARRAYAGG(num) 
FROM (SELECT num FROM generate_series(start_num, end_num) AS num WHERE num != start_num) AS nums
WHERE customer_id = ?;

SELECT start_num AS allocated_num FROM customer_number_range WHERE customer_id = ?;
COMMIT;

当某个domain object被删除、作废或者不再需要占用编号时，要把对应的编号放回回收列表，注意要校验编号是否属于该客户的范围：

// Java示例伪代码
public void recycleNumber(Long customerId, Integer num) {
    try (Connection conn = getConnection()) {
        conn.setAutoCommit(false);
        // 先查询客户的编号范围，确保编号合法
        String queryRangeSql = "SELECT start_num, end_num, used_recycled_nums FROM customer_number_range WHERE customer_id = ? FOR UPDATE";
        try (PreparedStatement stmt = conn.prepareStatement(queryRangeSql)) {
            stmt.setLong(1, customerId);
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                int start = rs.getInt("start_num");
                int end = rs.getInt("end_num");
                JSONArray recycledNums = rs.getJSONArray("used_recycled_nums");
                
                if (num >= start && num <= end && !recycledNums.contains(num)) {
                    recycledNums.add(num);
                    String updateSql = "UPDATE customer_number_range SET used_recycled_nums = ? WHERE customer_id = ?";
                    try (PreparedStatement updateStmt = conn.prepareStatement(updateSql)) {
                        updateStmt.setJSON(1, recycledNums);
                        updateStmt.setLong(2, customerId);
                        updateStmt.executeUpdate();
                    }
                }
            }
        }
        conn.commit();
    } catch (SQLException e) {
        // 异常处理
        conn.rollback();
    }
}

• 注册校验：客户注册指定编号范围时，要校验start_num <= end_num，避免无效范围；
• 并发控制：一定要用行锁（SELECT ... FOR UPDATE）或者乐观锁（加version字段），否则高并发场景下会出现重复分配编号的问题；
• 性能优化：如果客户的编号范围很大（比如百万级），第一次循环生成used_recycled_nums可能会慢，可以分批处理，或者用延迟初始化的方式，每次循环到某个编号时再标记为已使用；
• 业务适配：如果业务要求编号不能重复使用（即使回收了），那循环逻辑就改成报错或者自动申请新的范围，这个根据实际需求调整。

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