MySQLで今月の日付一覧を得る with 再帰CTE

sakaikさんのブログで今月の日付一覧を得るクエリを読んでいて、ここで紹介されているクエリが手元で実行できなかったので、メモ。

sakaik.hateblo.jp

原因は手元の環境が8.0.17でVALUES()関数がなかったことが原因。

よく見るとvalues()関数でテーブルを作って、それを自己結合しつつ出力を作ってソートしている。
再帰CTEでシーケンスが作れるので、それを使っても良いかもなと思い、復習がてら書いてみました。

with recursive rec(V, D) as (
  select 1, cast(date_format(@dt, '%Y-%m-01') as date)
  union all
  select V+1, date_add(D, interval +1 day)
  from rec
  where D < last_day(@dt)
)
select D as `DATE` from rec;

実行するとこう

mysql> set @dt = '2020-04-28';
Query OK, 0 rows affected (0.01 sec)

mysql> with recursive rec(V, D) as (
    ->   select 1, cast(date_format(@dt, '%Y-%m-01') as date)
    ->   union all
    ->   select V+1, date_add(D, interval +1 day)
    ->   from rec
    ->   where D < last_day(@dt)
    -> )
    -> select D as `DATE` from rec;
+------------+
| DATE       |
+------------+
| 2020-04-01 |
| 2020-04-02 |
| 2020-04-03 |
| 2020-04-04 |
| 2020-04-05 |
| 2020-04-06 |
| 2020-04-07 |
| 2020-04-08 |
| 2020-04-09 |
| 2020-04-10 |
| 2020-04-11 |
| 2020-04-12 |
| 2020-04-13 |
| 2020-04-14 |
| 2020-04-15 |
| 2020-04-16 |
| 2020-04-17 |
| 2020-04-18 |
| 2020-04-19 |
| 2020-04-20 |
| 2020-04-21 |
| 2020-04-22 |
| 2020-04-23 |
| 2020-04-24 |
| 2020-04-25 |
| 2020-04-26 |
| 2020-04-27 |
| 2020-04-28 |
| 2020-04-29 |
| 2020-04-30 |
+------------+
30 rows in set (0.00 sec)

曜日も含めて(dayofweek())出すとこう
1:日曜 ~ 7: 土曜

with recursive rec(V, D) as (
  select 1, cast(date_format(@dt, '%Y-%m-01') as date)
  union all
  select V+1, date_add(D, interval +1 day)
  from rec
  where D < last_day(@dt)
)
select D as `DATE`, dayofweek(D) as DAY_OF_WEEK from rec;

再帰CTE, 最初のセットで型が決まったり、with句内の終了条件(where句)は中間結果に対して書かないと行けなかったりでちょっと注意して書かないといけないなと思いました。