Hatena::ブログ(Diary)

Yet Another Hackadelic

2011-08-02

変数とストアドプロシージャについて

追記しました (2011-08-08T16:57:50Z)

割と弊社では MySQL の event scheduler を使ってみたり、ストアドプロシージャ、トリガーなど積極的に使っています。

今回はストアドプロシージャを書く上で、変数を色々使うのですが備忘録としてメモっておきます。

MySQL における変数には幾つか種類があります。

  • システム変数 (c.f. @@version)
  • ユーザー変数 (c.f. @var_name)
  • ローカル変数 (c.f. DECLARE var_name INT, ストアドプロシージャ内で使える)

これら変数の特徴は下記です。(MySQL 5.1.50 で確認)

-- システム変数 ユーザー変数 ローカル変数
スコープ GLOBAL *1 SESSION LOCAL
なし なし あり
宣言 なし なし あり
設定 SET GLOBAL / SET SESSION SET SET
SELECT INTO 不可
EXECUTE USING 不可 不可

一応幾つか説明しておくと、まずは SELECT INTO ですけど、

SELECT foo, bar INTO @foo_value, @bar_value FROM sample WHERE id = 10;

とかやると foo, bar の値がそれぞれ @foo_value, @bar_value に突っ込まれます。

次に EXECUTE USING ですけど、

SET @sample_id = 1;
PREPARE stmt FROM 'SELECT * FROM sample WHERE id = ?';
EXECUTE stmt USING @sample_id;
DEALLOCATE PREPARE stmt;

みたいな感じで事前に作った placeholder 付きのステートメントを EXECUTE で値を bind して使う事が出来ますが、残念ながらローカル変数をここに用いてはいけません。

考え方にもよりますが、最近のストアドプロシージャ内での編数の使い方として DECLARE で宣言した変数はテーブルのカラム名と区別出来る命名にしないとややこしくて分からなくなるのと、

placeholder への bind に使えないのでユーザー変数だけの利用に原則とどめています。

追記 (2011-08-08T16:57:50Z)

2011-08-06 - tsucchiの日記 で言及されてたのでちょっと触れておきます。

1. あんまり早くない

    • 対比として DBI を使ったスクリプトならば、アドバンテージがあるとしたらネットワークを介してクエリを打ち込まなくなる程度の物しか無いですね。なので大して早く無いというのは概ね同意です

2. スローログの件

    • そもそもスローログに載るような用途には使ってないので、この点に関してはなるほどなぁと言う感じです。

3. コンパイルエラーの件

    • これどうにかなりませんかねぇ。

4. テストが書きにくい

    • それ Test::Fixture::DBI で出来るよ

とまぁ、大体 id:tsucchi1022 さんと同じ意見ではあるのですが、弊社で使っている用途について簡単に説明すると現時点では、

1. Partition の add, drop を procedure + event scheduer で実施している

2. Trigger の内部で呼び出したりしている

3. 最近は Purge をプロシージャでやってたりする

    • Purge の度に書き捨てのスクリプト作るのもなんだかなぁと最近思ってたので。

いずれにせよ、Experimental な使い方している感は多々あります。Purge に関しては sql_log_bin = 0 としてバイナリログを無効化し、すべてのマスタとスレーブで同じ purge 用の procedure を叩いたりしてます。

See Also

*1:ただし SET SESSION 文の場合は SESSION 単位

はてなユーザーのみコメントできます。はてなへログインもしくは新規登録をおこなってください。

リンク元