淺談ＳＱＬ Ｓｅｒｖｅｒ中的編號方法

摘要：在數(shù)據(jù)庫開發(fā)時(shí)，編號問題是必須要考慮的問題。該文主要介紹了自動編號與手工編號的幾種編號方法，通過具體實(shí)例討論它們的生成方法、實(shí)現(xiàn)過程及優(yōu)缺點(diǎn)。用戶可以結(jié)合自己的實(shí)際需要來選擇合適的編號方法。

關(guān)鍵詞：編號方法；SQL Server；數(shù)據(jù)庫

中圖分類號：TP311文獻(xiàn)標(biāo)識碼：A文章編號：1009-3044(2008)24-1109-04

Brief Analysis of Coding Methods in SQL Server

YAN Hui-qin

(Wuxi Institute of Technology，Wuxi 214073，China)

Abstract: In database development， the code is to consider the issue.Through specific examples to discuss their production methods， process ，advantages and disadvantages，this paper describes severalautomatic and manual coding ways.The user can combine their actual needs to choose a suitable coding method.

Key words: coding methods;SQL Server;database

1 引言

在開發(fā)數(shù)據(jù)庫系統(tǒng)時(shí)，一般用編號作為數(shù)據(jù)表的主鍵，編號具有連續(xù)性、唯一性等特點(diǎn)。編號一般不由用戶錄入，而是由處理程序自動生成。該文將通過具體實(shí)例討論幾種編號的生成方法、實(shí)現(xiàn)過程，及它們的優(yōu)缺點(diǎn)。

2 自動編號（identity）

在SQL Server中，實(shí)現(xiàn)自動編號最簡單的方法是使用標(biāo)識列（Identity）。標(biāo)識列定義的字段具有這樣的性質(zhì)：當(dāng)用戶在表中插入記錄時(shí)，標(biāo)識列的值由SQL Server根據(jù)創(chuàng)建列時(shí)定義的增量值、初始值和當(dāng)前表中最大標(biāo)識值自動分配，而不需要用戶手工指定。

在創(chuàng)建表時(shí)，使用Identity屬性指定標(biāo)識列，也可以使用Identity屬性為已創(chuàng)建的表添加標(biāo)識列。

Identity屬性語法如下：Identity[(seed，incremnet)]。參數(shù)Seed：表示種子，指定表中第一行所使用的值，即標(biāo)識列的初始值。Increment：指定增量值，該值被添加到前一個(gè)的標(biāo)識值上，產(chǎn)生下一個(gè)標(biāo)識值。兩個(gè)參數(shù)必須同時(shí)指定，或二者都不指定，則取默認(rèn)值（1，1），表示標(biāo)識列從1開始，增量為1。

例如：創(chuàng)建學(xué)生表TblStudent，指定Stud_Num字段為標(biāo)識列。語句如下：

CREATE TABLE TblStudent

(Stud_Num Int NOT NULL Identity(1，1)， --編號 非空

Stud_Id Varchar(12) NOT NULL Primary Key， --學(xué)號

Stud_Name Varchar(8) NOT NULL， --姓名

Stud_Sex Char(2)，--性別

Birth Datetime，--出生日期

Enroll_Date Datetime Default(getdate())， --入學(xué)年份

Nation Varchar(10)，--民族

Native_Place Varchar(20)， --籍貫

Identity_Id Varchar(18)， --身份證號

Address Varchar(40)， --家庭地址

Postcode char(6)，--郵政編碼

Class_Id Varchar(10)， --班級號

Photo Image--照片

)

可以使用Alter Table語句為已有的表增加一個(gè)字段并指定為自動編號字段Identity列。語法如下：

Alter Table 表名 Add 字段名Int IDENTITY(1，1)。

Identity屬性在自動編號應(yīng)用中需要注意的問題

使用Identity屬性實(shí)現(xiàn)自動編號，簡單方便，不需要使用額外的代碼來維護(hù)。但是，就Identity屬性本身來說，并不能保證唯一性，可以通過為某個(gè)表設(shè)置Identity_insert為ON，在標(biāo)識列中強(qiáng)制插入值，可能會使標(biāo)識值重復(fù)。所以使用標(biāo)識列作為表的主鍵時(shí)，同樣要在該列上設(shè)置主鍵約束才能保證其唯一性。

3 手工編號

自動編號的優(yōu)點(diǎn)是方法簡單，不需要額外的代碼，由數(shù)據(jù)庫控制產(chǎn)生編號，不必?fù)?dān)心在多用戶環(huán)境下，生成的編號產(chǎn)生沖突。不過，缺點(diǎn)也是顯然的，由于是數(shù)據(jù)庫控制生成，無法預(yù)料下一個(gè)編號是什么，也不能控制它的生成規(guī)則，這樣生成的編號可讀性較差。

若要使編號有意義，例如單據(jù)編號，希望通過查看單據(jù)編號能知道此單據(jù)的日期和其他相關(guān)信息，而不是一組毫無意義的數(shù)字。用戶可以編寫代碼，按需要自定義規(guī)則生成編號。

3.1 流水號

流水編號是一種順序編號，由固定長度的數(shù)字或固定字符開頭加固定長度的標(biāo)識數(shù)據(jù)構(gòu)成編號。這種編號只需查詢出表中最大的編號，然后將編號加1，再轉(zhuǎn)換為固定格式即可。

舉例，以課程表tblCourse中課程編號Course_id為例，長度為8，以KC開頭，其余6位為流水號。通過max()函數(shù)取得當(dāng)前最大的編號，right()函數(shù)截取后6位流水號，如果是空則從0開始；加上1000001量，這樣可以在個(gè)位上加1，再截取后6位，得到新的流水號，以’ KC’開頭即可。如：第1條記錄，Course_id為NULL，得到編號KC000001；第2條記錄，取到最大號KC000001，取后6位得到000001，加上1000001得到1000002，取后6位得到000002，再以’ KC’開頭，得到新的編號KC000002，如此進(jìn)行下去。具體實(shí)現(xiàn)過程參照如下。

--創(chuàng)建函數(shù)Course_Id_Fun，獲得8位新編號

Create Function Course_id_fun( ) returns char(8)

as

begin

return ( select 'KC'+ Right(Is1(Right(max(course_id)，6)，0)+1000001，6)

from tblcourse )

end

go

--創(chuàng)建課程表tblcourse，設(shè)置Course_Id字段的默認(rèn)值為調(diào)用函數(shù)dbo.course_id_fun()的返回值。

Create table TblCourse

(Course_Id Varchar(8) Not Null Primary Key default dbo.course_id_fun()，--課程編號

Course_Name Varchar(30) Not Null， --課程名稱

Hours Int Check(Hours>=0)， --課時(shí)數(shù)

)

go

--向課程表中添加課程記錄

insert into tblcourse(course_name，hours) values('大學(xué)語文'，48)

insert into tblcourse(course_name，hours) values('高等數(shù)學(xué)'，64)

insert into tblcourse(course_name，hours) values('計(jì)算機(jī)導(dǎo)論'，56)

--查詢課程情況

Select * from tblcourse

查詢結(jié)果圖1所示。

流水號是最常見的一種編號方法，在單用戶環(huán)境下沒有問題，如果多用戶并發(fā)使用，就可能產(chǎn)生重復(fù)。比如，用戶A調(diào)用函數(shù)獲得一個(gè)新編號，在未保存到數(shù)據(jù)庫之前，用戶B也調(diào)用函數(shù)來獲取新編號，顯然，用戶A 、B將獲得相同的編號。要解決多用戶并發(fā)操作時(shí)，產(chǎn)生重復(fù)編號的問題，可以使用事務(wù)，配合鎖定提示來解決。方法如下：

Create Function Course_id_fun( ) returns char(8)

as

begin

return ( select 'KC'+ Right(Is1(Right(max(course_id)，6)，0)+1000001，6)

from tblcourse with(xlock，paglock) ) --使用排它鎖頁鎖paglock

end

go

3.2 按日期生成編號

按日期編號的原理與生成流水號相同，不同之處在于它需要查詢的最大編號是查詢當(dāng)前日期的數(shù)據(jù)中的最大編號。編號中的日期部分可通過GetDate函數(shù)獲取，轉(zhuǎn)換成指定的日期格式即可。

舉例，客戶投訴表tblCus_com中的投訴編號Cus_com_ID字段，編號長度為12位，前8位為日期信息，格式為YYYYMMDD，后4位為流水號，如編號200712010001。先創(chuàng)建視圖Getdate_view，按指定格式獲得當(dāng)前日期，創(chuàng)建函數(shù)BH_fun()，按指定格式顯示獲得新編號，具體實(shí)現(xiàn)過程參照如下

--創(chuàng)建視圖，獲得當(dāng)前日

Create view Getdate_view

as

select convert(char(20)，getdate()，112) as 'rq'

go

其中，CONVERT表示將某種數(shù)據(jù)類型的表達(dá)式顯式轉(zhuǎn)換為另一種數(shù)據(jù)類型。語法格式為：CONVERT (data_type[(length)]， expression [， style]) ，給 style 值加 100，可獲得包括世紀(jì)數(shù)位的四位年份 (yyyy)。如：112表示日期格式為 yyyymmdd，12表示yymmdd；

--創(chuàng)建函數(shù)，按指定格式顯示獲得新編號

Create function BH_fun()

returns char(12)

as

begin

declare @rq char(8)

select @rq=rq from Getdate_view --調(diào)用視圖，取得當(dāng)前日期，如20071201

return ( select @rq+right ( is1(right(max(tblCus_com)，4)，0)+10001，4)--如200712010001

from tb with(xlock，paglock)

where bh like @rq+'%' )

end

go

--創(chuàng)建客戶投訴表，設(shè)置Cus_com_ID字段的默認(rèn)值為調(diào)用函數(shù)dbo.bh_fun()的返回值。

create table tblCus_com

(Cus_com_ID char(12) primary key default dbo.bh_fun()， --投訴編號

Cus_com_Time datetime， --投訴時(shí)間

Complainant varchar(20) ， --投訴人

Reason varchar(40) not 1， --投訴原因

Accused char(20) ， --投訴對象

Tel char(20) ， --聯(lián)系方式

Status char(10)) --狀態(tài)

--向客戶投訴表中添加信息

insert into tblCus_com (Cus_com_Time，Complainant，Reason，Accused，Tel，Status)

values('2007-03-10'，'張先生'，'服務(wù)態(tài)度差，收費(fèi)高'，'維修組小王'，'13506170442'，'未處理')

--查詢客戶投訴情況

select * from tblCus_com

查詢結(jié)果圖2所示。

即使一個(gè)系統(tǒng)中，有多個(gè)使用相同編號規(guī)則處理的編號，也需要單獨(dú)編寫?yīng)毩⒌淖远x函數(shù)。

以上兩種編號法基本思想是查詢表中編號最大的值，將這個(gè)值加1得到新的編號，也稱為查表法。使用查表法，如果刪除表中最大的記錄，則這些編號被回收，新生成的編號可以繼續(xù)使用這些編號。

查表法生成編號的最大問題在于：為保證生成的編號不重復(fù)，必須使用鎖定提示來阻止在生成編號后保存數(shù)據(jù)之前，其他用戶對表的訪問，不管用戶的訪問是正常讀取數(shù)據(jù)還是生成編號，都會受到鎖的影響，若處理過程很快，不會引起問題；若表很大，查詢數(shù)據(jù)并且生成編號，然后將數(shù)據(jù)保存到表中的時(shí)間就可能比較長，有多個(gè)用戶并發(fā)操作此表，則可能造成進(jìn)程堵塞，嚴(yán)重的會引起死鎖。

3.3 獨(dú)立編號表法

建立一個(gè)單獨(dú)的編號表，保存每種編號的最新編號值，獲取新編號時(shí)，只需要查詢編號表即可，由于編號表的內(nèi)容不會太多，造成進(jìn)程堵塞或發(fā)生死鎖的可能性很小。

--創(chuàng)建編號表Tb_NO，用于記錄待產(chǎn)生編號表及表中編號的情況

create table Tb_NO(

name char(20) primary key，--待產(chǎn)生編號的表名

head nvarchar(10)not 1 default''，--編號的前綴，默認(rèn)值為空

currentNo int not 1 default 0，--當(dāng)前的編號數(shù)據(jù)

BHlen int not 1 default 6， --編號數(shù)字部分的長度

descript nvarchar(40))--對編號的描述

--向編號表Tb_NO中添加記錄，記錄客戶投訴表中的編號信息

insert into tb_no select ' tbl Cus_com’，'TS'，0，4，'客戶投訴表編號'

--創(chuàng)建存儲過程，產(chǎn)生新編號

create proc pro_nextNO

@Name char(20)，--待產(chǎn)生編號的表名

@NO nvarchar(20) output --返回新編號

as

begin tran

update tb_no with(rowlock)

set @NO=head+right(power(10，BHlen)+currentNo+1，BHlen)，

currentno=currentno+1

where name=@Name

commit tran

go

通過power(10，BHlen)產(chǎn)生一個(gè)量，如10000，在當(dāng)前的編號數(shù)據(jù)上加1，再取得指定長度，加上編號的前綴即得到一個(gè)新編號，如TS0001。在生成編號的處理上使用行級鎖rowlock，防止多個(gè)用戶在同一表上同時(shí)更新。

由于函數(shù)中無法更新表，所以用編號表法生成編號只能使用存儲過程，在應(yīng)用時(shí)，調(diào)用存儲過程獲取編號，然后插入到表中，不能像查表法直接為編號字段設(shè)置默認(rèn)值。

declare @bh char(6)

exec pro_nextNO'tblCus_com' ，@bh out

select @bh

查表法的優(yōu)點(diǎn)在于，生成編號時(shí)，僅僅通過控制編號表來實(shí)現(xiàn)生成不重復(fù)的編號，在處理過程中，不需要鎖定基礎(chǔ)數(shù)據(jù)表，在很大程度上防止了進(jìn)程堵塞或者死鎖。

由于編號表中記錄了生成的最后一個(gè)編號，所以刪除基礎(chǔ)數(shù)據(jù)表中編號最大的記錄，編號不會被回收。例如表中已使用TS0001、TS0002、TS0003，刪除了TS0002、TS0003記錄，由于編號表中已記錄了當(dāng)前生成的最大編號為3，所以刪除后使用編號表法生成的新編號為TS0004。

4 結(jié)束語

對于連續(xù)編號來說，無論使用SQL Server系統(tǒng)自動編號，還是編寫代碼生成編號，都會因?yàn)閿?shù)據(jù)表中的數(shù)據(jù)刪除而導(dǎo)致編號不連續(xù)，可以檢查空缺并補(bǔ)號，從而保證編號的連續(xù)性。在數(shù)據(jù)庫開發(fā)時(shí)，編號問題是必須要考慮的問題，本文主要介紹了4種編號方法，用戶可以結(jié)合自己的實(shí)際需要來選擇合適的編號方法。

參考文獻(xiàn)：

[1] 鄒建.中文版SQLServer2000開發(fā)與管理應(yīng)用實(shí)例[M].北京:人民郵電出版社，2005:123-138.

[2] 何旵陽.多機(jī)共享下自動連續(xù)編號的算法研究[J].軟件導(dǎo)刊，2007(11):154-155.