MySQL 8.0.19。MySQL 8.0 の「メンテナンスリリース」です。
8.0.19より前のMySQLには、「標準SQLのひとつであるVALUES文が実装されていない」という重大な不具合が含まれていたため、バグ修正として本リリースに含まれたようです（真に受ける人がいると困るので、無粋ながら説明しておくと、これ、思いっきり「新機能」ですからね！ ）
まだあまりよくわかっていないのですが、個人的にはこれは、「テンポラリテーブルを作らなくても、複数の行のデータを作れる」というものなのかなと理解しています。いまのところ。
これまでは、クエリの中で何かの当て込み用に複数行のデータが欲しかった場合には
SELECT 13, "name1" UNION SELECT 17, "name2" UNION SELECT 19, "name3"
のようにする方法はありました。全然スマートじゃないので、あんまり使うもんじゃないなというのが私の感想（でも、ここぞという時に役に立ったテクニックではあります）。
VALUESは、 これを
VALUES ROW(13, "name1"), ROW(17, "name2"), ROW(19, "name3")
のように書けるもの。
ということで、以前、緯度や経度1度あたりの長さを求める日記を書いたのですが、それを書き直してみたいと思います。今回は1度ではなく1秒の距離を求めてみることにします。
経度1秒の長さ
SELECT ido, ST_Distance( ST_GeomFromText(CONCAT("POINT(", ido, " ", 135, ")"), 6668), ST_GeomFromText(CONCAT("POINT(", ido, " ", 135+(1/60/60), ")"), 6668) ) dist FROM (VALUES ROW(10),ROW(20),ROW(30),ROW(40),ROW(50),ROW(60),ROW(70),ROW(80),ROW(89),ROW(35),ROW(0)) as t(ido) ORDER BY ido;
結果：
+-----+--------------------+ | ido | dist | +-----+--------------------+ | 0 | 30.922293347363215 | | 10 | 30.45543707038192 | | 20 | 29.068816191795264 | | 30 | 26.801938223437794 | | 35 | 25.357920979131784 | | 40 | 23.720694278317538 | | 50 | 19.91554019501125 | | 60 | 15.500025396953356 | | 70 | 10.607016183528014 | | 80 | 5.387103340492138 | | 89 | 0.5478026839370822 | +-----+--------------------+ 11 rows in set (0.00 sec)
赤道付近で約30m、北緯35度付近で 25m強となっていることが見て取れます。
テキストエディタでINSERT文を作ったり、作業のためだけにテーブルを作ったりする方法よりもずっとスマートですね。 ST_GeomFromText()に渡すWKTを、CONCATで作っているところがちょっと格好悪いけど。
緯度1秒の長さ
同様に、緯度1秒の長さを求めてみます。
SELECT ido, ST_Distance( ST_GeomFromText(CONCAT("POINT(", ido, " 135)"), 6668), ST_GeomFromText(CONCAT("POINT(", ido+1/60/60, " 135)"), 6668) ) dist FROM (VALUES ROW(10),ROW(20),ROW(30),ROW(40),ROW(50),ROW(60),ROW(70),ROW(80),ROW(89),ROW(35),ROW(0)) as t(ido) ORDER BY ido;
+-----+--------------------+ | ido | dist | +-----+--------------------+ | 0 | 30.71497472832411 | | 10 | 30.724353692261502 | | 20 | 30.75135882757604 | | 30 | 30.792732916306704 | | 35 | 30.817301218397915 | | 40 | 30.843485630117357 | | 50 | 30.897495447658002 | | 60 | 30.948247983120382 | | 70 | 30.989621736109257 | | 80 | 31.016626415774226 | | 89 | 31.02591015584767 | +-----+--------------------+ 11 rows in set (0.00 sec)
以前の日記で書いたとおり、極付近に近づくほど、緯度1秒の長さが長くなっていることが見て取れます。
2桁の数字を作る
確かセルコさんあたりが本に書いていた気がするのですが、クエリの中で2桁の整数が欲しくなったときのちょっとしたテクニック。 0～9の数字10件だけを登録しておいたテーブルを使う例だったと記憶していますが、VALUES文を使うと、それを、テーブルを使わずにできるようになります。
WITH t AS ( SELECT * FROM (VALUES ROW(1),ROW(2),ROW(3),ROW(4),ROW(5),ROW(6),ROW(7),ROW(8),ROW(9),ROW(0)) AS t(n) ) SELECT CONCAT(t1.n, t2.n) num FROM t t1, t t2 ORDER BY num;
+------+ | num | +------+ | 00 | | 01 | | 02 | : : : | 98 | | 99 | +------+
今まで使ってきたSQLの感覚に馴染むように、上のように書いてみましたが、実は VALUES 文は「文」ですから、これ自体が値を返すのです。
たとえば、冒頭で UNION との比較で紹介した例の実行結果は、こう。
mysql> VALUES -> ROW(13, "name1"), -> ROW(17, "name2"), -> ROW(19, "name3") -> ; +----------+----------+ | column_0 | column_1 | +----------+----------+ | 13 | name1 | | 17 | name2 | | 19 | name3 | +----------+----------+ 3 rows in set (0.00 sec)
ということで、先ほどのCTEの中身から、余計な SELECT 文を取り去ってしまいましょう。ほら、こんなふうに書けるんです。
WITH t(n) AS ( VALUES ROW(1),ROW(2),ROW(3),ROW(4),ROW(5),ROW(6),ROW(7),ROW(8),ROW(9),ROW(0) ) SELECT CONCAT(t1.n, t2.n) num FROM t t1, t t2 ORDER BY num;
感想
「テンポラリのテーブルを作らずに行を生やす」ということに漠然と憧れがあったのですが、まさか本当に実装されるとは思いませんでした。そもそもこれが標準SQLだというのも知りませんでした。 ただ、これはうまく使わないと、クエリが見えにくくなってしまうかもしれないなぁ、とも感じました。いくらでも「わかりにくいクエリ」を作ることができそうです。
また、いちいち「ROW」と書かなきゃいけないのも、ちょっと面倒に感じました。
木村明治さんが2019年夏にRDBMSごとのSQLの比較を発表してくれていましたが、その資料の21ページ以降で VALUES 文が紹介されていました。 さすがです！！！ しかも、アイデア自体はMySQL発祥(マルチプルインサート時のVALUES）らしい。へぇぇ。
www.slideshare.net
