提供了编程的基础技术教程

zl程序教程

您现在的位置是：首页 >  数据库

当前栏目

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

mysqlSQLServer数据库 方式 生成 主键 小结
2023-06-13 09:14:34 时间
主键的生成方式主要有三种：
一.数据库自动生成
二.GUID
三.开发创建

严格讲这三种产生方式有一定的交叉点，其定位方式将在下面进行讲解。
第一种方式，主要将其定位在自增长的标识种子：可以设置起始数值，及增长步长。其优点在于使用时完全将并发任务交于数据库引擎管理，你不用担心存在多用户使用的时候会产生两个相同的ID的情况。其缺点也在于此，多数的数据库不提供直接获取标识ID的方式，对于开发人员来说产生ID的方式是透明的，开发人员几乎无法干预此项。对于数据的迁移也不是很方便。
由于存在上面的利弊，这种自增长的ID一般多用于设计基础表（系统运行的基础信息，如员工表）主键，而极少（根本不）用于主从表主、外键，因为在产生主从表数据并关联时，必须确定主表的ID，然后才能定位从表的关联ID。
例(MsSQL):
复制代码代码如下:

--创建测试表
CREATETABLE[Identity](
IdINTIDENTITY(1,2)NOTNULLPRIMARYKEY,--种子的起始值1，步长2
NumberVARCHAR(20)UNIQUENOTNULL,
NameVARCHAR(20)NOTNULL,
PasswordVARCHAR(20)DEFAULT(123),
DescriptionVARCHAR(40)NULL
)
--插入记录
INSERTINTO[Identity](Number,Name,Description)VALUES("001","1st","Id=1,因为起始值1")
INSERTINTO[Identity](Number,Name,Description)VALUES("002","2nd","Id=3,因为起始值1，步长2")
INSERTINTO[Identity](Number,Name,Description)VALUES("003","3rd","Id=5,由于字符长度超长，报错插入失败，造成此Id产生后被放弃")
INSERTINTO[Identity](Number,Name,Description)VALUES("004","4th","Id=7not5,因为第三条记录插入失败")
--检索记录，查看结果
SELECT*FROM[Identity]

结果：
(1行受影响)
(1行受影响)
消息8152，级别16，状态14，第3行
将截断字符串或二进制数据。
语句已终止。
(1行受影响)
(3行受影响)
IdNumberNamePasswordDescription
10011st123Id=1,因为起始值1
30022nd123Id=3,因为起始值1，步长2
70044th123Id=7not5,因为第三条记录插入失败
第二种方式，GUID即GloballyUniqueIdentifier，也称为UUID（UniversallyUniqueIDentifier），全球唯一标识符，GUID一般由32位十六进制的数值组成，其中包含网卡地址、时间及其他信息。任何两台电脑都不会产生相同的GUID，他的优点在唯一性，当需要数据库整合时，能节约不少劳动力。比如总公司和分公司各自系统独立运行，所有分公司数据定期需要提交到总部，可以避免合并数据时主键冲突问题，同时GUID还兼具自增长标识种子特点，无需开发人员太多的关注。但是GUID信息量大，占用空间也大，关联检索时，估计效率上也不是很高，对于32位的十六进制其可读性也差，虽然主键有对用户的无意义性，但是在设计或者调试交流时很不方便。
从长远考虑，为了保证数据的可移植性，一般还是会选择使用GUID来作为主键。
例(MsSQL):
复制代码代码如下:

--创建测试表
CREATETABLEGUID(
IdUNIQUEIDENTIFIERNOTNULLPRIMARYKEY,--当然你也可以用字符串来保存
NumberVARCHAR(20)UNIQUENOTNULL,
NameVARCHAR(20)NOTNULL,
PasswordVARCHAR(20)DEFAULT(123)
)
--插入记录
INSERTINTOGUID(Id,Number,Name)VALUES(NewID(),"001","1st")
INSERTINTOGUID(Id,Number,Name)VALUES(NewID(),"002","2nd")
INSERTINTOGUID(Id,Number,Name)VALUES(NewID(),"003","3rd")
--检索记录，查看结果
SELECT*FROMGUID

结果：
IdNumberNamePassword
8E194F55-B4D3-4C85-8667-33BC6CD33BBC0011st123
7141F202-7D0E-4992-9164-5043EC9FC6F60022nd123
E0E365A0-8748-4656-AF24-5D0B216D20950033rd123
第三种方式开发创建，其便捷性在于可控制性，此可控制性是指其组成形式，可以是整形、也可以是字符型，你可以根据实际情况给予多样的组成及产生形式，说到这里可能有的朋友就想起来自动产生单号，如：20120716001或者PI-201207-0001等等，没错，自我创建同样适用于这些类似的应用。
说到自我创建，多数首先想到的是取Max(Id)+1，这种方式虽然省事，但是实际上对于定制（在生产单号之类的有一定意义的信息时可能会有这样的需求，主键没必要）及并发的处理并不是很好。如，当前表中最大编号为1000，当C1和C2用户同时取这个Id处理时，得到的都是1001，导致保存失败。常规的做法是在取值时候加锁，但是当多用户频繁操作时，性能是个很大的问题，其中主要的原因之一是直接操作的业务数据表。
针对此种情况，解决方案是使用键值表来保存表名、当前或者下一个Id及其他信息，如果系统中多个表Id都使用这种方式，那么键值表中就会有多条相应的规则记录；当然也可以让整个数据库所有表的Id从都按相同的规则从一个源产生，那么键值表中只需要一条规则记录即可。
下面来看看这样一个使用键值表例子的演变(MsSQL):
复制代码代码如下:
--创建键值表
CREATETABLEKeyTable(
IDINTIDENTITY(1,1)PRIMARYKEYNOTNULL,
TCodeVARCHAR(20)UNIQUENOTNULL,
TNameVARCHAR(50)NOTNULL,
TKeyINTNOTNULL,
)
GO
--插入测试记录
INSERTINTOKeyTable(TCode,TName,TKey)
VALUES("T001","Test",0)
GO
--创建获取指定表ID的存储过程，也可以修改成函数
CREATEPROCEDUREUP_NewTableID
@TCodeVARCHAR(20),@NextIDINTOUTPUT
AS
DECLARE@CurTKeyINT,@NextTKeyINT
BEGINTRANTransID
SELECT@CurTKey=TKey
FROMKeyTable
WHERETCode=@TCode
IF@@ROWCOUNT=0
BEGIN
ROLLBACKTRANTransID
RAISERROR("Warning:Nosuchrowisexists",16,1)
RETURN
END
SET@NextTKey=@CurTKey+1
--WAITFORDELAY"00:00:05"
UPDATEKeyTable
SETTKey=@NextTKey
WHERETCode=@TCode
IF@@ROWCOUNT=0
BEGIN
ROLLBACKTRANTransID
RAISERROR("Warning:Nosuchrowisupdated",16,1)
RETURN
END
COMMITTRANTransID
SET@NextID=@NextTKey
GO

执行存储过程UP_NewTableID:
复制代码代码如下:
DECLARE@NextIDINT
EXECUP_NewTableID"T001",@NextIDOUTPUT
PRINT@NextID

运行的时会发现很正常，获取的结果也很正确。但是如果在高并发的情况，多个用户可能就会获取相同的ID，如果获取的ID后是用于保存对应表中的记录，那么最多只有一个用户能保存成功。
下面模拟一下并发情形，将上面的存储过程UP_NewTableID中语句WAITFORDELAY"00:00:05"的注释去掉，打开3个查询分析器的窗体，依次执行上面语句。
预期是想分别获得1,2,3，但是也许会发现多个窗体的运行结果都是：1。这就是说在更新语句执行之前，大家都获取的ID是0，所以下一个数值都是为1。（实际的数值，根据DELAY的参数大小及运行时间按间隔有关）
从这方面来分析的话有的朋友可能就会想到，是否可以在更新语句执行时判断ID是不是原始ID了？修改过程：
复制代码代码如下:
ALTERPROCEDUREUP_NewTableID
@TCodeVARCHAR(20),@NextIDINTOUTPUT
AS
DECLARE@CurTKeyINT,@NextTKeyINT
BEGINTRANTransID
SELECT@CurTKey=TKey
FROMKeyTable
WHERETCode=@TCode
IF@@ROWCOUNT=0BEGIN
ROLLBACKTRANTransID
RAISERROR("Warning:Nosuchrowisexists",16,1)
RETURN
END
SET@NextTKey=@CurTKey+1
WAITFORDELAY"00:00:05"
UPDATEKeyTable
SETTKey=@NextTKey
WHERETCode=@TCodeANDTKey=@CurTKey--此处加上TKey的校验
IF@@ROWCOUNT=0BEGIN
ROLLBACKTRANTransID
RAISERROR("Warning:Nosuchrowisupdated",16,1)
RETURN
END
COMMITTRANTransID
SET@NextID=@NextTKey
GO

如果打开个3个执行过程来模拟并发，那么会有2个窗体出现：
消息50000，级别16，状态1，过程UP_NewTableID，第28行
Warning:Nosuchrowisupdated
由此会看到还是会由于并发导致有用户操作失败，但是较上一个至少将错误出现的时间点提前了。
那么有没有更好的方法，从查询到更新结束整个事务过程中，不会有任何其他事务插入其中来搅局的办法呢，答案很明确，有，使用锁！需要选择适当的锁，否则效果将和上面的一样。
复制代码代码如下:
ALTERPROCEDUREUP_NewTableID
@TCodeVARCHAR(20),@NextIDINTOUTPUT
AS
DECLARE@CurTKeyINT,@NextTKeyINT
BEGINTRANTransID
SELECT@CurTKey=TKey
FROMKeyTableWITH(UPDLOCK)--采用更新锁，并保持到事务完成
WHERETCode=@TCode
IF@@ROWCOUNT=0BEGIN
ROLLBACKTRANTransID
RAISERROR("Warning:Nosuchrowisexists",16,1)
RETURN
END
SET@NextTKey=@CurTKey+1
WAITFORDELAY"00:00:05"
UPDATEKeyTable
SETTKey=@NextTKey
WHERETCode=@TCode--此处无需验证TKey是否与SELECT的相同
COMMITTRANTransID
SET@NextID=@NextTKey
GO

可以打开N（N>=2)个窗体来进行测试，将会看到所有操作都被串行化，结果就是我们想要的那样。如此注释或者去掉模仿并发的语句WAITFORDELAY"00:00:05"即可。
如前面所说，这同样适应于单据编号类似编码的产生形式，只要对前面的代码及键值表稍作修改即可，有兴趣的朋友可以一试。如果是从前端取得这个编号，并应用于各个记录，那么可能存在跳号的可能。如果为了保证不存在跳号，一种解决方案就是使用跳号表，将跳号记录定期扫描并应用于其他记录。另一种解决方案是将记录的保存操作放置到编号产生的过程中，形成一个串行化的事务。

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

相关文章