如何在MySQL中创建类似CHV180000001格式的自增列？

你的SQL Server代码在MySQL里报错，核心原因是两种数据库的语法规则、函数体系以及计算列的定义方式存在差异。下面给你两种可行的实现方案，按需选择：

方案1：使用MySQL生成列（推荐，MySQL 5.7+支持） #

生成列是MySQL 5.7及以上版本提供的特性，和SQL Server的计算列逻辑类似，但语法不同，能自动计算并存储（或实时计算）指定表达式的值。

修正后的建表语句如下：

CREATE TABLE Candidates (
    ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    ApplicationID VARCHAR(12) GENERATED ALWAYS AS (CONCAT('CHV18', LPAD(ID, 6, '0'))) STORED,
    FirstName VARCHAR(100) NOT NULL,
    MiddleName VARCHAR(100) NOT NULL,
    LastName VARCHAR(100) NOT NULL,
    DateOfBirth DATE NOT NULL,
    Gender CHAR(1) NOT NULL
);

关键修改说明： #

• 移除了SQL Server风格的方括号[]：MySQL中列名用反引号 或者直接使用即可（除非是关键字）
• 替换字符串补零逻辑：用LPAD(ID, 6, '0')替代SQL Server的right('000000'+CONVERT([varchar](6),[ID]),(6))，LPAD可以直接把数字补前导零到指定长度，写法更简洁
• 字符串拼接方式：MySQL中不能用+做字符串拼接（+是算术加法），必须用CONCAT函数
• 生成列语法：GENERATED ALWAYS AS (表达式)，这里用STORED表示将计算结果存储在磁盘上，也可以用VIRTUAL（默认选项，查询时实时计算）
• 调整字符类型：把nchar(1)改为CHAR(1)，MySQL中NCHAR是特定字符集的固定长度类型，普通场景用CHAR(1)足够满足需求

使用这个方案时，插入数据只需指定非自增、非生成列的字段，ID会自动自增，ApplicationID会同步生成对应格式的值，比如第一条数据的ApplicationID就是CHV18000001。

方案2：使用触发器（兼容MySQL 5.7以下版本） #

如果你的MySQL版本低于5.7，不支持生成列，可以用触发器实现自动生成ApplicationID：

首先创建基础表：

CREATE TABLE Candidates (
    ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    ApplicationID VARCHAR(12) NOT NULL,
    FirstName VARCHAR(100) NOT NULL,
    MiddleName VARCHAR(100) NOT NULL,
    LastName VARCHAR(100) NOT NULL,
    DateOfBirth DATE NOT NULL,
    Gender CHAR(1) NOT NULL
);

然后创建插入前的触发器：

DELIMITER //
CREATE TRIGGER before_insert_candidates
BEFORE INSERT ON Candidates
FOR EACH ROW
BEGIN
    -- 基于自增ID生成格式化的ApplicationID
    SET NEW.ApplicationID = CONCAT('CHV18', LPAD(NEW.ID, 6, '0'));
END //
DELIMITER ;

插入数据时，无需指定ID和ApplicationID，MySQL会自动生成ID，触发器会同步计算并填充ApplicationID的值。

注意事项： #

• 触发器方案中ApplicationID是普通列，若手动插入值会覆盖触发器的结果，建议给ApplicationID添加唯一约束避免重复
• 优先推荐生成列方案，因为它更简洁、维护成本更低，不需要额外维护触发器

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