sqlserver数据库主键的生成方式有哪些

59次阅读
没有评论

共计 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 行业资讯频道了解更多相关知识。

正文完
 
丸趣
版权声明:本站原创文章,由 丸趣 2023-07-18发表,共计5192字。
转载说明:除特殊说明外本站除技术相关以外文章皆由网络搜集发布,转载请注明出处。
评论(没有评论)