SQL Server中使用存储过程自动生成id

环境:

  • 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的值为:
在这里插入图片描述

  • 0
    点赞
  • 7
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

jackletter

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值