この記事はスタンバイ Advent Calendar 2021の1日目の記事です。
MySQLで変数を使用して便利だと感じた機能を書きます。
TL;DR
- MySQL上で変数を使用できる
- selectの結果を変数に使用できる
MySQL上の変数とは
MySQLをコンソールから操作をする際、MySQL変数を使用して、決まった作業、複雑なデータのとり方を簡単に行う事ができるようになります。
特にMySQLはWith句を使用できないので、簡易なものは変数で代替することも可能であります。
MｙSQL変数で出来ること・出来ないこと
出来ること
- 変数に任意の文字列、数字を格納することが出来る
- select文の結果を使って変数を格納することが出来る
出来ないこと
- DBセッションをまたいだ変数は宣言できない
- テーブルデータを配列的に持つことは出来ない
変数の使い方
最も簡単な変数宣言方法、呼び出し方です。
### 変数の設定
mysql> set @user = "scott";
### 変数の使い方
mysql> select @user;
+-------+
| @user |
+-------+
| scott |
+-------+
設定した変数は検索キーワードにも使用できます。
mysql> select * from user where name = @user;
+-------+----------+
| name | password |
+-------+----------+
| scott | tiger |
+-------+----------+
select 文で変数を定義する
返り値が1個の場合に限り、select文の返り値を変数に指定することができます。宣言方法は2通りありますが、どちらでも同じ宣言が出来ます。
### select文で変数の宣言。into @[変数名]で変数定義できます
mysql> select password into @password from user where name = @user;
（or)
mysql> select @password := password from user where name = @user;
mysql> select @password;
+-----------+
| @password |
+-----------+
| tiger |
+-----------+
日時の取り扱い（15分前、1日前、1ヶ月前など）にも便利です。時系列の履歴データを扱うときには必須と言ってもいいかもしれません。
### 15分前、1日前、1ヶ月前を変数に追加
mysql> select
@now := now() ,
@15min_ago := now() - interval 15 minute,
@1day_age := now() - interval 1 day,
@1month_ago := now() - interval 1 month;
###参照する
mysql> select @15min_ago, @1day_ago, @1month_ago;
+---------------------+---------------------+---------------------+
| @15min_ago | @1day_ago | @1month_ago |
+---------------------+---------------------+---------------------+
| 2021-11-28 00:43:22 | 2021-11-27 00:58:22 | 2021-10-28 00:58:22 |
+---------------------+---------------------+---------------------+
上記で宣言した変数を使用することで、「現在から1日前までに修正されたレコード一覧。複数のテーブルから取得する」みたいなことが楽にできるようになります。
mysql> select * from user_data
where updated_at between @now and @1day_ago;
mysql> select * from user_history
where updated_at between @now and @1day_ago;
mysql> select * from event_history
where updated_at between @now and @1day_ago;
感想
- 変数を使用することで実行するSQLが簡単になるので、ミスの要素が少なくなる
- 定型的な手順を作成しやすくなる
- procedureや他の言語に置き換えがやりやすくなる