MySQL 事務實現高併發安全的全域自增 ID 生成器

背景需求

分表分庫架構下，單表 AUTO_INCREMENT 無法保證全域唯一。需要一個全域 ID 生成器，支援高併發、無重複、效能佳。

方案一：專用序號表 + 存儲過程 + 行鎖（已驗證）

1. 建立序號表

CREATE TABLE auto_id (
    idname VARCHAR(20) NOT NULL DEFAULT '',
    id BIGINT(20) NOT NULL DEFAULT 0 COMMENT '',
    PRIMARY KEY (idname)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2. 建立存儲過程

DELIMITER //
DROP PROCEDURE IF EXISTS get_increment_id;
CREATE PROCEDURE get_increment_id(
    IN idname_in VARCHAR(20),
    IN small_in BIGINT,
    OUT id_out BIGINT
)
BEGIN
    DECLARE oldid BIGINT;
    START TRANSACTION;
    SELECT id INTO oldid FROM auto_id WHERE idname = idname_in FOR UPDATE;
    IF oldid IS NULL THEN
        INSERT INTO auto_id (idname, id) VALUES (idname_in, small_in);
        SET id_out = small_in;
    ELSE
        UPDATE auto_id SET id = id + 1 WHERE idname = idname_in;
        SET id_out = oldid + 1;
    END IF;
    COMMIT;
END //
DELIMITER ;

3. 核心機制

SELECT ... FOR UPDATE 給目標行加排他鎖，其他併發請求阻塞等待，直到事務提交，保證同一 idname 同一時間只有一個進程獲取 ID。

4. PHP 呼叫範例

$sql = "CALL get_increment_id('{$key}', {$small}, @id)";
$db->query($sql);
$ret = $db->query("SELECT @id");
$id = $ret[0]['@id'];

方案二：利用 LAST_INSERT_ID() 機制（未實測，理論可行）

1. 建立單欄自增表

CREATE TABLE test (
    id INT(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
    PRIMARY KEY (id)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

2. 兩條 SQL 獲取 ID

UPDATE test SET id = LAST_INSERT_ID(id + 1);
SELECT LAST_INSERT_ID();

3. 原理說明

• LAST_INSERT_ID() 為連接級別，不受其他連接影響
• 無需查表，效能極高
• 缺點：每種 ID 類型需一張表維護

選型建議

根據業務複雜度選擇：簡單場景用方案二，需統一管理多類型 ID 用方案一。