MySQL8.0のSQLの新機能について調べたこと

MySQL8.0 リリース記念3部作の執筆時に調べたことのまとめ(後日談)です。

MySQL8.0のWindow関数のサンプル集
https://codezine.jp/article/detail/2678

MySQL8.0の再帰With句のサンプル集
https://codezine.jp/article/detail/2679

MySQL8.0で『SQLパズル』の問題を解く
https://codezine.jp/article/detail/2680

#メモ01 DML文のOrderBy句でのWindow関数は使用不可

Delete from テーブル order by dense_rank() over(order by ソートキー)
limit 1;

みたいなDMLは文法エラーになりました。

余談ですが、Oracle12cだと

select * from テーブル
order by dense_rank() over(partition by PID order by ソートキー)
limit 1 with ties;

なんてSQLが動いたりします。いつか使いたい

#メモ02 再帰With句の再帰項のSelect句でのWindow関数
OracleとPostgreSQLでは、使用できるが
MySQL8.0.15では文法エラー

-- Oracle18cでは動作します
with rec(Val,NodeCnt) as(
select 1,1 from dual
union all
select 1,NodeCnt + count(*) over()
  from rec
 where NodeCnt <= 5)
select * from rec;

MySQL 8.0.19からは、
Limit句を使って探索ノード数を制限可能になったようです。

A new, simple way to figure out why your recursive CTE is running away
https://mysqlserverteam.com/a-new-simple-way-to-figure-out-why-your-recursive-cte-is-running-away/

#メモ03 PostgreSQLのマニュアルにある、Limit句を使った非公式な枝切り
https://www.postgresql.jp/document/9.0/html/index.html
は、MySQL8.0で使用できず(無限ループしました)

-- PostgreSQL8.4では動作します
WITH RECURSIVE t(n) AS (
    SELECT 1
  UNION ALL
    SELECT n+1 FROM t
)
SELECT n FROM t LIMIT 100;

#メモ04 MySQLの再帰With句でズンドコキヨシ

with recursive rec(LV,ZunDokoStr) as(
select 0,Cast('' as Char(4000))
union all
select LV+1,concat(ZunDokoStr,if(RAND() > 0.5 ,'ズン！','ドコ！'))
  from rec
 where LV = 0 or ZunDokoStr Not Like '%ズン！ズン！ズン！ズン！ドコ！')
select concat(ZunDokoStr,'キ・ヨ・シ！') as "ズンドコキヨシ"
  from rec order by LV desc Limit 1;

ズンドコキヨシ
------------------------------------------------
ドコ！ズン！ズン！ズン！ズン！ドコ！キ・ヨ・シ！

#メモ05 SQLパズル56 ホテルの部屋番号
MySQLには、OracleのRowIDや、PostgreSQLのctidのようなものはない。

MySQLでは、Update文のCurrent Ofは使用不可
https://dev.mysql.com/doc/refman/5.6/ja/cursor-restrictions.html

UPDATE WHERE CURRENT OF および DELETE WHERE CURRENT OF は、
更新可能なカーソルがサポートされていないため実装されません。

なので、alter tableで主キーでも付けないと無理ということで
alter tableでオートインクリメントな主キーを追加し、
https://bugs.mysql.com/bug.php?id=88397
のGuilhem BichotさんのUpdate文を、Using構文を使ったJoinに変更したものを解としました。