sqlserver生成雪花ID

最新推荐文章于 2025-03-28 09:53:46 发布

原创最新推荐文章于 2025-03-28 09:53:46 发布 · 4.5k 阅读

0 ·

CC 4.0 BY-SA版权

MSSQL数据库专栏收录该内容

158 篇文章

订阅专栏

本文介绍了两个SQL Server中的自定义函数：fn_NewID及Fn_NewIdtoBint。这两个函数用于将UUID（唯一标识符）转换为bigint类型的数据。fn_NewID函数通过组合系统当前时间和一个随机数来生成bigint类型的唯一值；而Fn_NewIdtoBint函数则直接将UUID转换为bigint。文章详细展示了这两个函数的实现代码。

SELECT dbo.fn_NewID(NEWID()) -- 20201026155869949
SELECT dbo.Fn_NewIdtoBint(NEWID()) -- 7499730272645094476

CREATE function [dbo].[Fn_NewIdtoBint]
(
@Newid nvarchar(50)
)
returns bigint
as
begin
   declare @zaza bigint
   declare @uu uniqueidentifier
   set @uu = @Newid
   set @zaza = ( select convert(bigint, convert (varbinary(8), @uu, 1)) )

return @zaza
end

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO

CREATE function [dbo].[fn_NewID]
(
@Newid nvarchar(50)
)
returns bigint
as
begin
   declare @resultBigint bigint
   DECLARE @resultStr VARCHAR(19),@dateStr VARCHAR(50),@randStr1 VARCHAR(9);
   SELECT   @randStr1 = RIGHT(100000000000 + CONVERT(bigint, ABS(CHECKSUM(@Newid))), 10),
           --@dateStr = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(20),SYSDATETIME(),120),'-',''),':',''),' ','')
           @dateStr =left(replace(replace(replace(replace(CONVERT(varchar, SYSDATETIME() ),'-',''),' ',''),':',''),'.',''),8)
   set @resultStr=@dateStr+@randStr1
   SET @resultBigint = CONVERT(BIGINT, @resultStr);

return @resultBigint
end