SQL Server 中生成雪花 ID(Snowflake ID) 的方法

生成雪花 ID

雪花 ID 是 Twitter 提出的一种分布式 ID 生成算法，它生成 64 位的唯一 ID，通常包含时间戳、工作节点 ID 和序列号。 在 SQL Server 中可以通过以下几种方式实现雪花 ID 的生成：

使用 T-SQL 函数实现

-- 创建配置表
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 ToNextMillis(@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.ToNextMillis(@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;