在SQL Server中如何生成由年份、特殊字符和序列号组成的自增主键ID？

嘿，针对你要生成YYYY-XXXXX格式自增主键的需求，我给你整理了几个SQL Server上的可行方案，都是实际项目里验证过的，你可以根据自己的场景选择：

方案1：IDENTITY列 + 计算列（推荐简单场景） #

这个方案最直观，把年份和序列号分开存储，通过计算列自动拼合出你要的格式，维护起来也简单。

修改后的建表语句 #

CREATE TABLE [dbo].[STUDENT] (
    -- 默认取当前年份，也可以手动指定其他年份
    [Year_Code] [int] NOT NULL DEFAULT YEAR(GETDATE()),
    -- 从0开始自增的序列号，保证连续性
    [Seq_Num] [int] IDENTITY(0,1) NOT NULL,
    -- 自动生成指定格式的Stud_ID，PERSISTED让它可以作为主键
    [Stud_ID] AS CAST(Year_Code AS VARCHAR(4)) + '-' + RIGHT('00000' + CAST(Seq_Num AS VARCHAR(5)), 5) PERSISTED,
    [Stud_LName] [varchar](100) NOT NULL,
    [Stud_FName] [varchar](100) NOT NULL,
    [Stud_MName] [varchar](100) NOT NULL,
    -- 把生成的Stud_ID设为主键
    CONSTRAINT PK_STUDENT PRIMARY KEY (Stud_ID)
)

插入数据示例 #

插入时不用管Stud_ID、Year_Code和Seq_Num（用默认年份的话）：

INSERT INTO [dbo].[STUDENT] (Stud_LName, Stud_FName, Stud_MName)
VALUES ('Doe', 'Jane', 'Stack'), ('Doe', 'John', 'Stack')

查询后就能得到你要的效果：

Stud_ID     Stud_LName  Stud_FName  Stud_MName
----------  ----------  ----------  ----------
2024-00000  Doe         Jane        Stack
2024-00001  Doe         John        Stack

如果要指定特定年份（比如你例子里的2018），插入时手动给Year_Code赋值就行：

INSERT INTO [dbo].[STUDENT] (Year_Code, Stud_LName, Stud_FName, Stud_MName)
VALUES (2018, 'Doe', 'Jane', 'Stack'), (2018, 'Doe', 'John', 'Stack')

方案2：SEQUENCE + 默认值（适合复杂序列号规则） #

如果需要更灵活的控制（比如每年重置序列号），可以用SQL Server的SEQUENCE对象，比IDENTITY更灵活。

先创建序列 #

CREATE SEQUENCE [dbo].[Stud_Seq]
    START WITH 0          -- 起始序列号
    INCREMENT BY 1        -- 每次加1
    MINVALUE 0            -- 最小值
    MAXVALUE 99999        -- 最大值（对应5位数字）
    CYCLE                 -- 可选：到99999后从0重新开始

再创建表 #

CREATE TABLE [dbo].[STUDENT] (
    -- 自动获取序列值并拼合年份生成Stud_ID
    [Stud_ID] AS CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '-' + RIGHT('00000' + CAST(NEXT VALUE FOR [dbo].[Stud_Seq] AS VARCHAR(5)), 5) PERSISTED NOT NULL,
    [Stud_LName] [varchar](100) NOT NULL,
    [Stud_FName] [varchar](100) NOT NULL,
    [Stud_MName] [varchar](100) NOT NULL,
    CONSTRAINT PK_STUDENT PRIMARY KEY (Stud_ID)
)

注意事项 #

如果需要每年重置序列号，每年年初手动执行这条语句就行，也可以配个SQL作业自动跑：

ALTER SEQUENCE [dbo].[Stud_Seq] RESTART WITH 0;

方案3：触发器（兼容旧版SQL Server） #

如果你的SQL Server版本比较旧（比如2005及更早），不支持计算列或SEQUENCE，那就用触发器来实现。

先建表和序列号追踪表 #

CREATE TABLE [dbo].[STUDENT] (
    [Stud_ID] [varchar](10) NOT NULL,
    [Stud_LName] [varchar](100) NOT NULL,
    [Stud_FName] [varchar](100) NOT NULL,
    [Stud_MName] [varchar](100) NOT NULL,
    CONSTRAINT PK_STUDENT PRIMARY KEY (Stud_ID)
)

-- 建个表存每年的当前序列号，避免并发冲突
CREATE TABLE [dbo].[Stud_Seq_Tracker] (
    [Year_Code] [int] PRIMARY KEY,
    [Current_Seq] [int] DEFAULT 0
)

创建触发器 #

CREATE TRIGGER [dbo].[TRG_STUDENT_Generate_StudID]
ON [dbo].[STUDENT]
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @Year int = YEAR(GETDATE());
    DECLARE @NextSeq int;

    -- 原子操作获取下一个序列号，防止并发重复
    UPDATE [dbo].[Stud_Seq_Tracker]
    SET @NextSeq = Current_Seq + 1, Current_Seq = Current_Seq + 1
    WHERE Year_Code = @Year;

    -- 如果当前年份还没记录，初始化序列号为0
    IF @NextSeq IS NULL
    BEGIN
        SET @NextSeq = 0;
        INSERT INTO [dbo].[Stud_Seq_Tracker] (Year_Code, Current_Seq)
        VALUES (@Year, @NextSeq);
    END

    -- 插入数据并生成格式正确的Stud_ID
    INSERT INTO [dbo].[STUDENT] (Stud_ID, Stud_LName, Stud_FName, Stud_MName)
    SELECT 
        CAST(@Year AS VARCHAR(4)) + '-' + RIGHT('00000' + CAST(@NextSeq + ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 AS VARCHAR(5)), 5),
        Stud_LName, Stud_FName, Stud_MName
    FROM inserted;
END

插入数据 #

直接插就行，触发器会自动帮你生成Stud_ID：

INSERT INTO [dbo].[STUDENT] (Stud_LName, Stud_FName, Stud_MName)
VALUES ('Doe', 'Jane', 'Stack'), ('Doe', 'John', 'Stack')

几个关键提醒 #

• 并发问题：方案1和2都是SQL Server原生的原子操作，不用担心并发重复；方案3通过UPDATE获取序列号，也能避免冲突。
• 主键唯一性：计算列设置PERSISTED后可以直接设为主键，保证唯一性；触发器方案通过追踪表也能确保序列号不重复。
• 年份重置：如果需要每年从头开始序列号，方案2重启序列最方便；方案3会自动识别新的年份并初始化序列号。

内容的提问来源于stack exchange，提问作者NewBEE