MySQL 觸發器實現定長字符串主鍵 (零填充自增 ID) 完整方案

業務場景

需求：主鍵為定長字符串 (如 CHAR(5))，格式 00001, 00002... 便於排序、顯示、業務編碼拼接。標準 AUTO_INCREMENT 為整數，需轉換。

表結構設計

DROP TABLE IF EXISTS `tb_cate`;

CREATE TABLE `tb_cate` (
    `id` CHAR(5) PRIMARY KEY DEFAULT '' COMMENT '目錄編號，5位數字，高位補0，從00001開始',
    `type` CHAR(1) DEFAULT '0' COMMENT '目錄類型',
    `name` VARCHAR(40) NOT NULL COMMENT '目錄名稱',
    `describe` VARCHAR(200) COMMENT '目錄描述',
    `code` VARCHAR(30) NOT NULL COMMENT '完整編碼路徑，如 0000100002',
    `parent` CHAR(5) COMMENT '上級目錄',
    `lev` CHAR(1) DEFAULT '0' COMMENT '層級',
    `create_user` INT(10) DEFAULT 0 COMMENT '創建人',
    `update_user` INT(10) DEFAULT 0 COMMENT '修改人',
    `createtime` DATETIME COMMENT '創建時間',
    `updatetiem` DATETIME COMMENT '修改時間',
    -- 輔助自增列，僅供觸發器使用，業務層忽略
    `increc_id` INT UNIQUE AUTO_INCREMENT COMMENT '觸發器專用自增列'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='目錄表';

觸發器方案一：基於 LAST_INSERT_ID() 判斷首行 (含邊界 Bug 修正)

DELIMITER //

CREATE TRIGGER `trg_tb_cate_bi` BEFORE INSERT ON `tb_cate`
FOR EACH ROW
BEGIN
    -- 獲取輔助自增列當前最大值 (等同於即將生成的值 - 1)
    SELECT COALESCE(MAX(`increc_id`), 0) INTO @max_id FROM `tb_cate`;
    
    -- 計算新 ID：@max_id + 1
    SET @new_seq = @max_id + 1;
    
    -- 格式化為 5 位零填充字符串
    -- LPAD(str, len, padstr) 比 SUBSTRING(CONCAT...) 更簡潔高效
    SET NEW.`id` = LPAD(CAST(@new_seq AS CHAR), 5, '0');
END;
//

DELIMITER ;

優點：邏輯清晰，LPAD 性能優於字符串拼接截取。
注意：MAX(increc_id) 在高併發下可能有間隙 (事務回滾導致自增 ID 燒掉)，但主鍵 id 依賴 increc_id 生成，故主鍵也會有間隙，符合自增特性。

觸發器方案二：利用 AUTO_INCREMENT 內部計數器 (MySQL 8.0+ 信息_schema 可見)

DELIMITER //

CREATE TRIGGER `trg_tb_cate_bi_v2` BEFORE INSERT ON `tb_cate`
FOR EACH ROW
BEGIN
    -- 從信息模式獲取下一個自增值 (需 SUPER 權限或 8.0+ 權限)
    SELECT `AUTO_INCREMENT` INTO @next_id 
    FROM `information_schema`.`TABLES` 
    WHERE `TABLE_SCHEMA` = DATABASE() AND `TABLE_NAME` = 'tb_cate';
    
    SET NEW.`id` = LPAD(CAST(@next_id AS CHAR), 5, '0');
END;
//

DELIMITER ;

缺點：依賴 information_schema 查詢有額外開銷；高併發下可能與實際分配值有極微小竄動風險 (極罕見)。方案一更穩健。

測試驗證

INSERT INTO `tb_cate` (`name`, `type`) VALUES ('根目錄', '1');
INSERT INTO `tb_cate` (`name`, `type`) VALUES ('子目錄A', '2');
INSERT INTO `tb_cate` (`name`, `type`) VALUES ('子目錄B', '2');

SELECT `id`, `increc_id`, `name` FROM `tb_cate`;
-- 預期結果:
-- id     | increc_id | name
-- 00001  | 1         | 根目錄
-- 00002  | 2         | 子目錄A
-- 00003  | 3         | 子目錄B

擴展：可變長度參數化

若需支援不同長度 (如 8 位、10 位)，可將長度存入表選項或配置表，觸發器中動態讀取：

SET NEW.`id` = LPAD(CAST(@new_seq AS CHAR), @id_length, '0');

避坑指南

1. 輔助列必須 UNIQUE：防止併發插入導致 increc_id 衝突 (極罕見但理論上可能)。
2. 不要在觸發器中 INSERT 同表：會遞歸觸發報錯 Can't update table 'tb_cate' in stored function/trigger because it is already used by statement。
3. 事務回滾燒 ID：increc_id 和 id 都會有間隙，業務不可依賴連續性。
4. 主從複製安全：觸發器在從庫同步執行，邏輯一致，主鍵生成確定性滿足複製要求。