MySQL 自增主鍵獲取與 @@IDENTITY 併發安全性深度解析

背景：Oracle Sequence vs MySQL AUTO_INCREMENT

Oracle 使用 SEQUENCE 產生主鍵；MySQL 採用列屬性 AUTO_INCREMENT。插入後需獲取剛生成的 ID，MySQL 提供 SELECT @@IDENTITY（或 LAST_INSERT_ID()）。

@@IDENTITY / LAST_INSERT_ID() 併發行為

官方文檔： SELECT @@IDENTITY 返回**當前連接**上一次 INSERT 產生的自增值。其他連接的插入**不影響**本連接的返回值。

因此，線程隔離的連接池（每線程一連接）天然安全。Spring、MyBatis、Hibernate 默認使用 ThreadLocal 綁定連接，多線程環境下各線程獲取到的均為自己插入的 ID。

何時會出現「取到別人 ID」？

• 手動將同一 Connection 對象傳遞給多線程並發使用（極差實踐）。
• 非池化場景下，線程間共享單一連接對象。
• 使用 Statement.RETURN_GENERATED_KEYS 配合 getGeneratedKeys() 可完全避免此問題（JDBC 標準方式）。

JDBC 正確獲取自增 ID 代碼

String sql = "INSERT INTO users(name, email) VALUES(?, ?)";
try (Connection conn = dataSource.getConnection();
     PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
    ps.setString(1, "張三");
    ps.setString(2, "zhangsan@example.com");
    ps.executeUpdate();

    try (ResultSet rs = ps.getGeneratedKeys()) {
        if (rs.next()) {
            long generatedId = rs.getLong(1);
            System.out.println("新用戶 ID: " + generatedId);
        }
    }
}

MySQL 模擬 Oracle Sequence（不推薦生產使用，僅教學）

1. 序列管理表

CREATE TABLE wfo_seq (
  name VARCHAR(50) NOT NULL PRIMARY KEY,
  current_value INT NOT NULL,
  increment INT NOT NULL DEFAULT 1
) ENGINE=InnoDB;

2. 核心函數

-- 當前值
CREATE FUNCTION currval(seq_name VARCHAR(50)) RETURNS INT
BEGIN
  DECLARE v INT DEFAULT 0;
  SELECT current_value INTO v FROM wfo_seq WHERE name = seq_name;
  RETURN v;
END;

-- 下一個值（原子更新+返回）
CREATE FUNCTION nextval(seq_name VARCHAR(50)) RETURNS INT
BEGIN
  UPDATE wfo_seq SET current_value = current_value + increment WHERE name = seq_name;
  IF ROW_COUNT() = 0 THEN
    INSERT INTO wfo_seq(name, current_value, increment) VALUES(seq_name, 1, 1);
    RETURN 1;
  END IF;
  RETURN currval(seq_name);
END;

-- 設置值
CREATE FUNCTION setval(seq_name VARCHAR(50), value INT) RETURNS INT
BEGIN
  UPDATE wfo_seq SET current_value = value WHERE name = seq_name;
  RETURN currval(seq_name);
END;

3. 測試

SELECT setval('TestSeq', 10);  -- 初始化
SELECT currval('TestSeq');     -- 10
SELECT nextval('TestSeq');     -- 11
SELECT nextval('TestSeq');     -- 12

生產環境建議

1. **首選 AUTO_INCREMENT + LAST_INSERT_ID()/getGeneratedKeys()**，簡單高效、併發安全。
2. 分庫分表場景：使用 Snowflake 演算法（64 位 Long：時間戳+機器 ID+序列號）或 數據庫段號模式（批次預取 ID），避免單點寫入熱點。
3. 嚴禁手工維護序列表，行級鎖競爭極大，吞吐量低。
4. MySQL 8.0+ 支援 CREATE SEQUENCE（兼容 Oracle 語法），可作為遷移過渡方案。