优选主流主机商
任何主机均需规范使用

高效生成唯一长整型ID:SQL函数应用实例详解

前言

在设计表的时候考虑主键的数据类型是长整形还是字符串,最简单的方式当然是newid(),但这也有个问题,就是主键长度过长(36个字),数据量一多,必然会影响数据库操作的效率,而且大大增加了数据文件和索引文件所占用的空间。而且,newid返回的字符串是随机的,查询结果不能保证按保存顺序返回。这对于有顺序要求的系统来说,需要额外增加顺序列来进行排序,这也导致查询语句更加复杂。这也是主要放弃newid作为主键的主要原因。因此考虑用长整形来作数据表主键的数据类型。

实现方法

一开始在C#等面向对像语言中编写一个获取PK的方法,那是很顺序就完成了。

接着是SQL中,如果要用脚本导入数据,那就要提供一个SQL的方法来获取PK。

最初设计PK的组成:时间(yyMMddHHmmssmsS) + ‘4位随机数’   ,于是卡卡很快完成dbo.pk()

1 2 3 4 5 6 7 8 9 10 11 12 Create function dbo.pk() returns bigint as begin   declare @pk as bigint ,@fix bigint ,@idx int ,@ts as datetime   set @ts = GETDATE()   set @pk = convert ( bigint , convert ( varchar (6),@ts,12) + replace ( convert ( varchar (12),@ts,114), ':' , '' ))*10000   select @idx = A*10000   from vRand   return (@pk + @idx) end go

然后来获取一个10000PK测试:

1 2 3 4 5 6 7 8 9 10 11 12 13 declare @tab as table (pk bigint ) declare @i as integer set @i =0 while(@i<10000) begin insert @tab select dbo.pk() set @i = @i+1 end select pk, count (1) cnt from @tab   group by pk   having COUNT (1)>1

oh my god!竟然有30多个重复的。

可见这个方法,做为获取单个PK,那问题不大,但在做批量保存的时候,可能会发生主键冲突。

因此再设计一个支持批量保存的。

既然4位随机数不能保证毫秒级的唯一,那就只能用有序数了,把PK的组成改为:时间(yyMMddHHmmssmsS) + ‘4位有序数’

再考虑到年份只是2位数,跟面向对像中的PK组成有机会在202x年之后存在冲突,因此增加一个标识 ‘1’+yy作为年以延长千年虫问题,虽然还是有机会发生冲突,但那也是几百年以后的事情了。

但是为了保持效率和冲突的概率,还是将PK改为:’1’+时间(yyMMddHHmmssms) + ‘4位有序数’.

接下来又是一顿卡卡卡,dbo.pks(@count)已出:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 CREATE function dbo.pks(@ count as int ) returns @pks table (pk bigint ,id int ) as begin    declare @pk as bigint ,@fix bigint ,@idx int ,@ts as datetime,@lop int ,@i int    set @ts = GETDATE()    set @pk = convert ( bigint , '1' + convert ( varchar (6),@ts,12) + replace ( convert ( varchar (11),@ts,114), ':' , '' ))*10000    set @idx =0    set @lop = CEILING(@ count /10000.0)    set @i = 1    while(@lop >0)    begin      set @pk = @pk + 10000      set @idx = 0      while(@idx<10000 and @idx<@ count )      begin        insert @pks(pk,id)        values (@pk+@idx,@idx+ @i)        set @idx = @idx +1      end      set @lop = @lop -1      set @i = @i+10000    end    return end go

批量测试一下

1 select * from dbo.pks(500000)

正常返回500000行,没有一行重复!

在返回的结果列中,ID是从1开始编号的,这也保持与SQL的Row_number保持一致,方便SQL编程引用。

OK,到这里用利用SQL function获取PK就搞定了!

未经允许不得转载:搬瓦工中文网 » 高效生成唯一长整型ID:SQL函数应用实例详解