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 javascriptKEY (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 @Sequencepython = (@Sequence + 1) & @SequenceMask;
            IF @Sequence = 0
            BEGIN
                -- 序列耗尽，等待下一毫秒
                SET @Timestamp = dbo.TilNextMillis(@LastTimestamp);
            END
 js       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;