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');