日期：2012-07-30 15:00:00 来源：本站整理
sqlserver数据库主键的生成方法小结(sqlserver,mysql)[MSSQL防范]
本文“sqlserver数据库主键的生成方法小结(sqlserver,mysql)[MSSQL防范]”是由七道奇为您精心收集，来源于网络转载，文章版权归文章作者所有，本站不对其观点以及内容做任何评价，请读者自行判断，以下是其具体内容：
主键的生成方法主要有三种:
一. 数据库自动生成
二. 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数据库主键的生成方法小结(sqlserver,mysql)[MSSQL防范]”的内容，如果你对以上该文章内容感兴趣，你可以看看七道奇为您推荐以下文章：
| 本文地址： | 与您的QQ/BBS好友分享! |
- 好的评价 如果您觉得此文章好，就请您
0%(0) 
- 差的评价 如果您觉得此文章差，就请您
0%(0) 
中查找“sqlserver数据库主键的生成方法小结(sqlserver,mysql)”更多相关内容
中查找“sqlserver数据库主键的生成方法小结(sqlserver,mysql)”更多相关内容评论内容只代表网友观点，与本站立场无关！
评论摘要(共 0 条，得分 0 分，平均 0 分)
查看完整评论