SQL 並發安全的字母遞增流水號生成策略：輔助表 + 鎖機制

業務規則複雜度分析

流水號格式：HFR-{Code}-{Date}{Suffix}
Suffix 規則：無後綴 → A → B → ... → Z → ZA → ZB → ... → ZZ → ZZA ... (類似 Excel 欄位命名法，26 進位無零)。
核心挑戰：高併發下「讀取最大後綴 → 計算下一個 → 寫入」的原子性。

方案一：輔助表 + 悲觀鎖 (原答案核心思路)

-- 1. 建立後綴編碼表 (一次性初始化 10000+ 筆)
CREATE TABLE SuffixCode (
    seq INT PRIMARY KEY,      -- 1,2,3...
    code VARCHAR(10) NOT NULL -- '', 'A', 'B', ..., 'Z', 'ZA', ...
);
INSERT INTO SuffixCode VALUES (1, ''), (2, 'A'), ...; -- 以遞迴 CTE 或程式批次產生

-- 2. 存儲過程 / 事務邏輯 (SQL Server T-SQL 範例)
CREATE PROC GetNextSerial @Prefix VARCHAR(20), @Code VARCHAR(10), @Date VARCHAR(8), @Serial VARCHAR(50) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @BaseKey VARCHAR(50) = @Prefix + '-' + @Code + '-' + @Date;
    DECLARE @LastSuffix VARCHAR(10), @NextSeq INT, @NextSuffix VARCHAR(10);
    
    BEGIN TRAN;
    -- 關鍵：UPDLOCK, HOLDLOCK 強制序列化讀取最新流水號
    SELECT @LastSuffix = ISNULL(MAX(SUBSTRING(serial_no, LEN(@BaseKey)+1, 10)), '') 
    FROM SerialTable WITH (UPDLOCK, HOLDLOCK) 
    WHERE serial_no LIKE @BaseKey + '%';
    
    SELECT @NextSeq = seq + 1 FROM SuffixCode WHERE code = @LastSuffix;
    SELECT @NextSuffix = code FROM SuffixCode WHERE seq = @NextSeq;
    
    SET @Serial = @BaseKey + @NextSuffix;
    INSERT INTO SerialTable(serial_no, create_time) VALUES(@Serial, GETDATE());
    COMMIT TRAN;
    
    SELECT @Serial; -- 返回給呼叫端
END

方案二：資料庫序列 + 應用層編碼 (高效能、低衝突)

若業務允許「後綴不連續但單調遞增」，可大幅簡化：

-- 1. 建立 Sequence (PostgreSQL / Oracle / SQL Server 2012+)
CREATE SEQUENCE serial_seq START WITH 1 INCREMENT BY 1;

-- 2. 應用層 (Java/C#) 生成後綴
public String nextSuffix(long seq) { // seq 從 Sequence 取得
    if (seq == 1) return ""; // 第一筆無後綴
    seq--; // 對齊 0-based: 0->A, 25->Z, 26->ZA
    StringBuilder sb = new StringBuilder();
    while (seq >= 0) {
        sb.insert(0, (char)('A' + (seq % 26)));
        seq = seq / 26 - 1; // 26 進位無零邏輯
    }
    return sb.toString();
}
// 組合：prefix + "-" + code + "-" + date + nextSuffix(nextVal('serial_seq'))

• 優點：無資料庫鎖競爭，Sequence 獲取極快 (緩存)。
• 缺點：重啟/回滾導致序列跳號，後綴可能不連續 (如 A, B, D 缺 C)，但通常可接受。

方案三：Redis Lua 腳本原子遞增 (分散式高併發首選)

-- Redis Key 設計: serial:{prefix}:{code}:{date} -> Hash { seq: 123, last_suffix: "B" }
-- Lua 腳本 (EVALSHA 原子執行)
local key = KEYS[1]
local data = redis.call('HMGET', key, 'seq', 'last_suffix')
local seq = tonumber(data[1]) or 0
local last = data[2] or ''
local next_seq = seq + 1
local next_suffix = encode_suffix(next_seq) -- 用 Lua 實作 26 進位編碼
redis.call('HMSET', key, 'seq', next_seq, 'last_suffix', next_suffix)
return next_suffix

• 毫秒級吞吐，天然分散式安全。
• 需處理 Redis 持久化/備份策略，避免重啟後序列重置。

Excel 欄位命名法編碼演算法參考 (遞迴/迴圈)

// 1-based: 1->A, 26->Z, 27->AA, 702->ZZ, 703->AAA
String encode(int n) { // n >= 1
    StringBuilder sb = new StringBuilder();
    while (n > 0) {
        n--; // 調整為 0-based
        sb.insert(0, (char)('A' + n % 26));
        n /= 26;
    }
    return sb.toString();
}
// 本題規則：第一筆無後綴，故 seq=1 返回 ""，seq=2 返回 "A"...
String businessEncode(long seq) {
    if (seq == 1) return "";
    return encode(seq - 1);
}

選型建議

原答案的輔助表法屬於方案一，邏輯正確但需注意 UPDLOCK, HOLDLOCK 鎖升級風險；生產環境建議根據架構演進至方案二或三。