MySQL 模擬 Sequence 序列：自增函數實現與高併發流水號生成方案

一、業務場景：分佈式/多線程唯一流水號

需求：接口要求 int 類型流水號，多線程下時間戳可能重複，UUID 過長，需類 Oracle SEQUENCE 的單調遞增整數。

二、核心實現：Sequence 管理表 + 三個函數

2.1 管理表結構

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

2.2 當前值函數 currval(seq_name)

DELIMITER $
CREATE FUNCTION currval(seq_name VARCHAR(50)) RETURNS BIGINT
DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
BEGIN
  DECLARE value BIGINT DEFAULT 0;
  SELECT current_value INTO value FROM sequence WHERE name = seq_name;
  RETURN value;
END $
DELIMITER ;

2.3 下一值函數 nextval(seq_name)（核心）

DELIMITER $
CREATE FUNCTION nextval(seq_name VARCHAR(50)) RETURNS BIGINT
DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
BEGIN
  UPDATE sequence SET current_value = current_value + increment WHERE name = seq_name;
  RETURN currval(seq_name);
END $
DELIMITER ;

2.4 設置值函數 setval(seq_name, value)

DELIMITER $
CREATE FUNCTION setval(seq_name VARCHAR(50), value BIGINT) RETURNS BIGINT
DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
BEGIN
  UPDATE sequence SET current_value = value WHERE name = seq_name;
  RETURN currval(seq_name);
END $
DELIMITER ;

三、使用示例

public class SequenceGenerator {
  private DataSource dataSource;
  private String seqName;

  public SequenceGenerator(DataSource ds, String seqName) {
    this.dataSource = ds;
    this.seqName = seqName;
  }

  public long nextValue() {
    String sql = "SELECT nextval(?)";
    try (Connection conn = dataSource.getConnection();
         PreparedStatement ps = conn.prepareStatement(sql)) {
      ps.setString(1, seqName);
      try (ResultSet rs = ps.executeQuery()) {
        if (rs.next()) return rs.getLong(1);
        throw new IllegalStateException("Sequence not found: " + seqName);
      }
    } catch (SQLException e) {
      throw new RuntimeException("Get sequence failed", e);
    }
  }
}

// 使用
SequenceGenerator orderSeq = new SequenceGenerator(dataSource, "OrderSeq");
long orderNo = orderSeq.nextValue();  // 線程安全，數據庫層面串行化

五、高併發優化方案

5.1 緩存塊模式（減少數據庫往返）

-- 修改管理表增加緩存字段
ALTER TABLE sequence ADD cache_size INT DEFAULT 100;
ALTER TABLE sequence ADD cached_max BIGINT DEFAULT 0;

-- nextval 邏輯優化：
-- 1. 若 current_value < cached_max：直接 current_value++ 返回（無 UPDATE）
-- 2. 否則：cached_max = current_value + cache_size; UPDATE ...; 返回 current_value++

應用層同樣可實現：批量獲取 100 個號段到內存，用完再取。

5.2 分段序列（分庫分表場景）

CREATE SEQUENCE order_seq START WITH 1 INCREMENT BY 1 CACHE 100;
SELECT NEXT VALUE FOR order_seq;  -- 獲取下一值
SELECT LAST_VALUE FROM order_seq; -- 當前值

原生 Sequence 效能更高、語法標準、支援緩存、循環、權限控制。**MySQL 8.0+ 專案優先使用原生**。

七、自增列 vs Sequence 對比

八、總結

1. MySQL 5.7 及以下：自建 sequence 表 + nextval/currval/setval 函數模擬 Oracle Sequence
2. 高併發：應用層號段緩存（批量獲取）大幅降低數據庫壓力
3. MySQL 8.0+：**直接使用原生 CREATE SEQUENCE**，標準、高效、免維護
4. 分佈式場景：考慮 Snowflake、UidGenerator 等去中心化方案，避免單點