裏MySQL クエリー入門 基礎編3 (3) ユーザ変数

mysql

MySQLのユーザ変数はSQL中で使える変数をDB側で持てるという機能です。
使い方についてはMySQLのマニュアルユーザ変数を参考にして頂くとして、現在、他のRDBMSでは専用のクエリーが用意されているものについて、このユーザ変数を活用することでMySQLで解決できる場合が多々あります。

ユーザ変数使用上の注意として、デフォルトは文字列型でNULL値を持つため

SET @val=0;

のように数値型として使用する際は上記のように数値型としての初期化を行うこと
また、文字列型としてもNULL値のままだとconcat等で結合する際に不都合なので基本的に下記のように初期化して使うことになる。

SET @val='';

以下のSQLは、上記のように数値型で初期化されていないと、phpのように未定義変数に1を足して数値型に自動で変換されて1になったりはしない。

SELECT @val:=@val+1;
SELECT @val:

NULL

phpの場合は、自動的に数値型に初期化されて1が入る

<?php
$val=$val+1;
echo $val

1

ユーザ変数の実行の順番に注意。SQLのロジック順に駆動して行くので、例えばWHERE句→GROUP句→カラム のように実行され、基本的に左から右へ処理が流れる。特にカラムの並び順には注意が必要。
これを踏まえて、以下のようにWHERE句でユーザ変数を数値型に初期化することで、SET文で別途ユーザ変数を初期化する必要はなく、1クエリーでユーザ変数の初期化、使用が実行可能である。、ここでWHERE句は裏MySQL クエリー入門 基礎編2 (2) の解説のとおり、真(1)偽(0)判定を行っているので1+0=1で真になるように設定している。

SELECT @val:=@val+1 FROM DUAL WHERE 1+(@val:=0);

初期値を変えたいのであれば、同様に1+(5*0)=1で真になるように設定する

SELECT @val:=@val+1 FROM DUAL WHERE 1+(@val:=5)*0;

※ 7/2 追記 MySQLのマニュアルにあるとおり、1クエリーでユーザ変数の初期化をやると動作がおかしいようです。再度検証します。

※ 7/2 さらに追記 どうも1文中でユーザ変数の初期化は出来るもの、1文が完了するまで未定義型となるようでした。なので、先にSELECT文のWHERE句の中でユーザ変数を初期化、かつ条件が0になるようにして、行は選択させず、それをUNION ALLで本来のSQLと結合してを実行するようにしてみたところ1クエリーでユーザ変数の初期化から実行まで同時に実現することができました。つまり最初のSQL文はユーザ変数の初期化の為だけに*1実行させてます。あまり効率がよさそうではないので、どうしても1クエリーで実行したいときの参考にしてください。

  • 初期値0の場合
SELECT @val FROM DUAL WHERE @val:=0 
UNION ALL 
SELECT @val:=@val+1 
FROM  (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) t1
  • 初期値5の場合
SELECT @val FROM DUAL WHERE (@val:=5)*0 
UNION ALL 
SELECT @val:=@val+1 
FROM  (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) t1

*1:SELECT文を完了させユーザ変数の型を確定させる