資料庫流水序號產生方式(T-SQL版)

2016-01-03

2483
0

有这样一个需求需要根据输入的编码（这个编码值来自于数据库的一个表）生成下一个编码，编码规则如下所示（我们暂且不关心这个逻辑是否合理，只关心如何实现）：

1: 最小值为A0000，最大值为ZZZZZ

2：编码A0000的下一个值为A0001，编码A9999的下一个值为B0000, 编码AB999的下一个值为AC000，编码AC999的下一个值为AD000，依此内推。

3：不用担心输入值为A09BC这种值，应用程序从表里面取编码的最大值。应用程序会检查、控制输入参数，不用在数据库的函数里面做检查控制。

4：不用担心输入值为ac908这种值（大小写问题），应用程序从表里获取编码的值（不接受用户输入）。所以这个检查、控制也不用纳入数据库函数考虑范围。

在群裡有天@潇湘隐者提了一個這樣的問題，蠻有趣的。當天就試了一下以下是我的思路跟測試語法

1: 最小值为A0000，最大值为ZZZZZ

2：编码A0000的下一个值为A0001，编码A9999的下一个值为B0000, 编码AB999的下一个值为AC000，编码AC999的下一个值为AD000，依此内推。

3：不用担心输入值为A09BC这种值，应用程序从表里面取编码的最大值。应用程序会检查、控制输入参数，不用在数据库的函数里面做检查控制。

2015.12.30 照了@剑走江湖提出的問題，我稍微用了點小技巧，解決了 :D 。小於5位會出現異常的問題的原因在於

我補0是依照當時傳入流水號數字長度來補 e.g A0001 則會補上4個零。但為什麼5位不會出現異常呢，其實是我設定流水序號為VARCHAR(5) 也就是最多只能存入五位

以我的程序來說當序號為Z9999時，其實是會組合成ZA0000，但剛好因為VARCHAR(5)，所以最後就變成了ZA000了。

透過這個思路，我稍微修改了一下。

多了以下幾個處理

1.記錄傳入序號的長度

2.回傳時我們依照當下應該回傳序號的長度，做截取字串的動作(SUBSTRING)

歡迎朋友再試看看唷！如果需要更長的序號，請把所有VARCHAR的長度拉長即可

以上是一種trick的做法，實際後面還是有多運算了一些。剛趁著下午又想了一個新做法，這次直接判斷邊界值即可，效能應該會比較好唷

IF ASCII(CAST (SUBSTRING( @Header,LEN (@Header), 1) as CHAR ))=90 
		 AND LEN (@Header)<>@SerialLength -- 判斷目前最大英文是否到Z 與 判斷頭英文字母長度是否已經到了我們序號長度
		 BEGIN
			SET @Header = @Header+'A'
		 END
		 IF ASCII(CAST (SUBSTRING( @Header,LEN (@Header), 1) as CHAR ))<>90 -- 如果沒有到 Z則英文數字就進位
		 BEGIN
			SET @Header = SUBSTRING(@Header ,1, LEN(@Header )-1) + CHAR(ASCII (CAST( SUBSTRING(@Header ,LEN( @Header),1 ) as CHAR))+ 1)
		 END
		 
	IF @SerialLength-LEN(@Header)>=0 --如果目前序號未被英文字母占滿 e.g ZZZZ0(未被占滿) ZZZZZ(占滿)
		SET @Number =REPLICATE('0',@SerialLength-LEN(@Header))
	ELSE
		SET @Number=''

以下是我寫的解法，適用於SQL Server 2008 R2 + (倒是沒在其它版本試過，不過Oracle我查了一下有相對應的函數。應該可以無痛切換)

CREATE FUNCTION [dbo].[GeneratorKey]
(
	-- Add the parameters for the function here
	@Serial VARCHAR(5)
)
RETURNS VARCHAR(5)
AS
BEGIN
	DECLARE @Header VARCHAR( 5) =''; --頭編號
	DECLARE @Number VARCHAR( 5)='' ; -- 尾編號
	DECLARE @FullNumber VARCHAR( 5)='' ; --最後完整的序號
　　　　   DECLARE @SerialLength int =0 --用來儲存我們序號長度
             SET @SerialLength = LEN(@Serial) --這裡設定當下我們傳入序號的長度

--拆分傳入值為頭(字母 ) 尾( 數字)
	WHILE((ASCII (CAST( @Serial as CHAR)))>= 65)
	BEGIN
		 SET @Header=@Header +SUBSTRING( @Serial,1 ,1);
		 SET @Serial=SUBSTRING (@Serial, 2,LEN (@Serial)- 1)
	END
	--這裡@Serial 會只存放截出的[數字]，但因為下面計算@Serial實際的長度
	--需要用到原始數字的長度，所以多一個@Number來做存放
	SET @Number = @Serial;
--拆分結束
	--開始進行編號+1動作
	IF CAST (@Number as int)=CAST (REPLICATE( '9',LEN (@Number)) AS INT) --當編號準備要進位時
	BEGIN
		 IF ASCII(CAST (SUBSTRING( @Header,LEN (@Header), 1) as CHAR ))=90 -- 判斷目前最大英文是否到 Z
		 BEGIN
			SET @Header = @Header+'A'
		 END
		 ELSE -- 如果沒有到 Z則英文數字就進位
		 BEGIN
			SET @Header = SUBSTRING(@Header ,1, LEN(@Header )-1) + CHAR(ASCII (CAST( SUBSTRING(@Header ,LEN( @Header),1 ) as CHAR))+ 1)
		 END
	SET @Number =REPLICATE( '0',LEN (@Number))
	SET @FullNumber =@Header+ @Number; --設定最新流水號
	END
	ELSE
	BEGIN
		 SET @Number =CAST( @Number as int)+ 1;
		 --這裡就需要依照@Serial實際的長度來補多少個
		 --因為@Number 轉成int後就會去掉零
		 SET @Number = REPLICATE('0' ,LEN( @Serial)-LEN (@Number)) + @Number -- 補
		 SET @FullNumber =@Header+ @Number; --設定最新流水號
	END
	--PRINT @FullNumber
	RETURN SUBSTRING(@FullNumber,1,@SerialLength) --這裡改為截出我們預期長度的字串

END

流程大致是這樣的

1.我們將編號拆分成頭編號(英文)與尾編號(數字)分別存放，最後再合併成為一個完整的流水號

2.在14行我們做了將傳入的編號，拆分成頭編號與尾編號的動作。這裡就利用到ASCII函數永遠只會取第一碼的特性。假設傳入的是A0001就會回傳65，B0001則是66

所以我們可以永遠判斷第一位的ASCII碼是不是英文字母，是的話就截出來放入頭編號中。然後留下後續的字串符再重複做。直到沒有英文字母為止

3.因為我們編號已經拆為頭尾了，所以就很容易做相加的動作(因為我們要取得下一位的流水號)

首先我們判斷尾編號是否已經要進位了 (比如說9999或999或99這種，就是要進位了) 如果是的話就要做進位的動作

進位的動作是這樣的，我們要判斷的是目前最大的英文是否到Z如果是到Z了，就要補上新的頭編號。 e.g. Z9999 下一位會是ZA000

如果還沒有到Z就將目前最大的英文做進位的動作，e.g. A9999 -> B0000 。至於怎麼取最大的英文可以參考26行SUBSTRING的寫法

4.如果不需要進位的話，就簡單的將尾編號做加1的動作，都做完後就可以準備組成我們下一個流水號了。這裡我用了一個REPLICATE函數，他可以依照你設定的值，重複某一段文字

補零的動作其實就是找出我們原本傳入的尾編號長度，再扣掉我們運算後的尾編號長度。之所以要做這個動作是因為當我們將 '0001'這個編號轉成int做加1後，會變成2。原本的000就消失了

所以我們利用，原本的尾編號長度(0001) 扣掉我們運算後的尾編號長度(1) 。就知道我們需要重複3個0。

最後是我簡單的測試語法 :)

DECLARE @Serial VARCHAR( 5)='ZA000' --啟始的編號
DECLARE @Header VARCHAR( 5)=''
DECLARE @Count int= 50000; --要產生多少筆流水號
WHILE(@Count !=0)
BEGIN
        SELECT @Serial=dbo.GeneratorKey( @Serial);
        PRINT @Serial ;
        SET @Count =@Count- 1;
END

總結，我的思路是這樣的，透過英文可以轉成對應的ASCII碼，其實就可以利用ASCII碼做相加的動作。得到我們下一個要用到的英文字母

e.g. A的ASCII碼為65 ，我們可以先轉成65後做相加變成66，最後再轉回來英文字母，就會變成B :D

數字的部分就單純的拆解出來做相加就好了。

這裡有趣的地方是T-SQL ASCII這個函數不管你傳入多長的字串，他永遠只會回傳第一個字母的ASCII碼，這特性剛好讓我拿來做字母與數字拆分的動作 :D

有興趣的朋友可以協助測試唷！

回首頁