search
LoginSignup
73

More than 3 years have passed since last update.

posted at

updated at

MySQL で シーケンス 機能実現

普通は 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');

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
What you can do with signing up
73