SQLを書いていると、ちょっとしたテストデータを用意したいときや、固定の値を一時的にテーブルのように扱いたい場面があると思います。これまではUNION ALLを束ねて仮想的なテーブルを作るのが定番でしたが、MySQL 8.
今回は、そんなVALUES行コンストラクタについて解説をしていきます。
検証環境
今回はdockerで建てたMySQLを使用します。ベースイメージとして8.
% docker run --platform linux/x86_64 -p 127.0.0.1:3307:3306 -e MYSQL_ROOT_PASSWORD=my-secret-pw -e MYSQL_USER=kk2170 -e MYSQL_PASSWORD=my-secret-pw -d mysql:8.4
mysqlクライアントでアクセスが可能であることを確認しましょう。方法は以下になります。
% mysql -uroot -pmy-secret-pw -h127.0.0.1 -P3307
執筆時点では、以下の通りMySQL 8.
mysql> select version(); +-----------+ | version() | +-----------+ | 8.4.5 | +-----------+ 1 row in set (0.00 sec)
テーブル値コンストラクタを使ってみる
MySQL 8.
従来であれば固定データを扱う場合、SELECT ... UNION ALL SELECT ... を延々と書き連ねて擬似テーブルを作ったり、一時的にテーブルを作成して使用する方法がありましたが、小回りが利かなく、ちょっと利用したいときには面倒でした。そこで登場するのがテーブル値コンストラクタです。これは
具体的なイメージとしては、SQL中に小さな一時テーブルを用意する感覚で使えます。たとえば、別のDBから抜いてきたデータとJOINがしたい場合などに、持ってきたCSVの値をテキスト変換して、テーブル値コンストラクタを使用すると、スキーマの設計に影響を与えることなく簡単に使用することができます。
まずは試してみましょう。一番簡単にテーブルを作ってみます。以下のような簡単なクエリでテーブル形式のデータを作成できます。
VALUES ROW(1, 'kimura'), ROW(2, 'koichiro');
結果は以下のようになります。
+----------+----------+ | column_0 | column_1 | +----------+----------+ | 1 | kimura | | 2 | koichiro | +----------+----------+
テーブルが作成されていることがわかります。ただ、これを活用する際には注意が必要で、試しにWHERE句を付けて'kimura'だけを取得してみようと思います。
mysql> SELECT * FROM (VALUES ROW(1, 'kimura'), ROW(2, 'koichiro')) WHERE column_1 = 'kimura'; ERROR 1248 (42000): Every derived table must have its own alias
このように、エイリアスを付けないとエラーになってしまいます。なので、使用する際にはサブクエリを使用する時のようにエイリアスを付けてあげましょう。
mysql> SELECT * FROM ( VALUES ROW(1, 'kimura'), ROW(2, 'koichiro')) AS t(id, name) WHERE name = 'kimura';
結果は以下の通りです。
+----+--------+ | id | name | +----+--------+ | 1 | kimura | +----+--------+ 1 row in set (0.02 sec)
ということで、idとnameをカラムに持つテーブルが用意できていることがわかります。
FROM句の値はVALUESから始まるので、この機能を知らないと驚くことになるかもしれません。また、MySQL以外のRDBMSの場合はROWが存在しなくても大丈夫な場合もありますが、MySQLでは必須なので、他のRDBMSと兼用している場合には注意しましょう。
この仕組みは単独でデータを表示するだけではなく、既存のテーブルと結合させて活用することができます。最近ではマイクロサービス化が進みデータベースが分割されていることもあると思います。そんな時にデーターベース間を飛び越えて一部の値でJOINしたい場合などは、SQL上に値をサクッとかけるので非常に便利です。データをある程度埋め込むことも可能なので、チーム内でクエリを共有する際にも使いやすいです。
何より、各行の先頭に ROW( を、行末に ), を用意してあげるだけで、VALUESをCSVから簡単に変換できるという点もかなり便利で、手元にあるCSVとデータベース上の値でJOINをしたい場合に、従来であればINSERTやLOAD DATA INFILEをしたりしないといけなかったものを、クエリ上に乗せて簡単にJOINできるのが便利です。
また、行コンストラクタで作成されたものはテーブルとして扱えるため、実際にテーブルにINSERTする際にもWHERE句で絞り込みができるので、作成したテーブルを実際のテーブルに挿入したい場合も簡単にフィルタリングできます。
mysql> create database test; mysql> use test mysql> CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(50) );
以上のようなテーブルにINSERT SELECT構文を活用してkimuraという名前のユーザだけ挿入してみます。
mysql> INSERT INTO users (id, name) SELECT id, name FROM (VALUES ROW(1, 'kimura'), ROW(2, 'koichiro')) AS t(id, name) WHER E name = 'kimura'; mysql> SELECT * FROM users; +----+--------+ | id | name | +----+--------+ | 1 | kimura | +----+--------+ 1 row in set (0.00 sec)
このように、kimuraだけ挿入できていることがわかります。バルクインサートの形にしてしまうとフィルタリングなどがしづらかったですが、この形式にしておくと簡単にフィルタリングが可能です。
まとめ
いかがでしたでしょうか、今回は知ってるとちょっと便利なVALUES行コンストラクタについて解説しました。今までこういったデータを一時的に、またMySQLへ埋め込む形で作成することは非常に難しかったのですが、サクッと利用できるようになりました。
手元にある値をテーブルとして扱いたいユースケースはさまざまだと思いますが、かなり便利な構文であることは間違いありません。MySQL 8.