环境:
- window 10
- sqlserver 2014
概述:
数据库的id有很多种方法:
- 自增ID
- 序列(sqlserver 2008 r2中没有)
- 分布式id(雪花算法)
- 基于程序内缓存
- 基于redis缓存
- 基于数据库存储过程
这里展示基于数据库存储过程生成id,它有如下好处:
- 将id和表彻底分离,即使表或字段不存在也不影响生成。它就相当于,你只要提供两个关键字,就可以拿到id了
- 可以在插入表之前知道id的具体值(除了自增id都有这个优点)
- 相比于程序内缓存,不用考虑由于多个程序访问数据库造成缓存不一致的问题
它有如下缺点:
- 每次生成id都要访问数据库,开销增大
- 由于唯一id生成依赖数据库,所以在分布式环境中注意id的唯一值是否满足条件
代码:
-- 先创建日志记录表
create table __generator_err_log(
id int identity(1,1),--错误序号
err_proc varchar(200),--出现错误的存储过程或 触发器的名称
lock_str varchar(200),--申请的锁资源锁
err_num int,--错误号
err_severity int,--严重性
err_state int,--错误状态号
err_line int,--导致错误的例程中的行号
err_msg varchar(200),--错误消息的完整文本
err_time datetime --错误发生时间
)
-- 创建id记录表
create table __generator_id(
tablename varchar(50), -- 引用表名
colname varchar(50), -- 引用列名
currentid bigint,
primary key(tablename,colname)
)
-- Version=2021-09-19
/*
正常情况:
1. 无缓存,实际表里也没匹配到
2. 无缓存,实际表里匹配到
3. 有缓存
异常情况:
1. 实际表不存在等,造成查询报错
2. 获取锁超时
*/
/* 调用示例
exec __proc_generateid
@tablename='test',
@colname='id',
@count=1
*/
create proc __proc_generateid
@tablename varchar(200),
@colname varchar(200),
@count int=1
as
begin tran
declare @current bigint,@sql nvarchar(1000),@lockstr nvarchar(200), @result int;
declare @err_message varchar(500),@err_serverity varchar(50),@err_state varchar(50);
begin
-- 申请锁,20秒
set @lockstr='dbutil:newid:'+@tablename+'_'+@colname;
EXEC @result =sp_getAppLock @resource=@lockstr,@lockMode='Exclusive',@lockOwner='Transaction',@lockTimeout='20000';
if(@result >= 0)
begin
begin try
--先从id表里查找
set @current = (select top 1 currentid from __generator_id where tablename = @tablename and colname = @colname)
if @current is null
--表里尚未缓存,从实际表里查找id列的最大值
begin
set @sql='select @current=max('+@colname+') from '+@tablename
exec sp_executesql @sql,N'@current bigint output',@current output
if(@current is null)
--实际表里也没有值
begin
set @current=@count
insert into __generator_id(tablename, colname, currentid) values(@tablename, @colname, @current)
select @current
end
else
--实际表里有值
begin
set @current+=@count
insert into __generator_id(tablename, colname, currentid) values(@tablename, @colname, @current)
select @current
end
end
else
--表里已经缓存
begin
set @current += @count
update __generator_id set currentid = @current where tablename = @tablename and colname = @colname
select @current
end
exec @result= sp_releaseapplock @resource=@lockstr,@lockOwner='Transaction';
commit
return
end try
begin catch
exec @result= sp_releaseapplock @resource=@lockstr,@lockOwner='Transaction';
select @err_message=ERROR_MESSAGE(),@err_serverity=ERROR_SEVERITY(),@err_state=ERROR_STATE();
RAISERROR (@err_message, @err_serverity,@err_state );
rollback;
INSERT INTO __generator_err_log(err_proc,lock_str,err_num,err_severity,err_state,err_line,err_msg,err_time)
values('__proc_generateid',@lockstr,ERROR_NUMBER(),ERROR_SEVERITY(),ERROR_STATE(), ERROR_LINE() ,ERROR_MESSAGE(),GetDate());
return;
end catch
end
else
begin
rollback;
INSERT INTO __generator_err_log(err_proc,lock_str,err_msg,err_time)
values('__proc_generateid',@lockstr,'20秒内未获取到锁,申请锁返回:'+Convert(varchar,@result),GetDate());
select @err_message='20秒内未获取到锁:'+@lockstr,@err_serverity=10,@err_state=1;
RAISERROR (@err_message, 16,1 );
return;
end
end
测试调用:
exec __proc_generateid "test","id"
exec __proc_generateid "test","id"
效果如下:
此时,表__generator_id的值为: