【MySQL】ユーザ定義変数を使ったSQL
-
カテゴリ：
- DB
-
タグ：
- #MySQL
MySQLのユーザ定義変数の使い方をまとめたメモです。
ユーザ定義変数とは、
つまりSQLの中で登場したカラムの値や集計値を変数に一時的に保存し、再利用できる機能です。
ユーザ定義変数は同じ接続内のみ使用可能で、接続を切ると保存していた値は削除されます。また別クライアントからでは参照できません。
サンプル
ユーザ定義変数の使い方
簡単にユーザ定義変数を使い方を見ていきます。
SET @[変数名] = 代入する値;上記のようにSET句を使って値を代入することができます。
mysql> SET @name=taro;
mysql> SET @age=19;
mysql> SELECT @name, @age;
+----------+-----------+
| @name | @age |
+----------+-----------+
| taro | 19 |
+----------+-----------+SETしたユーザ定義変数の値（@name、@age）をSELECT文で呼び出せているのが分かると思います。
SELECT文にユーザ定義変数の組み込む
次に、ユーザ定義変数をSELECT文の中で生成して利用することもできます。
SELECT
dummy.*
, ( @col:=@val1+1 ) AS col1
, ( @col:=@val1+@val2) AS col2
FROM
( SELECT @val1:=1, @val2:=10 ) dummy;
+----------+------------+----------+-----------+
| @val1:=1 | @val2:=1 | col1 | col2 |
+----------+------------+----------+-----------+
| 1 | 10 | 2 | 11 |
+----------+------------+----------+-----------+From句内のサブクエリでユーザ定義変数の生成を行い、「dummy」という名前を付けています。
SELECT @[変数名] := 代入する値, ...;※SELECT文の中でユーザ定義変数を定義する際は「=」ではなく「:=」を使うことに注意してください。
続いてメインクエリのSELECT句の中で生成したユーザ定義変数をさらに加工して出力しています。
col1には「@val1(1) + 1 = 2」で、col2は「@val1(1) + @val2(10) = 11」といった感じです。
クエリが処理される順番に注意
少しややこしい話になりますが、ユーザ定義変数を使う際はクエリがどの順番で処理されているか意識して作成する必要があります。
クエリが発行されたタイミングでSQLを評価するのですが、評価する順番が決まっています。
■クエリが評価される順番
FROM句
-> ON句
-> JOIN句
-> WHERE句
-> GROUP BY句
-> HAVING句
-> SELECT句(カラムの左から右へ順番に処理)
-> ORDER BY句
-> LIMIT句
上記の順番でSQL文が評価され結果の表を出力しています。これを念頭にユーザ定義変数でいろいろ検証してみました。
サンプルデータとして以下のテーブルを用意します。
TABLE1
+----------+-----------+
| id | name |
+----------+-----------+
| 1 | ichiro |
+----------+-----------+
| 2 | jiro |
+----------+-----------+
| 3 | taro |
+----------+-----------+
| 4 | hanako |
+----------+-----------+
| 5 | tom |
+----------+-----------+SELECT
dummy.*
, TABLE1.*
, @val
, ( @val:=@val+1 ) AS col1
, ( @val:=@val+@val) AS col2
, ( @val:=@val+@const) AS col3
, ( @val:=@val+term_id) AS col4
FROM
( SELECT @val:=0, @const:=1 ) dummy
, TABLE1;
+----------+-----------+----------+-----------+-----------+----------+-----------+----------+-----------+
| @row1:=0 | @const:=1 | id | name | @val | col1 | col2 | col3 | col4 |
+----------+-----------+----------+-----------+-----------+----------+-----------+----------+-----------+
| 0 | 1 | 1 | ichiro | 0 | 1 | 2 | 3 | 4 |
+----------+-----------+----------+-----------+-----------+----------+-----------+----------+-----------+
| 0 | 1 | 2 | jiro | 4 | 5 | 10 | 11 | 13 |
+----------+-----------+----------+-----------+-----------+----------+-----------+----------+-----------+
| 0 | 1 | 3 | taro | 13 | 14 | 28 | 29 | 32 |
+----------+-----------+----------+-----------+-----------+----------+-----------+----------+-----------+
| 0 | 1 | 4 | hanako | 32 | 33 | 66 | 67 | 71 |
+----------+-----------+----------+-----------+-----------+----------+-----------+----------+-----------+
| 0 | 1 | 5 | tom | 71 | 72 | 144 | 145 | 150 |
+----------+-----------+----------+-----------+-----------+----------+-----------+----------+-----------+先ほどのサンプル同様、ユーザ定義変数を初期化し「dummy」として命名しています。そこにサンプルテーブルの「TABLE1」を結合している表になります。
まずSELECT文の処理順はFROM句からなので、ユーザ定義変数の「dummy」とテーブル「TABLE1」が読み込まれます。
今回はON句からHAVING句までが無いので、次に処理されるのがSELECT句になります。SELECT句の「dummy.*」はFrom句で定義したユーザ定義変数がそのまま入るので「0」と「1」が全レコードに対して出力されます。
「TALBE1.*」ではテーブル「TABLE1」の内容がそのまま出るので、5つある全レコードが出力されています。
次に「col1」から「col4」のカラムですが、それぞれ以下のような計算で出力されます。
col1 => @val(初期値0) + 1
col2 => @val + @val
col3 => @val + @const(1)
col4 => @val + [TABLE1のid]
注意点として、各カラムの中で「@val」値を上書いているのでどんどん値が積みあがって大きくなっていきます。
[クエリが処理される順番]の所でも話しましたが、SELECT句はカラムの左から右へ順番に処理されるので、col1カラムで代入された@valを引き継いでcol2カラムを計算され、「col2 = (@val+1) + (@val+1)」という処理がされることになります。
さらに1つのSQLの中で「@val」は共通なので、1レコード目のcol4カラムで代入された「@val」を2レコード目のcol1カラムで使われます。
以上です。（＞o＜）ノ
