【MySQL】ユーザ定義変数を使ったSQL

MySQLのユーザ定義変数の使い方をまとめたメモです。

ユーザ定義変数とは、

ユーザー定義変数に値を保存し、あとで参照することができます。これで 1 つのステートメントから次のステートメントに変数を移行させることができます。ユーザー定義変数は接続に固有のものです。. つまり、あるクライアントによって定義されたユーザー変数をほかのクライアントが表示したり、使用したりすることはできません。特定のクライアント接続で使用されているすべての変数は、クライアントが接続を切ったときに自動的に開放されます。
 
by http://download.nust.na/pub6/mysql/doc/refman/5.1-olh/ja/user-variables.html

つまり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       |
+----------+-----------+
上記のサンプルテーブルに対して以下のSQLを発行してみます。
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<)ノ



投稿日:2020-03-05    更新日:2020-03-07

[スポンサーリンク]

関連記事
勉強した内容を緩くメモする|JBの技術メモ
勉強した内容を緩くメモする|JBの技術メモ
勉強した内容を緩くメモする|JBの技術メモ
勉強した内容を緩くメモする|JBの技術メモ
勉強した内容を緩くメモする|JBの技術メモ
勉強した内容を緩くメモする|JBの技術メモ
サイト内検索
プロフィール

プロフィール

[Name : じゃぶじゃぶ(@jbjb_2019)]
都内で社内SEをしているおじさん。
仕事で得られる知識だけでは限界を感じ、 WEBの勉強がてらITブログを開始。
サーバからWEBサイトまでフルスクラッチで開発しました。
現在は勉強のモチベーションを保つために活用中。
興味があることを雑記的に書いていきます。

[スポンサーリンク]

[スポンサードリンク]