sqlserver數據庫主鍵的生成方式有哪些

156次閱讀

沒有評論

共計 5192 個字符，預計需要花費 13 分鐘才能閱讀完成。

sqlserver 數據庫主鍵的生成方式有哪些，針對這個問題，這篇文章詳細介紹了相對應的分析和解答，希望可以幫助更多想解決這個問題的小伙伴找到更簡單易行的方法。

主鍵的生成方式主要有三種：

一. 數據庫自動生成

二. GUID

三. 開發創建

嚴格講這三種產生方式有一定的交叉點，其定位方式將在下面進行講解。

第一種方式，主要將其定位在自增長的標識種子：可以設置起始數值，及增長步長。其優點在于使用時完全將并發任務交于數據庫引擎管理，你不用擔心存在多用戶使用的時候會產生兩個相同的 ID 的情況。其缺點也在于此，多數的數據庫不提供直接獲取標識 ID 的方式，對于開發人員來說產生 ID 的方式是透明的，開發人員幾乎無法干預此項。對于數據的遷移也不是很方便。由于存在上面的利弊，這種自增長的 ID 一般多用于設計基礎表（系統運行的基礎信息，如員工表）主鍵，而極少（根本不）用于主從表主、外鍵，因為在產生主從表數據并關聯時，必須確定主表的 ID，然后才能定位從表的關聯 ID。

例 (MsSQL):

代碼如下:

– 創建測試表 CREATE TABLE [Identity](Id INT IDENTITY(1,2) NOT NULL PRIMARY KEY,

– 種子的起始值 1，步長 2 Number VARCHAR(20) UNIQUE NOT NULL, Name VARCHAR(20) NOT NULL, Password VARCHAR(20) DEFAULT(123), Description VARCHAR(40) NULL )

– 插入記錄 INSERT INTO [Identity](Number,Name,Description) VALUES(001 , 1st , Id=1, 因為起始值 1)

INSERT INTO [Identity](Number,Name,Description) VALUES(002 , 2nd , Id=3, 因為起始值 1，步長 2)

INSERT INTO [Identity](Number,Name,Description) VALUES(003 , 3rd , Id=5, 由于字符長度超長，報錯插入失敗，造成此 Id 產生后被放棄)

INSERT INTO [Identity](Number,Name,Description) VALUES(004 , 4th , Id=7 not 5, 因為第三條記錄插入失敗)

– 檢索記錄，查看結果 SELECT * FROM [Identity]

結果：(1 行受影響) (1 行受影響) 消息 8152，級別 16，狀態 14，第 3 行將截斷字符串或二進制數據。語句已終止。(1 行受影響) (3 行受影響) Id Number Name Password Description 1 001 1st 123 Id=1, 因為起始值 1 3 002 2nd 123 Id=3, 因為起始值 1，步長 2 7 004 4th 123 Id=7 not 5, 因為第三條記錄插入失敗第二種方式，GUID 即 Globally Unique Identifier，也稱為 UUID（Universally Unique IDentifier），全球唯一標識符，GUID 一般由 32 位十六進制的數值組成，其中包含網卡地址、時間及其他信息。任何兩臺電腦都不會產生相同的 GUID，他的優點在唯一性，當需要數據庫整合時，能節約不少勞動力。比如總公司和分公司各自系統獨立運行，所有分公司數據定期需要提交到總部，可以避免合并數據時主鍵沖突問題，同時 GUID 還兼具自增長標識種子特點，無需開發人員太多的關注。但是 GUID 信息量大，占用空間也大，關聯檢索時，估計效率上也不是很高，對于 32 位的十六進制其可讀性也差，雖然主鍵有對用戶的無意義性，但是在設計或者調試交流時很不方便。從長遠考慮，為了保證數據的可移植性，一般還是會選擇使用 GUID 來作為主鍵。

例 (MsSQL):

代碼如下:

– 創建測試表 CREATE TABLE GUID(Id UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,

– 當然你也可以用字符串來保存 Number VARCHAR(20) UNIQUE NOT NULL, Name VARCHAR(20) NOT NULL, Password VARCHAR(20) DEFAULT(123) )

– 插入記錄 INSERT INTO GUID(Id,Number,Name) VALUES(NewID(), 001 , 1st )

INSERT INTO GUID(Id,Number,Name) VALUES(NewID(), 002 , 2nd )

INSERT INTO GUID(Id,Number,Name) VALUES(NewID(), 003 , 3rd )

– 檢索記錄，查看結果 SELECT * FROM GUID

結果：Id Number Name Password 8E194F55-B4D3-4C85-8667-33BC6CD33BBC 001 1st 123 7141F202-7D0E-4992-9164-5043EC9FC6F6 002 2nd 123 E0E365A0-8748-4656-AF24-5D0B216D2095 003 3rd 123 第三種方式開發創建，其便捷性在于可控制性，此可控制性是指其組成形式，可以是整形、也可以是字符型，你可以根據實際情況給予多樣的組成及產生形式，說到這里可能有的朋友就想起來自動產生單號，如：20120716001 或者 PI-201207-0001 等等，沒錯，自我創建同樣適用于這些類似的應用。說到自我創建，多數首先想到的是取 Max(Id)+1，這種方式雖然省事，但是實際上對于定制（在生產單號之類的有一定意義的信息時可能會有這樣的需求，主鍵沒必要）及并發的處理并不是很好。如，當前表中最大編號為 1000，當 C1 和 C2 用戶同時取這個 Id 處理時，得到的都是 1001，導致保存失敗。常規的做法是在取值時候加鎖，但是當多用戶頻繁操作時，性能是個很大的問題，其中主要的原因之一是直接操作的業務數據表。針對此種情況，解決方案是使用鍵值表來保存表名、當前或者下一個 Id 及其他信息，如果系統中多個表 Id 都使用這種方式，那么鍵值表中就會有多條相應的規則記錄；當然也可以讓整個數據庫所有表的 Id 從都按相同的規則從一個源產生，那么鍵值表中只需要一條規則記錄即可。

下面來看看這樣一個使用鍵值表例子的演變 (MsSQL):

代碼如下:

– 創建鍵值表 CREATE TABLE KeyTable(ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,

TCode VARCHAR(20) UNIQUE NOT NULL,

TName VARCHAR(50) NOT NULL,

TKey INT NOT NULL, )

GO

– 插入測試記錄 INSERT INTO KeyTable(TCode,TName,TKey) VALUES(T001 , Test ,0) GO

– 創建獲取指定表 ID 的存儲過程，也可以修改成函數 CREATE PROCEDURE UP_NewTableID @TCode VARCHAR(20),@NextID INT OUTPUT AS DECLARE @CurTKey INT,

@NextTKey INT BEGIN TRAN TransID SELECT @CurTKey=TKey FROM KeyTable

WHERE TCode = @TCode IF @@ROWCOUNT = 0 BEGIN ROLLBACK TRAN TransID RAISERROR(Warning: No such row is exists ,16,1) RETURN END SET

@NextTKey = @CurTKey + 1 –WAITFOR DELAY 00:00:05 UPDATE KeyTable SET TKey = @NextTKey

WHERE TCode = @TCode IF @@ROWCOUNT = 0 BEGIN ROLLBACK TRAN TransID RAISERROR(Warning: No such row is updated ,16,1) RETURN END COMMIT TRAN TransID SET @NextID = @NextTKey GO

執行存儲過程 UP_NewTableID

代碼如下:

DECLARE @NextID INT EXEC UP_NewTableID T001 ,

@NextID OUTPUT PRINT

@NextID 運行的時會發現很正常，獲取的結果也很正確。但是如果在高并發的情況，多個用戶可能就會獲取相同的 ID，如果獲取的 ID 后是用于保存對應表中的記錄，那么最多只有一個用戶能保存成功。

下面模擬一下并發情形，將上面的存儲過程 UP_NewTableID 中語句 WAITFOR DELAY 00:00:05 的注釋去掉，打開 3 個查詢分析器的窗體，依次執行上面語句。預期是想分別獲得 1,2,3，但是也許會發現多個窗體的運行結果都是：1。這就是說在更新語句執行之前，大家都獲取的 ID 是 0，所以下一個數值都是為 1。（實際的數值，根據 DELAY 的參數大小及運行時間按間隔有關）從這方面來分析的話有的朋友可能就會想到，是否可以在更新語句執行時判斷 ID 是不是原始 ID 了？

修改過程：

代碼如下:
ALTER PROCEDURE UP_NewTableID @TCode VARCHAR(20),

@NextID INT OUTPUT AS DECLARE @CurTKey INT,

@NextTKey INT BEGIN TRAN TransID SELECT

@CurTKey=TKey FROM KeyTable WHERE TCode=@TCode IF @

@ROWCOUNT=0BEGIN ROLLBACK TRAN TransID RAISERROR(Warning: No such row is exists ,16,1) RETURN END SET @NextTKey=@CurTKey+1 WAITFOR DELAY 00:00:05 UPDATE KeyTable SET TKey=@NextTKey WHERE TCode=@TCode AND TKey=@CurTKey– 此處加上 TKey 的校驗 IF @@ROWCOUNT=0BEGIN ROLLBACK TRAN TransID RAISERROR(Warning: No such row is updated ,16,1) RETURN END COMMIT TRAN TransID SET @NextID=@NextTKey GO

如果打開個 3 個執行過程來模擬并發，那么會有 2 個窗體出現：消息 50000，級別 16，狀態 1，過程 UP_NewTableID，第 28 行 Warning: No such row is updated 由此會看到還是會由于并發導致有用戶操作失敗，但是較上一個至少將錯誤出現的時間點提前了。那么有沒有更好的方法，從查詢到更新結束整個事務過程中，不會有任何其他事務插入其中來攪局的辦法呢，答案很明確，有，使用鎖！需要選擇適當的鎖，否則效果將和上面的一樣。

代碼如下:

ALTER PROCEDURE UP_NewTableID

@TCode VARCHAR(20),@NextID INT OUTPUT AS DECLARE

@CurTKey INT,@NextTKey INT BEGIN TRAN TransID SELECT

@CurTKey=TKey FROM KeyTable WITH (UPDLOCK)

– 采用更新鎖，并保持到事務完成 WHERE TCode=@TCode IF @@ROWCOUNT=0BEGIN ROLLBACK TRAN TransID RAISERROR(Warning: No such row is exists ,16,1) RETURN END SET

@NextTKey=@CurTKey+1 WAITFOR DELAY 00:00:05

UPDATE KeyTable SET TKey=@NextTKey WHERE TCode=@TCode

– 此處無需驗證 TKey 是否與 SELECT 的相同 COMMIT TRAN TransID SET @NextID=@NextTKey GO

可以打開 N（N =2) 個窗體來進行測試，將會看到所有操作都被串行化，結果就是我們想要的那樣。如此注釋或者去掉模仿并發的語句 WAITFOR DELAY 00:00:05 即可。

如前面所說，這同樣適應于單據編號類似編碼的產生形式，只要對前面的代碼及鍵值表稍作修改即可，有興趣的朋友可以一試。如果是從前端取得這個編號，并應用于各個記錄，那么可能存在跳號的可能。如果為了保證不存在跳號，一種解決方案就是使用跳號表，將跳號記錄定期掃描并應用于其他記錄。另一種解決方案是將記錄的保存操作放置到編號產生的過程中，形成一個串行化的事務。

關于 sqlserver 數據庫主鍵的生成方式有哪些問題的解答就分享到這里了，希望以上內容可以對大家有一定的幫助，如果你還有很多疑惑沒有解開，可以關注丸趣 TV 行業資訊頻道了解更多相關知識。

正文完