MySQL表创建语法错误修正：生成带前缀的自增序列字段

原SQL存在以下核心问题：

• VARCHAR类型未指定长度，MySQL要求必须显式定义长度
• AUTO_INCREMENT不能直接作为默认值的组成部分，且单表仅支持一个整数类型的自增字段（需设为主键/唯一键）

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

通过自增主键+存储型生成列，自动格式化生成目标ID：

CREATE TABLE Meta_data (
    id INT AUTO_INCREMENT PRIMARY KEY,
    Name_list_id VARCHAR(10) GENERATED ALWAYS AS (CONCAT('NL', LPAD(id, 2, '0'))) STORED,
    Result_id VARCHAR(10) GENERATED ALWAYS AS (CONCAT('RS', LPAD(id, 2, '0'))) STORED,
    No_of_subject INT
);

说明：

• id作为自增主键，保证序号连续唯一
• LPAD(id, 2, '0')将数字补为两位（如1→01、10→10），若需要三位格式可将2改为3
• STORED表示该列会实际存储在表中，查询时直接读取；若用默认的VIRTUAL则为查询时计算，不占用存储

测试示例：

插入数据时无需指定ID列，会自动生成：

INSERT INTO Meta_data (No_of_subject) VALUES (5), (10);

查询结果：

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

CREATE TABLE Meta_data (
    id INT AUTO_INCREMENT PRIMARY KEY,
    Name_list_id VARCHAR(10),
    Result_id VARCHAR(10),
    No_of_subject INT
);

-- 创建插入触发器
DELIMITER //
CREATE TRIGGER trg_meta_data_insert BEFORE INSERT ON Meta_data
FOR EACH ROW
BEGIN
    SET NEW.Name_list_id = CONCAT('NL', LPAD((SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'Meta_data'), 2, '0'));
    SET NEW.Result_id = CONCAT('RS', LPAD((SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'Meta_data'), 2, '0'));
END //
DELIMITER ;

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