MySQL 模擬序列 (Sequence) 實現：單行表 + LAST_INSERT_ID 原子自增

MySQL 模擬序列 (Sequence) 實現：單行表 + LAST_INSERT_ID 原子自增

MySQL 8.0 前無原生 CREATE SEQUENCE (8.0+ 已支持)，傳統方案通過**單行表 + LAST_INSERT_ID() 函數**實現線程安全、高性能的序列號生成，邏輯等同於 Oracle SEQUENCE。

核心原理

LAST_INSERT_ID(expr) 將函數返回值設置為 expr，並更新該連接的「最後插入 ID」上下文變量。後續同連接調用 SELECT LAST_INSERT_ID() 即可獲取該值，**不受並發影響**，屬於連接級別隔離。

DDL 與初始化

CREATE TABLE emp_seq (
    seq BIGINT NOT NULL
) ENGINE=InnoDB;

INSERT INTO emp_seq VALUES (0); -- 起始值

獲取下一序列值 (原子操作)

-- 步驟 1: 更新並設置 LAST_INSERT_ID
UPDATE emp_seq SET seq = LAST_INSERT_ID(seq + 1);

-- 步驟 2: 獲取新值
SELECT LAST_INSERT_ID();

也可合併為單條語句 (需客戶端支持多結果集) 或封裝為存儲過程：

DELIMITER //
CREATE PROCEDURE next_val(OUT next_id BIGINT)
BEGIN
    UPDATE emp_seq SET seq = LAST_INSERT_ID(seq + 1);
    SELECT LAST_INSERT_ID() INTO next_id;
END //
DELIMITER ;

-- 調用
CALL next_val(@next_id);
SELECT @next_id;

Java/JDBC 調用示例

public long nextSeqValue() {
    String sql = "UPDATE emp_seq SET seq = LAST_INSERT_ID(seq + 1); SELECT LAST_INSERT_ID();";
    try (Connection con = ds.getConnection();
         Statement stmt = con.createStatement()) {
        // 需要 allowMultiQueries=true (連接串參數)
        boolean hasResults = stmt.execute(sql);
        if (hasResults) {
            try (ResultSet rs = stmt.getResultSet()) {
                if (rs.next()) return rs.getLong(1);
            }
        }
        // 兼容不支持多查詢的驅動：分兩次 execute
        return -1;
    } catch (SQLException e) {
        throw new RuntimeException("獲取序列失敗", e);
    }
}

連接串關鍵參數：jdbc:mysql://host:3306/db?allowMultiQueries=true 允許單次 execute 多條語句。

與 Oracle SEQUENCE 對比

高並發優化建議

• 應用層批量取號：一次 UPDATE seq = LAST_INSERT_ID(seq + 1000) 取 1000 號，本地原子計數器分配，減少數據庫交互 1000 倍。
• 分段序列：多張序列表 (如 emp_seq_0...emp_seq_9)，按 hash(id) % 10 路由，分散熱點。
• 升級 MySQL 8.0+：使用原生 CREATE SEQUENCE，支持 CACHE、NEXTVAL 函數、無縫遷移。

總結

單行表 + LAST_INSERT_ID 是 MySQL 5.x/5.7 時代標準、可靠的序列實現，核心優勢在於**連接級隔離性**保證並發正確性。高並發場景建議引入應用層批量取號或遷移至 MySQL 8.0 原生序列。