MySQL 8.0.31 がリリースされました。「リリースノートでわいわい言う勉強会」は1か月後くらいにやりたいと考えているので、それまでツイッターやブログでぜひ MySQL 8.0.31 を試して、ブログやツイートなどで気になる変更点を教えてください。
さて、さっそくですが私が気になったのは「新構文」。
"INTERSECT" と "EXCEPT" に対応したということで、軽く試してみました。
テーブルとデータの準備
flg1とflg2の２つのカラムを含むテーブルを作成し、データを投入します。
mysql> SELECT @@VERSION; +-----------+ | @@VERSION | +-----------+ | 8.0.31 | +-----------+ 1 row in set (0.00 sec)
mysql> CREATE TABLE t26 ( -> id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, -> name varchar(10), -> flg1 char(1), -> flg2 char(1)); Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO t26 (name, flg1, flg2) VALUES -> ('name01', 1, null), -> ('name02', 1, 0), -> ('name03', 1, 1), -> ('name04', 1, 1), -> ('name05', 0, 1), -> ('name06', 0, 1), -> ('name07', null, 0), -> ('name08', null, 1); Query OK, 8 rows affected (0.01 sec) Records: 8 Duplicates: 0 Warnings: 0
データが以下のように登録されました。ここから、flg1 が立っているものだけを抽出するSQL (SQL1) と flg2 が立っているものを抽出するSQL(SQL2)に対して、 INTERSECT, EXCEPT そして比較用に UNION類を試してみます。
mysql> SELECT * FROM t26; +----+--------+------+------+ | id | name | flg1 | flg2 | +----+--------+------+------+ | 1 | name01 | 1 | NULL | | 2 | name02 | 1 | 0 | | 3 | name03 | 1 | 1 | | 4 | name04 | 1 | 1 | | 5 | name05 | 0 | 1 | | 6 | name06 | 0 | 1 | | 7 | name07 | NULL | 0 | | 8 | name08 | NULL | 1 | +----+--------+------+------+ 8 rows in set (0.00 sec)
INTERSECT
SQL1 と SQL2 の INTERSECT すなわち共通部分を得る文です。
mysql> SELECT * FROM t26 WHERE flg1=1 -> INTERSECT -> SELECT * FROM t26 WHERE flg2=1; +----+--------+------+------+ | id | name | flg1 | flg2 | +----+--------+------+------+ | 3 | name03 | 1 | 1 | | 4 | name04 | 1 | 1 | +----+--------+------+------+ 2 rows in set (0.00 sec)
図にすると、こんな感じ。
EXCEPT
除外を指定する EXCEPT です。 SQL1 の結果から、SQL2の結果にも含まれるものを除外します。
mysql> SELECT * FROM t26 WHERE flg1=1 -> EXCEPT -> SELECT * FROM t26 WHERE flg2=1; +----+--------+------+------+ | id | name | flg1 | flg2 | +----+--------+------+------+ | 1 | name01 | 1 | NULL | | 2 | name02 | 1 | 0 | +----+--------+------+------+ 2 rows in set (0.00 sec)
UNION
２つのSQL文を「くっつける」指示としては UNION があります。昔からある命令ですが、参考までに実行結果を示しておきます。SQL1 にも SQL2 にも含まれない id=7 が存在していないことがわかりますね。
mysql> SELECT * FROM t26 WHERE flg1=1 -> UNION -> SELECT * FROM t26 WHERE flg2=1; +----+--------+------+------+ | id | name | flg1 | flg2 | +----+--------+------+------+ | 1 | name01 | 1 | NULL | | 2 | name02 | 1 | 0 | | 3 | name03 | 1 | 1 | | 4 | name04 | 1 | 1 | | 5 | name05 | 0 | 1 | | 6 | name06 | 0 | 1 | | 8 | name08 | NULL | 1 | +----+--------+------+------+ 7 rows in set (0.00 sec)
UNION ALL
こちらもおまけで「UNION ALL」を。昔からある構文です。
mysql> SELECT * FROM t26 WHERE flg1=1 -> UNION ALL -> SELECT * FROM t26 WHERE flg2=1; +----+--------+------+------+ | id | name | flg1 | flg2 | +----+--------+------+------+ | 1 | name01 | 1 | NULL | | 2 | name02 | 1 | 0 | | 3 | name03 | 1 | 1 | | 4 | name04 | 1 | 1 | | 3 | name03 | 1 | 1 | | 4 | name04 | 1 | 1 | | 5 | name05 | 0 | 1 | | 6 | name06 | 0 | 1 | | 8 | name08 | NULL | 1 | +----+--------+------+------+ 9 rows in set (0.00 sec)
旧バージョンのMySQLでは？
手元のものをみんな MySQL 8.0.31 に上げてしまい、今手元にある「少し古いバージョン」が MySQL 8.0.22 しかなかったので、こちらで念のため試してみました。
mysql> SELECT @@version; +-----------+ | @@version | +-----------+ | 8.0.22 | +-----------+
「そんな構文は知らんぞ」というエラーになります。
mysql> SELECT * FROM t26 WHERE flg1=1 -> INTERSECT -> SELECT * FROM t26 WHERE flg2=1; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTERSECT SELECT * FROM t26 WHERE flg2=1' at line 2
mysql> SELECT * FROM t26 WHERE flg1=1 -> EXCEPT -> SELECT * FROM t26 WHERE flg2=1; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'EXCEPT SELECT * FROM t26 WHERE flg2=1' at line 2
おわりに
新しいバージョンによって、速度が改善したり、安定性が増したり、色々進化するものですが、やっぱり一番ワクワクするのは新しい文が使えるようになった時ですよね（とウワモノ屋を自認する私は思うのです）。マイナーバージョンアップなのに新構文が増えるってどうなのよ、という向きもありますが、そういった所も含めてエキサイティングな MySQL 8.0 であると言えるかと思います(笑)。
追記1(2022/10/12)：
「flg1 と flg2 を見るだけなら、INTERSECTとかを使わずに普通にWHERE条件で絞ればいいじゃん」と言っている人がいましたが、そういう話じゃない(笑)。2つのクエリ結果に対する演算という部分がポイントです。
そんな人は、とりあえず以下のようにVIEWをつくって、2つのテーブルがあるように見れば理解しやすいでしょうか。
mysql> CREATE VIEW v26a AS SELECT * FROM t26 WHERE flg1=1; Query OK, 0 rows affected (0.01 sec)
mysql> CREATE VIEW v26b AS SELECT * FROM t26 WHERE flg2=1; Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM v26a; +----+--------+------+------+ | id | name | flg1 | flg2 | +----+--------+------+------+ | 1 | name01 | 1 | NULL | | 2 | name02 | 1 | 0 | | 3 | name03 | 1 | 1 | | 4 | name04 | 1 | 1 | +----+--------+------+------+ 4 rows in set (0.00 sec)
mysql> SELECT * FROM v26b; +----+--------+------+------+ | id | name | flg1 | flg2 | +----+--------+------+------+ | 3 | name03 | 1 | 1 | | 4 | name04 | 1 | 1 | | 5 | name05 | 0 | 1 | | 6 | name06 | 0 | 1 | | 8 | name08 | NULL | 1 | +----+--------+------+------+ 5 rows in set (0.00 sec)
追記2(2022/10/12)
テーブル内容まるごとなら、TABLE句を使うこともできる。
mysql> TABLE v26a INTERSECT TABLE v26b; +----+--------+------+------+ | id | name | flg1 | flg2 | +----+--------+------+------+ | 3 | name03 | 1 | 1 | | 4 | name04 | 1 | 1 | +----+--------+------+------+ 2 rows in set (0.00 sec)
追記3(2022/10/12)
気になるひとのために、EXPLAINの結果も載せておきます。SELECT で書いてみましたが、TABLE句を使っても同じ結果になります。ワーニング(note)が出ます。カラム展開した状態をおしえてくれる、ってことなのかな・・・
mysql> EXPLAIN SELECT * FROM v26a INTERSECT ALL SELECT * FROM v26b; +----+------------------+----------------+------------+------+---------------+------+---------+------+------+----------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+------------------+----------------+------------+------+---------------+------+---------+------+------+----------+-----------------+ | 1 | PRIMARY | t26 | NULL | ALL | NULL | NULL | NULL | NULL | 8 | 12.50 | Using where | | 2 | INTERSECT | t26 | NULL | ALL | NULL | NULL | NULL | NULL | 8 | 12.50 | Using where | | 3 | INTERSECT RESULT | <intersect1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+------------------+----------------+------------+------+---------------+------+---------+------+------+----------+-----------------+ 3 rows in set, 1 warning (0.00 sec)
mysql> show warnings; +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Note | 1003 | /* select#1 */ select `test`.`t26`.`id` AS `id`,`test`.`t26`.`name` AS `name`,`test`.`t26`.`flg1` AS `flg1`,`test`.`t26`.`flg2` AS `flg2` from `test`.`t26` where (`test`.`t26`.`flg1` = 1) intersect all /* select#2 */ select `test`.`t26`.`id` AS `id`,`test`.`t26`.`name` AS `name`,`test`.`t26`.`flg1` AS `flg1`,`test`.`t26`.`flg2` AS `flg2` from `test`.`t26` where (`test`.`t26`.`flg2` = 1) | +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
VIEWだからEXPLAINでワーニングが出る、というわけではなく、元のテーブルに対する連結でも同様のワーニングが出ます。（以下はUNIONでの例）
mysql> EXPLAIN SELECT * FROM t26 WHERE flg1=1 UNION SELECT * FROM t26 WHERE flg2=1; +----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+ | 1 | PRIMARY | t26 | NULL | ALL | NULL | NULL | NULL | NULL | 8 | 12.50 | Using where | | 2 | UNION | t26 | NULL | ALL | NULL | NULL | NULL | NULL | 8 | 12.50 | Using where | | 3 | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+ 3 rows in set, 1 warning (0.00 sec)
mysql> SHOW WARNINGS; +-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Note | 1003 | /* select#1 */ select `test`.`t26`.`id` AS `id`,`test`.`t26`.`name` AS `name`,`test`.`t26`.`flg1` AS `flg1`,`test`.`t26`.`flg2` AS `flg2` from `test`.`t26` where (`test`.`t26`.`flg1` = 1) union /* select#2 */ select `test`.`t26`.`id` AS `id`,`test`.`t26`.`name` AS `name`,`test`.`t26`.`flg1` AS `flg1`,`test`.`t26`.`flg2` AS `flg2` from `test`.`t26` where (`test`.`t26`.`flg2` = 1) | +-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
