minimize

事業拡大のため、新しい仲間を募集しています。
→詳しくはこちら

ここでは、MySQLに関する事を書いていきます。
新しく書いたものが上になるように並べてあります。

MySQL5でストアドプロシージャを使う

ついにMySQL5において念願のストアドプロシージャがサポートされることになりました。
まだ正式リリースではありませんが、早速ダウンロードして試してみることにします。

MySQLでは、SQL:2003 という規格が採用されています。
これはIBMのDB2等でも使われているようです。Oracleの10でも使われるとか使われないとか。
以前のストアドプロシージャと互換性はあるらしいので、
OracleのPL/SQL等を利用している人ならば簡単に移行できるでしょう。

なお、以下の内容はほぼMySQLのマニュアルに載っていることを日本語で説明しているに過ぎません。
わからない事があったらマニュアル原文を読むことをお勧めします。

プロシージャを宣言する

CREATE PROCEDURE sp_name ([parameter[,...]])

[characteristic ...] routine_body

プロシージャを宣言します。

関数を宣言する

CREATE FUNCTION sp_name ([parameter[,...]])

[RETURNS type]

[characteristic ...] routine_body

関数を宣言します。プロシージャとの違いは、関数は戻り値を持つということです。
そのため、宣言文で戻り値の型を定義します。
代わりに、引数は入力タイプ(IN)しか使用できません。

実際にプロシージャを使ってみる

まず最初にやることがあります。
それは「デリミタ文字列の宣言」です。
通常、MySQLは ;(セミコロン) をコマンドの終わりとして認識します。
しかし、プロシージャを記述する場合にはそれだと都合が悪いのです。
プロシージャ内において ; はコマンドの終わりではなく、単に行の終わりを意味します。

そこで、プロシージャを定義する前にデリミタ文字列を変更しておく必要があります。
MySQLプロンプトから以下を実行します。

delimiter |

デリミタ文字は別に何でもいいのですが、通常使われない文字である必要があります。
では、次に進みましょう。

CREATE PROCEDURE simpleproc (OUT param1 INT)
BEGIN
  SELECT COUNT(*) INTO param1 FROM t;
END
|

デリミタ文字列の | を入力することによって、MySQLがプロシージャ宣言の終了を認識します。

プロシージャの処理内容は単純です。
t という名前のテーブルに存在するレコード数を、プロシージャの引数に格納します。
使い方は以下のようになります。

CALL simpleproc(@a)|

プロシージャを呼び出すキーワード、CALL を使っています。
@a というのは「テンポラリ変数」です。
プログラム言語でいうところのローカル変数と同じ使い方ができます。
これだけではプロシージャがどんな値を返したのかわからないので、表示させてみます。

SELECT @a|

テンポラリ変数の値は、セッションが切れるまで有効です。

関数を使う

次に関数を定義してみましょう。
ほとんどプロシージャの場合と同じです。

CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50)
RETURN CONCAT('Hello, ',s,'!');
|

呼び出しは以下のようになります。

SELECT hello('world')|

プロシージャと違うところは、関数は戻り値を持つという事です。
その為、プロシージャ本体の最後で RETURN を使用しています。
パラメータには入力タイプのみが使用できます。
呼び出し方法は通常の関数と同じです。CALL は使用しません。

プロシージャの定義を変更する

プロシージャ(関数)を一度宣言した後、コメント等を付加させるには以下の構文を使用します。

ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...]

プロシージャを削除する

DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

プロシージャ宣言文を表示する

CREATE PROCEDURE によって定義したプロシージャ(関数)宣言を表示します。

SHOW CREATE {PROCEDURE | FUNCTION} sp_name

プロシージャ一覧を表示する

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE pattern]

プロシージャを呼び出す

CALL sp_name([parameter[,...]])

プロシージャ本体

[begin_label:] BEGIN
  statement(s)
END [end_label]

begin_labelend_label は(もし記述する場合には)同一にする必要があります。

DECLARE

様々なアイテムを定義します。

DECLARE文は、BEGINEND の間に記述する必要があります。
さらに、全てのステートメント文よりも前で記述されていなければなりません。

ローカル変数の定義

DECLARE var_name[,...] type [DEFAULT value]

プロシージャ内で有効なローカル変数を定義します。

変数に値を格納する

SET variable = expression [,...]

SELECTの結果を変数に格納する

SELECT column[,...] INTO variable[,...] table_expression

通常のSELECT文とほぼ同様です。
variable にはローカル変数、テンポラリ変数、またはMySQLサーバが持つグローバル変数が使用できます。
当然ですが、columnの個数とvariableの個数は合わせる必要があります。
そして、SELECTの結果は先頭の1行のみ有効です。

Conditions and Handlers

コンディションを定義します。

DECLARE condition_name CONDITION FOR condition_value

ハンドラを定義します。
ハンドラとは、プロシージャ内のSQLステートメントを実行中に
コンディションで指定した例外をキャッチした場合に実行するロジックの事です。

DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement

ハンドラにキャッチされない例外が発生した場合、
プロシージャは強制終了されます。

カーソル

カーソルを定義します。

DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
DECLARE cursor_name CURSOR FOR sql_statement

カーソルを開く

指定したカーソルを開きます。

OPEN cursor_name

カーソルをフェッチする

指定したカーソルをフェッチします。
現在行のレコードを変数に格納し、行を一つ先に進めます。

FETCH cursor_name

カーソルを閉じる

指定したカーソルを閉じます。

CLOSE cursor_name

IF文

IF search_condition THEN statement(s)
[ELSEIF search_condition THEN statement(s)]
...
[ELSE statement(s)]
END IF

説明不要ですね。

CASE文

CASE case_value

WHEN when_value THEN statement

[WHEN when_value THEN statement ...]

[ELSE statement]

END CASE

LOOP文

条件無しのループを定義します。

[begin_label:] LOOP

statement(s)

END LOOP [end_label]

begin_labelend_label は(もし記述する場合には)同一にする必要があります。
ループを抜けるには、後述の LEAVE文 を使用します。

LEAVE文

LEAVE label

ループ等のブロックを脱出します。
C,Javaのbreakに相当します。

ITERATE文

ITERATE label

ループ等のブロックを始めから実行します。
C,Javaのcontinueに相当します。

WHILE文

条件付きループを定義します。

[begin_label:] WHILE search_condition DO

statement(s)

END WHILE [end_label]

search_condition が成立している間ループを繰り返します。

REPEAT文

条件付きループを定義します。

[begin_label:] REPEAT

statement(s)

UNTIL search_condition
END REPEAT [end_label]

search_condition が成立するまでループを繰り返します。
つまり、

while (!search_condition) statement(s);

に相当します。

複数バージョンのMySQLを同一マシンから動かす

サーバ上のMySQLをバージョンアップするときなど、必要不可欠になるのがこの作業です。
現行のMySQLを動かしたまま、新バージョンのMySQLを動作させることが目標です。

確認に使用したバージョンは MySQL 4.0.18 です。
ちなみに、現在動いているMySQLのバージョンは 3.23.53 でした。
使用OSはRedHat Linux 7.2です。

新バージョンのMySQLをインストールする

今回はソースからコンパイルしました。

./configure --with-tcp-port=13306 --with-unix-socket-path=/tmp/mysql.sock-4.0.18 \
    --prefix /usr/local/mysql-4.0.18

使用するポート番号とソケットパス、それとインストールディレクトリを明記して
configureを実行します。
もしかしたら前の二つは実際には必要ないかもしれません。
結局後で設定ファイルに記述する事になるからです。
しかし今回はとりあえず初めてなので、念には念を入れてやることにします。

後は通常通りインストール作業を行います。

make
make install

ここで一つアドバイスを。
通常、make install の前にはchrootする事が多いと思いますが、今回は予めrootで

mkdir /usr/local/mysql-4.0.18
chmod someuser:someuser /usr/local/mysql-4.0.18

を実行しておき、chrootせずに make install を実行することにしました。
こうすれば、一般ユーザーでインストールする事になるので
/usr/local/bin 等に格納してある現行のMySQLコマンドを誤って消してしまうことが無くなります。
これをやってしまうと、現行のMySQLが動かなくなる危険性もありますから。

設定ファイルを作成する

新バージョンのMySQLを実行するユーザーを新規で作成しましょう。
もしくは既存ユーザーでも構いません。
そのユーザーのホームディレクトリ上に、.my.cnf を作成します。

[mysqld]
port=13306
basedir=/usr/local/mysql-4.0.18
datadir=/usr/local/mysql-4.0.18/var
socket=/tmp/mysql.sock-4.0.18
log=/usr/local/mysql-4.0.18/var/log

設定項目の内容は以下の通りです。各自自分の環境に合わせて置き換えて下さい。

MySQLデータベースを初期化する

ソースを展開したディレクトリ上(MySQLをインストールしたディレクトリでは無い)で、

scripts/mysql_install_db

を実行します。なんかエラーが出るかもしれませんが、
データベース初期ファイルが生成されていればOKです。
datadir で指定したディレクトリに mysql, test という二つのディレクトリが
作成されていることを確認しましょう。

MySQLサーバーを起動する

ここまで来れば、あとはサーバを実行するだけです。
新バージョンのMySQLをインストールしたディレクトリ上で、

./bin/mysqld_safe &

を実行します。
MySQL4から、実行スクリプト名が以前の safe_mysqld から変わっています。内容は同じです。

Starting mysqld daemon with databases from /usr/local/mysql-4.0.18/var

というメッセージだけが出力されれば成功です。
設定ファイルに記述ミスがあった場合などは、
エラーメッセージが出力されてサーバは起動されません。

確認する

無事実行が確認できたら、実際にMySQLに接続しましょう。
現行のMySQLと新バージョンのMySQL、どちらにも接続できればOKです。
そして、新バージョンのMySQLだけをシャットダウンさせてみます。
新バージョンのMySQLをインストールしたディレクトリ上で、

./bin/mysqladmin shutdown

を実行して下さい。

mysqld ended

というメッセージが表示されるはずです。
最後に、この段階でも現行のMySQLに接続できることを確認しておきましょう。