Edited at

MySQL で シーケンス 機能実現

More than 1 year has passed since last update.

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


  • 1つのテーブルには1つの 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');