MySQL8.0の機能を調べてまとめている。 Derived Table, Lateral Derived Tableについて読んだまとめ。
DERIVED TABLE
LATERAL DERIVED TABLESや8.0の新機能を説明する前に、DERIVED TABLEについて説明する
DERIVED TABLEとはFROM句のscope内で作るテーブルのことで、例えばFROM句に書いたサブクエリのSELECT等がある。 構文としては以下で、
SELECT ... FROM (subquery) [AS] tbl_name [(col_list)] ...
JSON_TABLE()関数によってJSONカラムからderived tableを作ることもできる
SELECT * FROM JSON_TABLE(arg_list) [AS] tbl_name ...
このとき、
[AS] tbl_nameは必須- derived tableの各カラムはユニークな名前
- tbl_nameに続いてカラム名を指定する場合はtable内のカラム数と指定したカラム名の数が一致する必要がある
準備
create table t3 ( id int not null auto_increment, c1 int, c2 int, primary key (id)); create table t4 ( id int not null auto_increment, c1 int, c2 int, primary key (id)); insert into t3 (c1, c2) values (1, 10), (1, 20), (2, 30), (2, 40), (3, 50), (3, 60); insert into t4 (c1, c2) values (1, 100), (2, 200), (3, 300), (4, 400), (5, 500), (6,600); -- t3 mysql> select * from t3; +----+------+------+ | id | c1 | c2 | +----+------+------+ | 1 | 1 | 10 | | 2 | 1 | 20 | | 3 | 2 | 30 | | 4 | 2 | 40 | | 5 | 3 | 50 | | 6 | 3 | 60 | +----+------+------+ 6 rows in set (0.00 sec)
derived tableの使い所としては、例えば上記のt3テーブルのc1の合計の平均を求めたい場合
select avg(sum(c1)) from t3 group by c1;とすることはできないがsum()するまでをderived tableにすることで外側のクエリで平均を求めることができる。
-- group by下だけの結果 mysql> select sum(c1) from t3 group by c1; +---------+ | sum(c1) | +---------+ | 2 | | 4 | | 6 | +---------+ 3 rows in set (0.01 sec) -- sum()とave()は同時に指定できない mysql> select avg(sum(c1)) from t3 group by c1; ERROR 1111 (HY000): Invalid use of group function -- derived tableを使った方法 mysql> select avg(sum_col) from (select sum(c1) as sum_col from t3 group by c1) as t1; +--------------+ | avg(sum_col) | +--------------+ | 4.0000 | +--------------+ 1 row in set (0.01 sec)
derived tablesには以下の制約がある
- 相関サブクエリにできない
- 同じSELECT内で他のテーブルを参照することはできない
8.0.14より前ではderived tableは外側のデータを参照することができなかった。 8.0.14ではこれができる。
例えば、上記で作成したtable t3,t4で以下は8.0.14以降で実行できる
select * from t4 where t4.c1 < ( select avg(dt.a) from ( select sum(t3.c1) as a from t3 where t4.c1 = t3.c1 group by t3.c1 ) dt );
実行結果: 5.7.25
mysql> select * from t4 where t4.c1 < ( -> select avg(dt.a) from ( -> select sum(t3.c1) as a from t3 where t4.c1 = t3.c1 group by t3.c1 -> ) dt -> ); ERROR 1054 (42S22): Unknown column 't4.c1' in 'where clause'
実行結果: 8.0.15
mysql> select * from t4 where t4.c1 < ( -> select avg(dt.a) from ( -> select sum(t3.c1) as a from t3 where t4.c1 = t3.c1 group by t3.c1 -> ) dt -> ); +----+------+------+ | id | c1 | c2 | +----+------+------+ | 1 | 1 | 100 | | 2 | 2 | 200 | | 3 | 3 | 300 | +----+------+------+ 3 rows in set (0.00 sec)
8.0.15でのexplain結果
mysql> explain select * from t4 where t4.c1 < ( select avg(dt.a) from ( select sum(t3.c1) as a from t3 where t4.c1 = t3.c1 group by t3.c1 ) dt ); +----+--------------------+------------+------------+------+---------------+------+---------+------+------+----------+------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+------------+------------+------+---------------+------+---------+------+------+----------+------------------------------+ | 1 | PRIMARY | t4 | NULL | ALL | NULL | NULL | NULL | NULL | 6 | 100.00 | Using where | | 2 | DEPENDENT SUBQUERY | <derived3> | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | NULL | | 3 | DEPENDENT DERIVED | t3 | NULL | ALL | NULL | NULL | NULL | NULL | 6 | 16.67 | Using where; Using temporary | +----+--------------------+------------+------------+------+---------------+------+---------+------+------+----------+------------------------------+ 3 rows in set, 2 warnings (0.00 sec)
EXPLAIN SELECTによるデータ変更の注意
Derived table内でデータの変更を行うstored functionを書いていて、外側のクエリでテーブルへのアクセスをする場合、EXPLAINによって、stored functionが呼ばれ、EXPLAIN SELECTであってもデータに変更が起こる。
例えば以下のようにしてt5テーブルとstored functionを作ったとする
create table t5 ( id int not null auto_increment, c1 int, primary key (id)); delimiter // CREATE FUNCTION f1(p1 INT) RETURNS INT BEGIN INSERT INTO t5 (c1) VALUES (p1); RETURN p1; END// delimiter ;
ここで、
- 単体でexplain selectでf1をselectする
- 外側のクエリでテーブルにアクセスしないようにし、derived tableでf1をselectする
- 外側のクエリでテーブルにアクセスしつつ、derived tableでf1をselectする
とすると3の場合はf1が実行され、EXPLAIN SELECTを実行したつもりが、データが変更されてしまう.
ただ、デフォルトだとドキュメントのサンプルのストアドファンクションは定義できない。。。
mysql> delimiter // mysql> CREATE FUNCTION f1(p1 INT) RETURNS INT -> BEGIN -> INSERT INTO t5 (c1) VALUES (p1); -> RETURN p1; -> END// ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable) mysql> delimiter ;
面倒なので、実験はパス
- 単体でexplain selectでf1をselectする
EXPLAIN SELECT f1(5); -- f1によってt５テーブルのデータは変更されない
- 外側のクエリでテーブルにアクセスしないようにし、derived tableでf1をselectする
EXPLAIN SELECT NOW() AS a1, (SELECT f1(5)) AS a2; -- f1によってt５テーブルのデータは変更されない
- 外側のクエリでテーブルにアクセスしつつ、derived tableでf1をselectする
EXPLAIN SELECT * FROM t1 AS a1, (SELECT f1(5)) AS a2; -- f1によってt５テーブルのデータは変更される!!
LATERAL DERIVED TABLE
derived tableでは同じFROM句内で他のテーブルのカラムを参照することはできない。
MySQL 8.0.14以降ではLATERAL句を使うことで、これが可能になる。
syntaxはderived tableのsyntaxの前にLATERALがつくだけで後は同じ。
Lateral derived tableには以下の制約がある
- lateral derived tableはFROM句内でだけ使える
- joinの右/左にある場合はleft outer/right (outer) joinかjoin, inner join, cross joinを指定しないといけない
- SQL標準に従って、table関数は暗黙にLATERAL句を持つ
- SQL標準に従ってJSON_TABLE()のまえにはLATERALはかけない
例
mysql> select * from (select * from t4) as a, (select * from t3 where t3.c1 > a.c1) as b; ERROR 1054 (42S22): Unknown column 'a.c1' in 'where clause'
mysql> select * from (select * from t4) as a, LATERAL (select * from t3 where t3.c1 > a.c1) as b; +----+------+------+----+------+------+ | id | c1 | c2 | id | c1 | c2 | +----+------+------+----+------+------+ | 1 | 1 | 100 | 3 | 2 | 30 | | 1 | 1 | 100 | 4 | 2 | 40 | | 1 | 1 | 100 | 5 | 3 | 50 | | 2 | 2 | 200 | 5 | 3 | 50 | | 1 | 1 | 100 | 6 | 3 | 60 | | 2 | 2 | 200 | 6 | 3 | 60 | +----+------+------+----+------+------+ 6 rows in set (0.00 sec)
