SQL中如何在插入数据时自动生成递增的CN前缀控制编号

你设想的CONCAT('CN2022', LPAD(id, 2, '0'))拼接逻辑完全可行，只需要把这个规则绑定到表结构上，插入时无需手动传值，数据库会自动生成符合要求的编号，以下是两种可直接落地的实现方案：

方案1：生成列（计算列）实现（优先推荐） #

这是最稳定、性能最好的实现方式，直接依托数据库原生的自增主键作为序号源，通过数据库内置的生成列特性自动计算编号值，无需额外写逻辑，也不会出现并发场景下序号重复的问题。
以MySQL为例，建表时直接按如下规则定义：

CREATE TABLE biz_record (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  -- 以下是你的业务字段，可根据实际需求调整
  col1 VARCHAR(10),
  age INT,
  col2 VARCHAR(10),
  email VARCHAR(50),
  -- 定义自动生成的控制编号列
  Control_Number VARCHAR(10) GENERATED ALWAYS AS (
    CONCAT('CN2022', LPAD(id, 2, '0'))
  ) STORED
);

插入数据时完全不需要处理id和Control_Number字段，只传入业务字段即可：

INSERT INTO biz_record (col1, age, col2, email)
VALUES ('x',12,'x','x@gmail.com'),
       ('x',12,'x','x@gmail.com'),
       ('x',12,'x','x@gmail.com');

执行查询后就能得到你期望的输出结果：

CN202201,x,12,x,x@gmail.com
CN202202,x,12,x,x@gmail.com
CN202203,x,12,x,x@gmail.com

注意：LPAD的长度参数需要根据你预估的最大序号调整，如果序号会超过99（即达到3位数），就把第二个参数从2改成3，避免序号截断。如果后续需要调整固定前缀，直接修改生成列的拼接表达式即可。

方案2：触发器实现（兼容不支持生成列的数据库版本） #

如果你使用的数据库版本不支持生成列特性，可以通过前置插入触发器实现自动编号赋值，同样不需要在插入语句中手动传编号值。
第一步先建基础表：

CREATE TABLE biz_record (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  col1 VARCHAR(10),
  age INT,
  col2 VARCHAR(10),
  email VARCHAR(50),
  Control_Number VARCHAR(10)
);

第二步创建插入前触发的触发器：

DELIMITER //
CREATE TRIGGER trg_gen_control_num
BEFORE INSERT ON biz_record
FOR EACH ROW
BEGIN
  SET NEW.Control_Number = CONCAT(
    'CN2022',
    LPAD(
      (SELECT AUTO_INCREMENT FROM information_schema.TABLES 
       WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'biz_record'),
      2, '0'
    )
  );
END //
DELIMITER ;

触发器创建完成后，插入数据的写法和方案1完全一致，数据库会在插入时自动为Control_Number赋值。

注意事项 #

• 不要在应用层代码中通过查询表内最大ID+1的方式生成编号，高并发插入场景下极易出现序号重复、主键冲突的问题，编号生成逻辑尽量交给数据库底层实现。
• 如果需要序号按年度重置（比如2023年的编号从CN202301开始重新计数），可以在生成列/触发器的逻辑中增加年份判断，或在年度切换时调整规则即可。

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