SQLServer中生成雪花ID(Snowflake ID)的实现方法

-- 创建配置表
create table snowflakeconfig (
    machineid bigint not null,
    datacenterid bigint not null,
    lasttimestamp bigint not null,
    sequence bigint not null,
    constraint pk_snowflakeconfig primary key (machineid, datacenterid)
);

-- 初始化配置 (机器id和数据中心id需要在每个节点上配置不同)
insert into snowflakeconfig (machineid, datacenterid, lasttimestamp, sequence)
values (1, 1, 0, 0);

-- 创建获取当前时间戳的函数
create function getcurrenttimestamp()
returns bigint
as
begin
    declare @epoch datetime2 = '1970-01-01 00:00:00';
    declare @now datetime2 = sysutcdatetime();
    return cast(datediff_big(millisecond, @epoch, @now) as bigint);
end;

-- 创建等待下一毫秒的函数
create function tilnextmillis(@lasttimestamp bigint)
returns bigint
as
begin
    declare @timestamp bigint;
    set @timestamp = dbo.getcurrenttimestamp();
    
    while @timestamp <= @lasttimestamp
    begin
        set @timestamp = dbo.getcurrenttimestamp();
    end
    
    return @timestamp;
end;
go

-- 创建计算幂的函数（替代位移操作）
create function poweroftwo(@exponent bigint)
returns bigint
as
begin
    return cast(power(cast(2 as float), @exponent) as bigint);
end;
go

-- 创建生成雪花id的存储过程
create procedure generatesnowflakeid
    @machineid bigint = 1,
    @datacenterid bigint = 1,
    @snowflakeid bigint output
as
begin
    set nocount on;
    
    -- 常量定义
    declare @twepoch bigint = 1700058600000;
    declare @machineidbits bigint = 5;
    declare @datacenteridbits bigint = 5;
    declare @sequencebits bigint = 12;
    
    -- 使用power计算替代位移
    declare @maxmachineid bigint = dbo.poweroftwo(@machineidbits) - 1;
    declare @maxdatacenterid bigint = dbo.poweroftwo(@datacenteridbits) - 1;
    declare @sequencemask bigint = dbo.poweroftwo(@sequencebits) - 1;
    
    declare @machineidshift bigint = @sequencebits;
    declare @datacenteridshift bigint = @sequencebits + @machineidbits;
    declare @timestampleftshift bigint = @sequencebits + @machineidbits + @datacenteridbits;
    
    -- 验证参数
    if @machineid > @maxmachineid or @machineid < 0
    begin
        throw 50000, 'machineid can''t be greater than maxmachineid or less than 0', 1;
        return;
    end
    
    if @datacenterid > @maxdatacenterid or @datacenterid < 0
    begin
        throw 50000, 'datacenterid can''t be greater than maxdatacenterid or less than 0', 1;
        return;
    end
    
    -- 使用事务确保原子性
    begin transaction;
    
    begin try
        declare @lasttimestamp bigint;
        declare @sequence bigint;
        declare @timestamp bigint;
        
        -- 获取当前状态
        select @lasttimestamp = lasttimestamp, @sequence = sequence
        from snowflakeconfig with (updlock)
        where machineid = @machineid and datacenterid = @datacenterid;
        
        -- 获取当前时间戳
        set @timestamp = dbo.getcurrenttimestamp();
        
        -- 检查时钟回拨
        if @timestamp < @lasttimestamp
        begin
            rollback transaction;
            throw 50000, 'clock moved backwards. refusing to generate id', 1;
            return;
        end
        
        -- 同一毫秒内生成多个id
        if @lasttimestamp = @timestamp
        begin
            set @sequence = (@sequence + 1) & @sequencemask;
            if @sequence = 0
            begin
                -- 序列耗尽，等待下一毫秒
                set @timestamp = dbo.tilnextmillis(@lasttimestamp);
            end
        end
        else
        begin
            set @sequence = 0;
        end
        
        -- 更新状态
        update snowflakeconfig
        set lasttimestamp = @timestamp,
            sequence = @sequence
        where machineid = @machineid and datacenterid = @datacenterid;
        
        -- 生成id (使用乘法替代位移)
        set @snowflakeid = 
            (@timestamp - @twepoch) * dbo.poweroftwo(@timestampleftshift) +
            @datacenterid * dbo.poweroftwo(@datacenteridshift) +
            @machineid * dbo.poweroftwo(@machineidshift) +
            @sequence;
        
        commit transaction;
    end try
    begin catch
        rollback transaction;
        throw;
    end catch
end;
go

-- 使用存储过程版本
declare @id bigint;
exec generatesnowflakeid @machineid = 1, @datacenterid = 1, @snowflakeid = @id output;
select @id as snowflakeid;