如何在SQL Server中基于前缀生成递增的友好标识符（FriendlyID）

当然可以搞定！在SQL Server里完全能实现按前缀分组生成这种带补零的友好ID，而且还能保证绝对不冲突，适配你的EF Core 5环境。我给你分享两种靠谱的方案，重点推荐第一种，灵活性拉满：

这个方案通过一个辅助表记录每个前缀的当前最大序号，再用触发器在插入数据时自动生成唯一的FriendlyID，完美适配动态新增的前缀场景。

步骤1：创建辅助追踪表 #

先建一个专门的表，用来存储每个前缀对应的最新序号：

CREATE TABLE PrefixSequenceTracker (
    Prefix NVARCHAR(50) PRIMARY KEY, -- 对应主表的Prefix列
    CurrentMaxNumber INT NOT NULL DEFAULT 0 -- 记录该前缀的当前最大序号
);

步骤2：创建插入触发器 #

在你的主表上创建INSTEAD OF INSERT触发器，它会拦截插入请求，先更新辅助表获取下一个序号，再生成FriendlyID插入主表：

CREATE TRIGGER trg_GenerateFriendlyID
ON YourMainTableName -- 替换成你的实际主表名
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;

    -- 先处理已有前缀的序号递增
    WITH InsertedPrefixes AS (
        SELECT DISTINCT Prefix FROM inserted
    ),
    UpdatedTrackers AS (
        UPDATE t
        SET CurrentMaxNumber = t.CurrentMaxNumber + 1
        OUTPUT inserted.Prefix, inserted.CurrentMaxNumber AS NewNumber
        FROM PrefixSequenceTracker t
        INNER JOIN InsertedPrefixes ip ON t.Prefix = ip.Prefix
    ),
    -- 处理新增前缀的初始化（直接从1开始）
    NewTrackers AS (
        INSERT INTO PrefixSequenceTracker (Prefix, CurrentMaxNumber)
        SELECT Prefix, 1
        FROM InsertedPrefixes ip
        WHERE NOT EXISTS (SELECT 1 FROM PrefixSequenceTracker t WHERE t.Prefix = ip.Prefix)
        OUTPUT inserted.Prefix, inserted.CurrentMaxNumber AS NewNumber
    )
    -- 合并结果，插入主表并生成FriendlyID
    INSERT INTO YourMainTableName (ID, Prefix, FriendlyID)
    SELECT 
        i.ID,
        i.Prefix,
        CONCAT(i.Prefix, FORMAT(COALESCE(ut.NewNumber, nt.NewNumber), '0000')) -- 0000是4位补零，可按需调整位数
    FROM inserted i
    LEFT JOIN UpdatedTrackers ut ON i.Prefix = ut.Prefix
    LEFT JOIN NewTrackers nt ON i.Prefix = nt.Prefix;
END;

方案优势 #

• 完全支持动态新增的前缀，不用手动维护额外对象
• 原子性操作：SQL Server会自动处理并发插入，同一前缀绝不会生成重复序号
• 逻辑清晰，后期维护简单

如果你的业务前缀是固定的几个（比如示例里的AB、BC、DE），可以给每个前缀单独创建序列，插入时直接取序列的下一个值拼接：

步骤1：为每个前缀创建序列 #

-- 为AB前缀创建序列，从1开始递增
CREATE SEQUENCE Seq_AB START WITH 1 INCREMENT BY 1;
-- 为BC前缀创建序列
CREATE SEQUENCE Seq_BC START WITH 1 INCREMENT BY 1;
-- 其他前缀同理

步骤2：插入时生成FriendlyID #

插入数据时可以直接用NEXT VALUE FOR获取序号，比如：

INSERT INTO YourMainTableName (ID, Prefix, FriendlyID)
VALUES (NEWID(), 'AB', CONCAT('AB', FORMAT(NEXT VALUE FOR Seq_AB, '0000')));

方案局限 #

• 前缀新增时需要手动创建对应的序列，灵活性不足
• 不适合前缀动态变化的业务场景

不管用哪种方案，都需要在EF Core里配置FriendlyID为数据库生成，避免手动赋值冲突：

实体类配置 #

public class YourEntity
{
    public Guid ID { get; set; }
    public string Prefix { get; set; }
    // 标记为数据库生成字段
    [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
    public string FriendlyID { get; set; }
}

DbContext配置 #

在OnModelCreating里确认字段配置：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<YourEntity>()
        .Property(e => e.FriendlyID)
        .HasComputedColumnSql(null); // 因为是触发器/序列生成，不是SQL计算列，所以设为null即可
}

• 已有数据初始化：如果主表已经有历史数据，需要先把辅助表填充好，避免序号重复：

INSERT INTO PrefixSequenceTracker (Prefix, CurrentMaxNumber)
SELECT Prefix, MAX(CAST(RIGHT(FriendlyID, 4) AS INT))
FROM YourMainTableName
GROUP BY Prefix;

• 补零位数调整：把FORMAT函数里的'0000'改成'000000'就能生成6位补零的序号，按需调整即可
• 并发测试：如果业务有高并发插入需求，可以测试下触发器的表现，SQL Server的行级锁会保证同一前缀的序号唯一性

内容的提问来源于stack exchange，提问作者Isaac Dontje Lindell