はじめに
みなさんはデバッグ用に大量のダミーデータが必要になった経験はないでしょうか。あるいは、歯抜けのあるデータを、漏れなく並べて表示したいときはないでしょうか。どちらも私はあります。
そんなとき、連続データを生成して利用してみたら便利だったため、今回はWITH RECURSIVEを使ったMySQLでの連続データ生成をテーマに本記事を書いてみました。
WITH RECURSIVEの基本の動き
公式のものを拝借して、WITH RECURSIVEのクエリとその結果について確認してみましょう。
WITH RECURSIVE cte AS
(
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM cte LIMIT 3
)
SELECT * FROM cte;
上記クエリの結果が以下です。
+------+
| n |
+------+
| 1 |
| 2 |
| 3 |
+------+
1から始まり、インクリメントされた結果が返されました。
WITH RECURSIVE内のクエリはどのように働いているのでしょうか。
図を用いて見ていきます。（あくまでイメージとして捉えてください）
Tips:LIMIT
MySQL 8.0.19より前のものは、LIMIT句の使用はできません。
LIMIT句を使用できない場合は、WHERE句を使用して以下のように記述します。
WITH RECURSIVE cte AS
(
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM cte WHERE n < 3
)
SELECT * FROM cte;
SELECTで使ってみる
日毎の集計データを表示する際に、WITH RECURSIVEを使用して連続する日付の仮想的な表を作成し、集計データと結合してみました。
要件にもよると思うのですが、集計数が0件のときはレコードを登録していないケースや、まだレコードのない未来日の分をリクエストされても表示をさせたいケースを想定しました。
使用するテーブル
DESC c_trainer_register;
+-------+--------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| ymd | date | NO | PRI | NULL | |
| count | bigint | NO | | NULL | |
+-------+--------+------+-----+---------+-------+
日毎のトレーナー登録数を集計するテーブルを作成してみました。
データは以下のように登録されているとします。
SELECT * FROM c_trainer_register;
+------------+-------+
| ymd | count |
+------------+-------+
| 2024-06-10 | 9 |
| 2024-06-11 | 9 |
| 2024-06-12 | 6 |
| 2024-06-13 | 3 |
| 2024-06-15 | 1 |
| 2024-06-16 | 2 |
+------------+-------+
6 rows in set (0.00 sec)
6/14はトレーナーの登録がなかったみたいです。
では、6/16までの1週間のトレーナー登録数を表示したいとします。
クエリ
WITH RECURSIVE days AS
(
SELECT
'2024-06-16' AS day
UNION ALL
SELECT
SUBDATE(day, INTERVAL 1 DAY) AS day
FROM
days
LIMIT
7
)
SELECT
days.day AS ymd,
COALESCE(data.count, 0) AS count
FROM
days
LEFT OUTER JOIN c_trainer_register AS data
ON days.day = data.ymd
ORDER BY
days.day ASC
;
結果
+------------+-------+
| ymd | count |
+------------+-------+
| 2024-06-10 | 9 |
| 2024-06-11 | 9 |
| 2024-06-12 | 6 |
| 2024-06-13 | 3 |
| 2024-06-14 | 0 |
| 2024-06-15 | 1 |
| 2024-06-16 | 2 |
+------------+-------+
7 rows in set (0.00 sec)
レコードのない6/14もちゃんと0件と表示されています。やったー！
INSERTで使ってみる
デバッグ用に大量のダミーデータの登録を依頼されたケースを想定しました。
BULK INSERTするにも億劫な大量のデータ作成も、WITH RECURSIVEを使うことで簡単に処理できてしまうかもしれません。
使用するテーブル
DESC u_trainer;
+--------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+------------+------+-----+---------+-------+
| trainer_id | bigint | NO | PRI | NULL | |
| trainer_name | varchar(6) | NO | | NULL | |
| created_at | datetime | NO | | NULL | |
| updated_at | datetime | NO | | NULL | |
+--------------+------------+------+-----+---------+-------+
このテーブルに、ダミーのトレーナーデータをつくりたいとします。
クエリ
INSERT INTO u_trainer
(
WITH RECURSIVE t AS
(
SELECT
1 AS trainer_id,
CAST('t_1' AS char(6)) AS trainer_name,
NOW() AS created_at,
NOW() AS updated_at
UNION ALL
SELECT
trainer_id + 1,
CONCAT('t_', trainer_id + 1),
NOW(),
NOW()
FROM
t
LIMIT
1000
)
SELECT * FROM t
);
結果
mysql> SELECT * FROM u_trainer;
+------------+--------------+---------------------+---------------------+
| trainer_id | trainer_name | created_at | updated_at |
+------------+--------------+---------------------+---------------------+
| 1 | t_1 | 2024-04-07 04:22:15 | 2024-04-07 04:22:15 |
| 2 | t_2 | 2024-04-07 04:22:15 | 2024-04-07 04:22:15 |
| 3 | t_3 | 2024-04-07 04:22:15 | 2024-04-07 04:22:15 |
| 4 | t_4 | 2024-04-07 04:22:15 | 2024-04-07 04:22:15 |
| 5 | t_5 | 2024-04-07 04:22:15 | 2024-04-07 04:22:15 |
〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜
〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜
| 996 | t_996 | 2024-04-07 04:22:15 | 2024-04-07 04:22:15 |
| 997 | t_997 | 2024-04-07 04:22:15 | 2024-04-07 04:22:15 |
| 998 | t_998 | 2024-04-07 04:22:15 | 2024-04-07 04:22:15 |
| 999 | t_999 | 2024-04-07 04:22:15 | 2024-04-07 04:22:15 |
| 1000 | t_1000 | 2024-04-07 04:22:15 | 2024-04-07 04:22:15 |
+------------+--------------+---------------------+---------------------+
1000 rows in set (0.00 sec)
1000件登録されています。やったー！
Tips:カラムのサイズ
非再帰的部分のCAST('t_1' AS char(6))という記述がありますね。
この記述がないとどうなるのでしょうか。
先ほどのクエリからCASTを外し、SELECT文に変え、LIMITも変更しました。
WITH RECURSIVE t AS
(
SELECT
1 AS trainer_id,
't_1' AS trainer_name,
NOW() AS created_at,
NOW() AS updated_at
UNION ALL
SELECT
trainer_id + 1,
CONCAT('t_', trainer_id + 1),
NOW(),
NOW()
FROM
t
LIMIT
10
)
SELECT * FROM t;
すると、以下のエラーが表示されます。 ※厳密なSQLモードのときに限ります
ERROR 1406 (22001): Data too long for column 'trainer_name' at row 1
trainer_nameのデータが長すぎるよと怒られました。
これは、trainer_nameのサイズが、非再帰的部分で記述された値の長さで設定されてしまうからなんですね。
そのため、先ほどのクエリだと、trainer_nameがt_9までならOKだけど、t_10以上は長さが足りていませんよと怒られてしまうのです。
INSERT先のテーブル定義では、trainer_nameの型はvarchar(6)ですから、t_9999までは許容としたいですよね（といいつつ、後述する再帰処理の上限の関係で初回は1000件までにしていますが…）。
そのため、非再帰的部分でCASTによる型変換を行うことによって、型を明示的に示しています。
Tips:再帰処理の上限
今回の例ではきりよくLIMIT 1000としましたが、せっかくならvarchar(6)の限界(t_9999)に挑みたいですよね！
ということで、LIMITの値を9999に変更してみました。
INSERT INTO u_trainer
(
WITH RECURSIVE t AS
(
SELECT
1 AS trainer_id,
CAST('t_1' AS char(6)) AS trainer_name,
NOW() AS created_at,
NOW() AS updated_at
UNION ALL
SELECT
trainer_id + 1,
CONCAT('t_', trainer_id + 1),
NOW(),
NOW()
FROM
t
LIMIT
9999
)
SELECT * FROM t
);
すると、以下のエラーが表示されます。
ERROR 3636 (HY000): Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.
CTEの再帰レベル数の上限値にひっかかっているようです。再帰レベル数の上限値のデフォルトは1001のようですね。
これを変更したい場合は、SET SESSION cte_max_recursion_depth = xxx;を用いて変更することができます。
mysql> SET SESSION cte_max_recursion_depth = 9999;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO u_trainer
-> (
-> WITH RECURSIVE t AS
-> (
-> SELECT
-> 1 AS trainer_id,
-> CAST('t_1' AS char(6)) AS trainer_name,
-> NOW() AS created_at,
-> NOW() AS updated_at
-> UNION ALL
-> SELECT
-> trainer_id + 1,
-> CONCAT('t_', trainer_id + 1),
-> NOW(),
-> NOW()
-> FROM
-> t
-> LIMIT
-> 9999
-> )
-> SELECT * FROM t
-> );
Query OK, 9999 rows affected (0.07 sec)
Records: 9999 Duplicates: 0 Warnings: 0
9999件登録できました。やったー！
おわりに
WITH RECURSIVEを使用することで、連続データを生成することができました。これを利用すると、なにかと捗る機会は少なくないのではないかと思います。
今回はSELECT文、INSERT文で例を用いて触れてみましたが、他にも「こうすると便利だったよ」という例があれば教えていただけると嬉しいです。