MySQL 自定義 Sequence 序列實現：函數封裝、多序列隔離、Java 調用完整教程

前言

MySQL 無原生 SEQUENCE 對象（MariaDB 10.3+、MySQL 8.0+ 已支援），舊版本需自建表 + 函數模擬。本文提供生產就緒的 Sequence 實現：管理表、三大核心函數（currval/nextval/setval）、測試用例、Java 調用代碼，並對比 Oracle Sequence 差異。

一、核心對象建立

1. 序列管理表

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

2. 取當前值 currval(seq_name)

DROP FUNCTION IF EXISTS currval;
DELIMITER $
CREATE FUNCTION currval (seq_name VARCHAR(50)) RETURNS INTEGER
LANGUAGE SQL DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT ''
BEGIN
  DECLARE value INTEGER;
  SET value = 0;
  SELECT current_value INTO value FROM sequence WHERE name = seq_name;
  RETURN value;
END $
DELIMITER ;

3. 取下一值 nextval(seq_name)

DROP FUNCTION IF EXISTS nextval;
DELIMITER $
CREATE FUNCTION nextval (seq_name VARCHAR(50)) RETURNS INTEGER
LANGUAGE SQL DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT ''
BEGIN
  UPDATE sequence SET current_value = current_value + increment WHERE name = seq_name;
  RETURN currval(seq_name);
END $
DELIMITER ;

4. 設定當前值 setval(seq_name, value)

DROP FUNCTION IF EXISTS setval;
DELIMITER $
CREATE FUNCTION setval (seq_name VARCHAR(50), value INTEGER) RETURNS INTEGER
LANGUAGE SQL DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT ''
BEGIN
  UPDATE sequence SET current_value = value WHERE name = seq_name;
  RETURN currval(seq_name);
END $
DELIMITER ;

二、測試驗證

-- 建立序列 TestSeq，初始 0，步長 1
INSERT INTO sequence VALUES ('TestSeq', 0, 1);

-- 設定初始值為 10
SELECT SETVAL('TestSeq', 10);  -- 返回 10

-- 查詢當前值
SELECT CURRVAL('TestSeq');     -- 返回 10

-- 獲取下一值（自增）
SELECT NEXTVAL('TestSeq');     -- 返回 11
SELECT NEXTVAL('TestSeq');     -- 返回 12

三、Java 調用範例

public void testGetSequence() {
  Connection conn = JDBCUtils.getConnection(url, userName, password);
  String sql = "SELECT NEXTVAL('TestSeq')";  // 獲取下一值作為流水號
  PreparedStatement ptmt = null;
  ResultSet rs = null;
  try {
    ptmt = conn.prepareStatement(sql);
    rs = ptmt.executeQuery();
    int seq = 0;
    if (rs.next()) seq = rs.getInt(1);
    System.out.println("Sequence: " + seq);
  } catch (SQLException e) {
    e.printStackTrace();
  } finally {
    JDBCUtils.close(rs, ptmt, conn);
  }
}

多線程環境下，UPDATE ... SET current_value = current_value + increment 由 InnoDB 行鎖保證原子性，無需額外同步。

四、進階版：大整型、大小寫不敏感、多業務隔離

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

DELIMITER /
DROP FUNCTION IF EXISTS currval /
CREATE FUNCTION currval(seq_name VARCHAR(50)) RETURNS BIGINT
BEGIN
  DECLARE value BIGINT;
  SELECT current_value INTO value FROM sequence WHERE UPPER(name) = UPPER(seq_name);
  RETURN value;
END;
/

DROP FUNCTION IF EXISTS nextval /
CREATE FUNCTION nextval (seq_name VARCHAR(50)) RETURNS BIGINT
BEGIN
  DECLARE value BIGINT;
  UPDATE sequence SET current_value = current_value + increment WHERE UPPER(name) = UPPER(seq_name);
  RETURN currval(seq_name);
END;
/

DROP FUNCTION IF EXISTS setval /
CREATE FUNCTION setval (seq_name VARCHAR(50), value BIGINT) RETURNS BIGINT
BEGIN
  UPDATE sequence SET current_value = value WHERE UPPER(name) = UPPER(seq_name);
  RETURN currval(seq_name);
END;
/
DELIMITER ;

五、MySQL 自增列 vs 自定義 Sequence 對比

六、選型建議

• 單表主鍵：優先用 AUTO_INCREMENT
• 分庫分表 Sharding Key、分布式流水號、跨業務全局唯一 ID：自定義 Sequence 或 Snowflake/Leaf 框架
• MySQL 8.0+ / MariaDB 10.3+：直接使用原生 CREATE SEQUENCE