MySQL 實現 Sequence 序列效果：單表多自增字段完整方案

痛點：AUTO_INCREMENT 的局限

• 單表僅支持一個自增列；
• 步長固定為 1，不可自定義；
• 無法跨表共享序列。

自建序列方案：三層架構

1. 序列表：元數據存儲；
2. 函數：currval()/nextval() 封裝邏輯；
3. 觸發器：INSERT 前自動賦值，業務無感。

完整可運行腳本

1. 序列表

DROP TABLE IF EXISTS sequence;
CREATE TABLE sequence (
  seq_name VARCHAR(50) NOT NULL COMMENT '序列名',
  current_val INT NOT NULL COMMENT '當前值',
  increment_val INT NOT NULL DEFAULT 1 COMMENT '步長',
  PRIMARY KEY (seq_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2. 初始化兩個序列（步長 1 與 2）

INSERT INTO sequence VALUES ('seq_test1_num1', 0, 1);
INSERT INTO sequence VALUES ('seq_test1_num2', 0, 2);

3. 當前值函數

CREATE FUNCTION currval(v_seq_name VARCHAR(50)) RETURNS INT
BEGIN
  DECLARE value INT DEFAULT 0;
  SELECT current_val INTO value FROM sequence WHERE seq_name = v_seq_name;
  RETURN value;
END;

4. 下一值函數（原子更新 + 返回）

CREATE FUNCTION nextval(v_seq_name VARCHAR(50)) RETURNS INT
BEGIN
  UPDATE sequence SET current_val = current_val + increment_val WHERE seq_name = v_seq_name;
  RETURN currval(v_seq_name);
END;

5. 測試函數

SELECT currval('seq_test1_num1');  -- 0
SELECT nextval('seq_test1_num1');  -- 1
SELECT nextval('seq_test1_num2');  -- 2

6. 業務表

DROP TABLE IF EXISTS test1;
CREATE TABLE test1 (
  name VARCHAR(255) NOT NULL,
  value DOUBLE(255,0) DEFAULT NULL,
  num1 INT(11) DEFAULT NULL,
  num2 INT(11) DEFAULT NULL,
  PRIMARY KEY (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

7. BEFORE INSERT 觸發器

CREATE TRIGGER TRI_test1_num1 BEFORE INSERT ON test1
FOR EACH ROW
BEGIN
  SET NEW.num1 = nextval('seq_test1_num1');
  SET NEW.num2 = nextval('seq_test1_num2');
END;

8. 驗證插入效果

INSERT INTO test1 (name, value) VALUES ('1', 111), ('2', 222), ('3', 333), ('4', 444);
SELECT * FROM test1;
/*
+------+-------+------+------+
| name | value | num1 | num2 |
+------+-------+------+------+
| 1    |   111 |    1 |    2 |
| 2    |   222 |    2 |    4 |
| 3    |   333 |    3 |    6 |
| 4    |   444 |    4 |    8 |
+------+-------+------+------+
*/

方案優勢總結

高併發優化建議

單行更新成熱點時，採用「批次預取」：

CREATE FUNCTION nextval_batch(v_seq_name VARCHAR(50), batch_size INT) RETURNS INT
BEGIN
  UPDATE sequence SET current_val = current_val + increment_val * batch_size WHERE seq_name = v_seq_name;
  RETURN currval(v_seq_name) - increment_val * (batch_size - 1);  -- 返回批次起始值
END;

應用端緩存批次範圍，本地分配，耗盡再取下一批，極大降低序列表寫入壓力。