SQL处理数据并发,解决ID自增

小程序1530 更新时间：2025-06-08 05:10:38

2023年6月21日发(作者：)

SQL处理数据并发，解决ID⾃增1 创建MaxIdProcess表，由于存储ID的最⼤值CREATE TABLE [dbo].[MaxIdProcess]( [Id] [bigint] IDENTITY(1,1) NOT NULL, --⾃增ID [TableNM] [nvarchar](200) NOT NULL, --存储表明 [Prefix] [nvarchar](50) NULL, --ID前缀 [Radix] [char](2) NULL, -- [MaxId] [nvarchar](50) NULL, --存储最⼤ID [CreateDatetime] [datetime] NULL, --创建时间 [LastModifyDatetime] [datetime] NULL, [LastModifyBy] [nvarchar](50) NULL, CONSTRAINT [PK_MaxIdProcess] PRIMARY KEY CLUSTERED

( [Id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]2、创建存储过程 Pro_GetTableNextMaxIdByTableName 获取最⼤ID 1 CREATE procedure [dbo].[Pro_GetTableNextMaxIdByTableName]

2 @TableName char(50), --table名称 3 @Prefix char(3), --ID前缀 4 @NextId char(16) out --ID输出 5 AS 6

7 begin

8 --if not exists (select * from MaxIdProcess where TableName=@TableName) 9 --begin10 -- insert into MaxIdProcess values(@TableName,null)11 --end12 -- update MaxIdProcess

13 -- set @NextId= isnull(MaxId, @Prefix + '1'),14 -- MaxId = _GetMaxId(MaxId,@Prefix)15 -- where TableName=@TableName16

17

18 --检查系统表中是否存在该表，如果不存在则调⽤Pro_GetRandomStr存储过程获取⼀个随机ID19 IF EXISTS (SELECT object_id FROM s(nolock) WHERE type='U' AND name=@TableName)20 BEGIN21 EXEC Pro_GetRandomStr @NextId output22 RETURN

23 END

24

25 --检查MaxIdProcess表中是否存有需要获取ID的表名，如果不存在则插⼊数据26 if not exists (select * from MaxIdProcess where TableNM=rtrim(@TableName))27 begin28 insert into MaxIdProcess values(@TableName,@Prefix,'10',0,getdate(),getdate(),'Auto')29 end30

31 declare @temp bigint32 update MaxIdProcess33 set @temp=cast(rtrim(MaxId) as bigint),MaxId=MaxId+134 where rtrim(TableNM)=rtrim(@TableName)35 set @NextId=@Prefix+right(cast(1000+@temp as nvarchar(16)),13)36 end3、创建执⾏存储过程，如插⼊新增⽤户CREATE PROCEDURE [dbo].[Pro_User_Insert] @UserId CHAR(16) OUT , @UserNM NVARCHAR(50) , @Description NVARCHAR(255)

ASBEGIN TRY BEGIN DECLARE @Name NVARCHAR(50); SELECT @UserId = , @Name = ption FROM b WHERE = @UserNM; IF NOT ISNULL(@UserId, '') = '' BEGIN SELECT @UserNM + @Name + '已经存在'; --SELECT '⽤户已经存在'; RETURN; END; DECLARE @MaxId CHAR(16);

EXEC leNextMaxIdByTableName 'User', 'Usr', @MaxId OUT;

SET @UserId = @MaxId;

INSERT INTO UsersTb ( [UserId] , [UserNM] , [Description]

) VALUES ( @UserId , @UserNM, @Description ); SELECT '执⾏成功'; END END TRY BEGIN CATCH SELECT ERROR_MESSAGE();

END CATCH4、执⾏新增⽤户存储过程DECLARE @UserId int;EXEC Pro_User_Insert @UserId output,'zhangsan','张三' ;5、完成

关于 Pro_GetRandomStr 存储过程CREATE Procedure [dbo].[Pro_GetRandomStr] @RandomStr varchar(16) output as BEGIN set nocount on

declare @s varchar(61)

declare @r varchar(16)

declare @pos int declare @len int set @s = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ012345678'

set @len = len(@s); set @r = ''

while len(@r) < 16

begin

set @pos = cast(rand()*61 as int); --while @pos > @len or @pos <1 --begin -- if(@pos < 1) -- set @pos = cast(rand()*61 as int); -- else -- set @pos = cast(@pos /2 as int); --end set @r = @r + substring(@s, @pos, 1) --select @r end set @RandomStr = upper(@r)END c# 实现存储过程string strcon = "Data Source=(local)SQLEXPRESS; Initial Catalog=TestDatabase; "; strcon += "Integrated Security=True;"; using (SqlConnection con = new SqlConnection(strcon)) { (); SqlParameter[] parameters =

{ new SqlParameter("@UserId", ,16), new SqlParameter("@UserNM", ar), new SqlParameter("@Description", ar), }; //parameters[0].Value = null; parameters[0].Direction = ; parameters[1].Value = "zhangsan"; parameters[2].Value = "张三"; SqlCommand cmd = new SqlCommand(); dType = Procedure; dText = "Pro_User_Insert"; tion = con; ge(parameters); object obj = eScalar(); //string msg = eScalar().ToString(); ();

}