Read it later.

Web製作に関するサーバ構成やコーディングネタの備忘録として使ってます。

*

MySQL用シーケンス制御ファンクション集(PostgreSQL風にファンクションを揃えてみた)

      2013/10/01

MySQLには、PostgreSQLのようなシーケンスが機能的にないので、作ってみました。
ファンション名は、Postgresに合わせてみた。

nextval …シーケンスを進め、新規戻り値を戻す
currval …指定されたシーケンスに対しnextvalで得られた最新の値を返す
setval …シーケンスの現在値を設定する

ワーク用に下記のテーブルを利用しています。
sequences …シーケンス制御テーブル’

利用例は、下記のスクリプトに書いてあります。


/* --+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--
  MySQL用シーケンス制御ファンクション集
    nextval   …シーケンスを進め、新規戻り値を戻す
    currval   …指定されたシーケンスに対しnextvalで得られた最新の値を返す
    setval    …シーケンスの現在値を設定する
  本処理に関連するテーブル
    sequences …シーケンス制御テーブル'
--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+-- */

/*
--# ※SSHから直接実行する場合のみ指定する。
SET NAMES 'utf8'; --# クライアントの文字セットをutf-8に指定
*/
DROP TABLE IF EXISTS sequences;
CREATE TABLE IF NOT EXISTS sequences (
    name                                varchar(255)        NOT NULL  DEFAULT ''          COMMENT 'シーケンス名'
  , currval                             bigint              NOT NULL  DEFAULT 0           COMMENT '現在値'
  , PRIMARY KEY(
      name
    )
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='シーケンス制御テーブル'
;
/* --+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--
[処理概要]
    シーケンスを進め、新規戻り値を戻す
[入力パラメタ]
    name                                シーケンス名
[戻り値]
    bigint                              取得したシーケンス値
[実行例]
SELECT
   nextval(
       'my_serial_key'
   ) AS result_value
;
--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+-- */
DROP FUNCTION IF EXISTS nextval;
DELIMITER //
CREATE FUNCTION nextval(
    name                            varchar(255)
) RETURNS bigint DETERMINISTIC SQL SECURITY INVOKER COMMENT 'シーケンスの新規値取得'
BEGIN
-- #-------------------------------------------------------------------
-- # 変数宣言
-- #-------------------------------------------------------------------
  -- # 戻り値
  DECLARE return_value                  bigint         DEFAULT 0;
  -- # 現在の設定値
  DECLARE current_value                 bigint         DEFAULT 1;
-- #-------------------------------------------------------------------
-- # ここからがメイン処理
-- #-------------------------------------------------------------------
  IF EXISTS(
      SELECT
          *
      FROM
        sequences
      WHERE sequences.name = name
  ) THEN

    SELECT
        sequences.currval + 1
    INTO
        current_value
    FROM
      sequences
    WHERE sequences.name = name
    ;

    UPDATE sequences
    SET
        currval
          = current_value
    WHERE sequences.name = name
    ;

    SET return_value = current_value;

  ELSE

    SET current_value = 1;

    INSERT INTO sequences(
        name
      , currval
    ) VALUES (
        name
      , current_value
    );

    SET return_value = current_value;

  END IF;

-- #-------------------------------------------------------------------
-- # 戻り値をセットして処理を終了
-- #-------------------------------------------------------------------
  RETURN return_value;
END//
DELIMITER ;

/* --+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--
[処理概要]
    指定されたシーケンスに対しnextvalで得られた最新の値を返す
[入力パラメタ]
    name                                シーケンス名
[戻り値]
    bigint                              取得したシーケンス値
[実行例]
SELECT
   currval(
       'my_serial_key'
   ) AS result_value
;
--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+-- */
DROP FUNCTION IF EXISTS currval;
DELIMITER //
CREATE FUNCTION currval(
    name                                varchar(255)
) RETURNS bigint DETERMINISTIC SQL SECURITY INVOKER COMMENT 'シーケンスの現在値取得'
BEGIN
-- #-------------------------------------------------------------------
-- # 変数宣言
-- #-------------------------------------------------------------------
  -- # 戻り値
  DECLARE return_value                  bigint         DEFAULT 0;
-- #-------------------------------------------------------------------
-- # ここからがメイン処理
-- #-------------------------------------------------------------------
  SELECT
      sequences.currval
  INTO
      return_value
  FROM
    sequences
  WHERE sequences.name = name
  ;

-- #-------------------------------------------------------------------
-- # 戻り値をセットして処理を終了
-- #-------------------------------------------------------------------
  RETURN return_value;
END//
DELIMITER ;

/* --+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--
[処理概要]
    シーケンスの現在値を設定する
[入力パラメタ]
    name                                シーケンス名
[戻り値]
    bigint                              現在のシーケンス値
[実行例]
SELECT
   setval(
       'my_serial_key'
      ,1000
   ) AS result_value
;
--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+-- */
DROP FUNCTION IF EXISTS setval;
DELIMITER //
CREATE FUNCTION setval(
    name                                varchar(255)
  , currval                             bigint
) RETURNS bigint DETERMINISTIC SQL SECURITY INVOKER COMMENT 'シーケンスの現在値設定'
BEGIN
-- #-------------------------------------------------------------------
-- # 変数宣言
-- #-------------------------------------------------------------------
  -- # 戻り値
  DECLARE return_value                  bigint         DEFAULT 0;
-- #-------------------------------------------------------------------
-- # ここからがメイン処理
-- #-------------------------------------------------------------------
  IF EXISTS(
      SELECT
          *
      FROM
        sequences
      WHERE sequences.name = name
  ) THEN

    UPDATE sequences
    SET
        currval
          = currval
    WHERE sequences.name = name
    ;

  ELSE

    INSERT INTO sequences(
        name
      , currval
    ) VALUES (
        name
      , currval
    );

  END IF;

  SELECT
      sequences.currval
  INTO
      return_value
  FROM
    sequences
  WHERE sequences.name = name
  ;

-- #-------------------------------------------------------------------
-- # 戻り値をセットして処理を終了
-- #-------------------------------------------------------------------
  RETURN return_value;
END//
DELIMITER ;

 - MySQL

Message

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

CAPTCHA


日本語が含まれない投稿は無視されますのでご注意ください。(スパム対策)

  関連記事

mysql
[MySQL][phpMyAdmin]Allowed memory size of XXXXXX bytes exhausted (tried to allocate XXXXXX bytes)

こんな現象がおきました phpMyAdminでデータベース全体をエクスポートした …

mysql
[MySQL]SELECT文で、FROM句を使用しないで、WHERE句を使用する

『SELECT文で、FROM句を使用しないで、WHERE句を使用する』 といった …

mysql
[MySQL]プロセスの確認と、プロセスの強制終了

ストプロ内でのどのSQLが実行されているかもプロセスを見れば分かる MySQL内 …

mysql
[MySQL]LOAD DATA INFILEで正常にCSVデータを読み込めない

※winscpを使って開発している人だけ、ご覧下さい。 LOAD DATA IN …

mysql
[MySQL]コマンドでのSQL文入力の途中キャンセル

mysqlをコマンドラインで操作している際、特に複数行、SQL文を入力してて途中 …

mysql
[MySQL]テーブルのカラムのコメントの変更

MySQLでカラムのコメントを変更する方法を探してみた。 結局、カラムの再定義と …

mysql
MySQL 権限一覧

権限 意味 ALL [PRIVILEGES] GRANT OPTION 以外の全 …

mysql
mysqlで一般クエリログをする

mysqlで一般クエリログをするためには、 my.cnfをいじるといいのだけど、 …

mysql
MySQLのrootパスワードが分からなくなったら

rootのパスワードが分からなくなったとき、どうすることもできないと観念したら、 …

mysql
[MySQL]データベースごとのサイズをSQLで一発で取得する

各データベースに接続して、単体でデーターベースサイズや、各テーブルのサイズを取得 …