MySQL で シーケンス 機能実現

普通は AUTO_INCREMENT を使いますが、不便なところがありあす：

• １つのテーブルには１つの AUTO_INCREMENT カラムしか設定できません。
• AUTO_INCREMENT カラムはテーブルに依存する。
• 新規データをインサートする時、AUTO_INCREMENT カラムには NULL で指定が必要。

MySQL でシーケンス機能を実現する方法は以下になります。

シーケンス管理用テーブル作成

sequence

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;

現在のシーケンス番号を取得する関数

currval

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 ;

次のシーケンス番号を取得する関数

nextval

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 ;

現在のシーケンス番号を更新する関数

setval

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 ;

テスト

test

INSERT INTO sequence VALUES ('TestSeq', 0, 1);
SELECT setval('TestSeq', 10);
SELECT currval('TestSeq');
SELECT nextval('TestSeq');