您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 自定義序列號生成方案：函數 + 觸發器實現業務單號自動編碼

編輯：MySQL 綜合教學

MySQL 存儲函數與 BEFORE INSERT 觸發器實現業務流水號自動生成

業務常需按規則生成唯一單號 (如 TS24010001)，MySQL 無原生 Sequence (8.0 前)，可通過自定義函數封裝邏輯 + 觸發器自動調用 實現。

一、生成函數 `get_workNo()`

DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `get_workNo`() RETURNS VARCHAR(45) CHARSET utf8
BEGIN
    DECLARE newWorkNo VARCHAR(45);
    DECLARE currentDate VARCHAR(15);   -- 年月如 2401
    DECLARE maxNo INT DEFAULT 0;
    DECLARE oldWorkNo VARCHAR(25) DEFAULT '';

    -- 取當前年月後 6 位 (如 202401 -> 2401)
    SELECT SUBSTRING(DATE_FORMAT(NOW(), '%Y%m'), 3, 6) INTO currentDate;

    -- 查詢本月最新單號 (按主鍵 id 倒序取第一條)
    SELECT IFNULL(workNo, '') INTO oldWorkNo
    FROM operation_work
    WHERE SUBSTRING(workNo, 3, 4) = currentDate
    ORDER BY id DESC LIMIT 1;

    IF oldWorkNo != '' THEN
        -- 截取末尾 4 位流水號轉數值
        SET maxNo = CONVERT(SUBSTRING(oldWorkNo, -4), DECIMAL);
    END IF;

    -- 拼接: 前綴 TS + 年月 4 位 + 4 位流水號 (不足左補 0)
    SELECT CONCAT('TS', currentDate, LPAD((maxNo + 1), 4, '0')) INTO newWorkNo;
    RETURN newWorkNo;
END$$
DELIMITER ;

二、插入觸發器

USE `it_system`;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` TRIGGER `operation_work_BINS`
BEFORE INSERT ON `operation_work`
FOR EACH ROW
BEGIN
    SET @workNo = get_workNo();
    SET NEW.workNo = @workNo;
END$$
DELIMITER ;

三、關鍵設計點解析

環節	說明
並發安全	單實例下 `SELECT ... ORDER BY id DESC LIMIT 1` 串行化讀取最新 id，配合表鎖/行鎖隱式保護；高併發建議改用單獨序列表 + `SELECT ... FOR UPDATE` 或 Redis 原子計數器。
規則可配置	前綴、日期格式、流水位數均在函數中可調，業務變更只改函數無需動表結構。
觸發器時機	`BEFORE INSERT` 確保單號寫入同一事務，避免單獨 UPDATE 產生額外 Binlog/鎖開銷。
性能	函數內單表索引查詢 (workNo 前綴索引)，極快；若表巨大建議歸檔歷史數據。