萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

SQL Server 流水號自動生成函數:BH 前綴 + 6 位數字遞增

自定義流水號格式:前綴固定 + 數字零填充遞增

業務常需生成如 BH000001、BH000002 格式的單號。本文提供一個純 T-SQL 純量函數,自動查詢現有最大值並生成下一號。

函數定義

CREATE FUNCTION dbo.f_NextBH()
RETURNS CHAR(8)
AS
BEGIN
    DECLARE @next CHAR(8);
    SELECT @next = 'BH' + RIGHT('000000' + CAST(ISNULL(CAST(RIGHT(MAX(BH), 6) AS INT), 0) + 1 AS VARCHAR(6)), 6)
    FROM YourTable;  -- 替換為實際存放流水號的表
    RETURN @next;
END;
GO

邏輯拆解

  1. MAX(BH):取現有最大單號 (如 BH000015)。
  2. RIGHT(..., 6):取後 6 位數字部份 (000015)。
  3. CAST(... AS INT):轉數值 15。
  4. ISNULL(..., 0):無資料時從 0 開始。
  5. + 1:遞增 → 16。
  6. CAST(... AS VARCHAR(6)) → '16'。
  7. RIGHT('000000' + ..., 6):左補零 → '000016'。
  8. 'BH' + ... → 'BH000016'。

使用範例

-- 插入新單據
DECLARE @newBH CHAR(8) = dbo.f_NextBH();
INSERT INTO Orders (BH, CustName, Amt) VALUES (@newBH, '客戶A', 1000);
SELECT @newBH;  -- 顯示 BH000016

併發安全警示與改進

上述函數在高併發下會產生重複號碼 (SELECT MAX 與 INSERT 非原子)。生產環境必須採用以下任一機制:

方案 A:SEQUENCE 物件 (SQL Server 2012+,推薦)

CREATE SEQUENCE dbo.seq_BH AS INT START WITH 1 INCREMENT BY 1 CACHE 100;
-- 取號
SELECT 'BH' + RIGHT('000000' + CAST(NEXT VALUE FOR dbo.seq_BH AS VARCHAR(6)), 6);
  • 原子性、高效能、可跨表共用、支援緩存。

方案 B:IDENTITY 欄位 + 計算欄位/視圖

CREATE TABLE Orders (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    BH AS 'BH' + RIGHT('000000' + CAST(Id AS VARCHAR(6)), 6) PERSISTED,
    CustName NVARCHAR(100),
    Amt DECIMAL(12,2)
);
  • 零維護、自動唯一、查詢可直接用 BH 索引 (PERSISTED)。

方案 C:序列表 + 交易鎖 (舊版本相容)

CREATE TABLE Seq_BH (Id INT NOT NULL DEFAULT 0);
INSERT INTO Seq_BH VALUES (0);

-- 取號預存程序
CREATE PROC usp_GetNextBH @BH CHAR(8) OUTPUT
AS
BEGIN
    BEGIN TRAN;
    UPDATE Seq_BH SET Id = Id + 1;
    SELECT @BH = 'BH' + RIGHT('000000' + CAST(Id AS VARCHAR(6)), 6) FROM Seq_BH;
    COMMIT TRAN;
END

最佳實務建議

  1. 新專案 優先用 SEQUENCE 或 IDENTITY+計算欄位,避免自寫函數併發 Bug。
  2. 流水號勿作為業務主鍵,主鍵用無意義 ID (INT/BIGINT/UUID),流水號作為業務唯一鍵/顯示碼。
  3. 若需按年/月重置 (如 BH2024010001),SEQUENCE 需搭配年度重置 Job,或改用 MAX(CASE WHEN BH LIKE 'BH202401%' THEN ...) 邏輯。
copyright © 萬盛學電腦網 all rights reserved