MySQL で sequence 作ってみたが... イマイチ...

※ 2015/02/08 追記: 以下に例示した sequence のエミュレーションは全然ダメ。使わないでください、って誰も使うわけないかw
※ 2015/02/08 の日記にちょっと進化させたやつを載せてますが、まだ少し難点あり。

MySQL で、複数の同時接続を考慮した sequence が必要だったので以下 URL を参考に作ってみた(like  PostgreSQL)。

参考: MySQL で シーケンス 機能実現, Emulating nextval() function to get sequence in MySQL

が、どうしても外側でトランザクションを併用しなければならないというダサい実装になってしまう。

つーのも MySQL の SELECT FOR UPDATE はトランザクション内でしかロックが効かない
(InnoDBでしか確認してないが)、というのと、Function 内でトランザクションが使えない
ということから、今回作ったやつだと以下のような感じで nextval() を実行しなければな
らない...

begin; select nextval('myseq'); commit;

うーーん、あまりにもダサい気がする。一応ソースを載せておくのでどなたかご指摘を
いただけますと助かります(電車の中でうんこ座りで書いたのでひどいコード... という
言い訳をしておこうw)。

(上記参考のjlakeさんのコードの一部を使わせてもらってますm(__)m)

-- ### sequence テーブル定義 ###
CREATE TABLE sequence (
    sequence_name VARCHAR(50) NOT NULL,
    current_value BIGINT,
    increment BIGINT NOT NULL DEFAULT 1,
    start_value BIGINT,
    min_value BIGINT,
    max_value BIGINT,
    is_cycle BOOLEAN DEFAULT FALSE,
    PRIMARY KEY (sequence_name)
);

-- ### sequence テーブルデフォルト定義 ###
DELIMITER //
CREATE TRIGGER sequence_default BEFORE INSERT ON sequence
FOR EACH ROW
BEGIN
    IF NEW.increment > 0 THEN
        IF NEW.max_value IS NULL THEN
            SET NEW.max_value = POWER(2, 63) - 1;
        END IF;
        IF NEW.min_value IS NULL THEN
            SET NEW.min_value = 1;
        END IF;
        IF NEW.start_value IS NULL THEN
            SET NEW.start_value = NEW.min_value;
        END IF;
    ELSE
        IF NEW.max_value IS NULL THEN
            SET NEW.max_value = -1;
        END IF;
        IF NEW.min_value IS NULL THEN
            SET NEW.min_value = POWER(-2, 63) - 1;
        END IF;
        IF NEW.start_value IS NULL THEN
            SET NEW.start_value = NEW.max_value;
        END IF;
    END IF;
END
//
DELIMITER ;

-- ### シーケンス関数内部利用のテンポラリテーブル定義 ###
DELIMITER //
CREATE PROCEDURE _create_tmp_sequence ()
BEGIN
    CREATE TEMPORARY TABLE IF NOT EXISTS tmp_sequence (
        tmp_sequence_name VARCHAR(50) KEY,
        tmp_current_value INT
    );
END
//
DELIMITER ;

-- ### currval() ###
DELIMITER //
CREATE FUNCTION currval (seq VARCHAR(50))
     RETURNS INTEGER
     LANGUAGE SQL
     DETERMINISTIC
     CONTAINS SQL
     SQL SECURITY DEFINER
     COMMENT ''
BEGIN
     DECLARE mes varchar(128);
     DECLARE my_currval INT;
     CALL _create_tmp_sequence();
     SELECT tmp_current_value
     INTO my_currval
     FROM tmp_sequence
     WHERE tmp_sequence_name = seq;

     IF my_currval IS NULL THEN
         IF (SELECT 1 FROM sequence WHERE sequence_name = seq) THEN
             -- 一度も nextval() or setval() してない
             SET mes = concat('currval of sequence "',
                               seq,
                              '" is not yet defined in this session');
         ELSE
             -- そもそも sequence がない
             SET mes = concat('sequence "', seq, '" does not exist');
         END IF;
         SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = mes;
     END IF;
     RETURN my_currval;
END
//
DELIMITER ;

-- ### setval() ###
DELIMITER //
CREATE FUNCTION setval (seq VARCHAR(50), val INTEGER)
    RETURNS INTEGER
    LANGUAGE SQL
    DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
    DECLARE dummy INT;
    DECLARE mes VARCHAR(128);
    CALL _create_tmp_sequence();

    IF (SELECT 1 FROM sequence WHERE sequence_name = seq FOR UPDATE) THEN
        -- ロックして current_value の設定
        UPDATE sequence
        SET current_value = val
        WHERE sequence_name = seq;
    ELSE
        -- そもそも sequence がない
        SET mes = concat('sequence "', seq, '" does not exist');
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = mes;
    END IF;

    REPLACE INTO tmp_sequence VALUES (seq, val);
    RETURN val;
 END
//
DELIMITER ;

-- ### nextval() ###

DELIMITER //
CREATE FUNCTION nextval (seq VARCHAR(50))
    RETURNS INT
    LANGUAGE SQL
    DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
 BEGIN
    DECLARE my_sequence_name VARCHAR(50);
    DECLARE my_current_value BIGINT;
    DECLARE my_next_value BIGINT;
    DECLARE my_increment BIGINT;
    DECLARE my_min_value BIGINT;
    DECLARE my_max_value BIGINT;
    DECLARE my_start_value BIGINT;
    DECLARE my_cycle BOOLEAN;
    DECLARE mes VARCHAR(128);
    CALL _create_tmp_sequence();
    SELECT sequence_name, current_value, increment,
         min_value, max_value, is_cycle, start_value
    INTO my_sequence_name, my_current_value, my_increment,
         my_min_value, my_max_value, my_cycle, my_start_value
    FROM sequence
    WHERE sequence_name = seq
    FOR UPDATE;

    IF my_sequence_name IS NULL THEN
        -- そもそも sequence がない。
        SET mes = concat('sequence "', seq, '" does not exist');
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = mes;
    ELSEIF my_current_value IS NULL THEN
        -- sequence はあるが、まだ一度も使ったことがない -> start_value を設定
        SET my_next_value = my_start_value;
    ELSE
        -- increment 分加算
        SET my_next_value = my_current_value + my_increment;
    END IF;
    IF my_next_value > my_max_value THEN
        IF my_cycle THEN
            -- max_value を超えてしまったら min_value に戻す
            SET my_next_value = my_min_value;
        ELSE
            SET mes = concat('nextval: reached maximum value of sequence ',
                             seq,
                             '(', my_max_value,')');
   SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = mes;
        END IF;
    END IF;
    UPDATE sequence
    SET current_value = my_next_value
    WHERE sequence_name = seq;
    REPLACE INTO tmp_sequence VALUES (seq, my_next_value);
    RETURN my_next_value;
 END
//
DELIMITER ;
-- デフォルト(値は PostgreSQL チック)で良い場合
INSERT INTO sequence (sequence_name) VALUES ('myseq1');
-- 2 ずつ increment の場合
INSERT INTO sequence (sequence_name, incremetn) VALUES ('myseq2', 2);
-- 1 ずつ increment(デフォルト) かつ start を 10 から, 最大値 9999, 最小値 10
INSERT INTO sequence (sequence_name, start_value, min_value, max_value)
  VALUES ('myseq3', 10, 10, 9999);