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

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

割と弊社では MySQL の event scheduler を使ってみたり、ストアドプロシージャ、トリガーなど積極的に使っています。
今回はストアドプロシージャを書く上で、変数を色々使うのですが備忘録としてメモっておきます。

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

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

一応幾つか説明しておくと、まずは 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-06 - tsucchiの日記で言及されてたのでちょっと触れておきます。

1. あんまり早くない

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

2. スローログの件

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

4. テストが書きにくい

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

1. Partition の add, drop を procedure + event scheduer で実施している
2. Trigger の内部で呼び出したりしている
3. 最近は Purge をプロシージャでやってたりする

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

日向夏特殊応援部隊