欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

sqlserver数据库主键的生成方式小结(sqlserver,mysql)

程序员文章站 2023-12-13 16:29:40
主键的生成方式主要有三种: 一. 数据库自动生成 二. guid 三. 开发创建 严格讲这三种产生方式有一定的交叉点,其定位方式将在下面进行讲解。 第一种方式,主要将其定位...
主键的生成方式主要有三种:
一. 数据库自动生成
二. 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'即可。
如前面所说,这同样适应于单据编号类似编码的产生形式,只要对前面的代码及键值表稍作修改即可,有兴趣的朋友可以一试。如果是从前端取得这个编号,并应用于各个记录,那么可能存在跳号的可能。如果为了保证不存在跳号,一种解决方案就是使用跳号表,将跳号记录定期扫描并应用于其他记录。另一种解决方案是将记录的保存操作放置到编号产生的过程中,形成一个串行化的事务。

俗话说萝卜白菜各有所爱,您用哪一种自有你的道理。

上一篇:

下一篇: