この記事は ミライトデザイン Advent Calendar 2022 の3日目の記事です。
今年も懲りずに乱入させていただきました。
昨日は@tkek321 さんのS3 で静的ページを公開する記事でしたね。
ちょっとしたページを公開したいけどWebサーバ作るのはちょっと、、、というときに重宝しそうですね。
本日は MySQL に関するちょっとした小ネタをご紹介したいと思います。
やりたいこと
MySQL上でいい感じのクエリを実行して下記のような連番を取得したいです。
テーブルやビューなどは作成せず、Nを自由に変更できるのが理想です。
mysql> SELECT ???
+-------+
| value |
+-------+
| 1 |
| 2 |
| ... |
| N |
+-------+
見た目は簡単そうですが、知らないと意外と手こずるかもしれません（自分は以前めっちゃ時間を溶かしました(T_T)）。
環境
mysql --version
mysql Ver 8.0.31 for Linux on x86_64 (MySQL Community Server - GPL)
1. UNION ALLを使用する
おそらく一番シンプルな方法です。
mysql> SELECT 1 AS value UNION ALL SELECT 2 UNION ALL SELECT 3;
+-------+
| value |
+-------+
| 1 |
| 2 |
| 3 |
+-------+
3 rows in set (0.01 sec)
nが小さく、かつ変更が不要であればこれでも十分かもしれません。
しかしnが大きかったり値を変更したくなった場合は、腱鞘炎になるリスクを負う必要があります。
なお、UNION ALLのほうがUNIONに比べて重複削除の処理がない分高速なので採用していますが、nが大きくないのであればどちらでも大丈夫です。
参考：UNION とUNION ALLの違い - Qiita
2. VALUES ROW()を使用する
MySQL 8.0.19 以降であれば、VALUES ROW()を使用するとUNION ALLと同様の結果を取得できます。
mysql> VALUES ROW(1), ROW(2), ROW(3);
+----------+
| column_0 |
+----------+
| 1 |
| 2 |
| 3 |
+----------+
3 rows in set (0.00 sec)
UNION ALLと基本的には同じような内容です。
ですがUNION ALLに比べて文字数が少ないので、腱鞘炎のリスクが低減されています。
今回の連番取得の目的ではそこまで相性が良くなさそうですが、VALUES ROW()は数字に限らず使用できるので、覚えておくと使い所があるかもしれません。
3. Recursive Common Table Expressions（再帰的な共通テーブル式）を使う
MySQL 8.0.1 以降であれば、再帰的な共通テーブル式を使用して連番を取得することも可能です。
mysql> WITH RECURSIVE seq AS (SELECT 1 AS value UNION ALL SELECT value + 1 FROM seq WHERE value < 3)
SELECT * FROM seq;
+-------+
| value |
+-------+
| 1 |
| 2 |
| 3 |
+-------+
3 rows in set (0.00 sec)
先程までの方式に比べ、nの値が大きくなっても修正箇所が1箇所（value < 3の部分のみ）で済むのが嬉しいポイントです。
強いて言えばクエリが直感的ではなく何をしているかが分かりづらいのが欠点でしょうか。
この方式は便利ですが、nの値が1000より大きいと下記のエラーが発生します。
mysql> WITH RECURSIVE seq AS (SELECT 1 AS value UNION ALL SELECT value + 1 FROM seq WHERE value < 1001)
SELECT * FROM seq;
ERROR 3636 (HY000): Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.
これはcte_max_recursion_depthシステム変数によって再帰の上限が制限されていることによるものなので、1000より大きい連番が必要な場合はシステム変数を変更してあげましょう。
SET cte_max_recursion_depth=2000;
自分の環境では1千万件の連番が7秒ちょっとで取得できたので、よほど大量の連番が必要でない限りはこの方式で十分そうです。
特に制限事項がなければ自分はこの方式を使っています。
4. UNION ALL・直積・ユーザー定義変数で頑張る
大きな連番（例えば1000とか）が必要で、かつ諸事情によりMySQL 8が使用できない場合は気合でなんとかします。
まず、1～10の連番をUNION ALLを使って取得します。
mysql> SELECT 1 AS value UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10;
+-------+
| value |
+-------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
+-------+
10 rows in set (0.00 sec)
これの直積（交差結合・CROSS JOINともいいます）を取って、行数を$10^{n}$に増幅させます。
※直積については下記のサイトが参考になるかと思います。
mysql> SELECT * FROM
(SELECT 1 AS value UNION ALL SELECT 2 ... UNION ALL SELECT 10) AS t1,
(SELECT 1 AS value UNION ALL SELECT 2 ... UNION ALL SELECT 10) AS t2
+-------+-------+
| value | value |
+-------+-------+
| 10 | 1 |
| 9 | 1 |
| ... | ... |
| 1 | 10 |
+-------+-------+
100 rows in set (0.00 sec)
さらに、ユーザー定義変数を使って連番を作成します。
MySQL :: MySQL 8.0 リファレンスマニュアル :: 9.4 ユーザー定義変数
mysql> SELECT @num := @num+1 AS value FROM
(SELECT 1 AS value UNION ALL SELECT 2 ... UNION ALL SELECT 10) AS t1,
(SELECT 1 AS value UNION ALL SELECT 2 ... UNION ALL SELECT 10) AS t2,
(SELECT @num := 0) AS v;
+-------+
| value |
+-------+
| 1 |
| 2 |
| ... |
| 100 |
+-------+
100 rows in set, 2 warnings (0.00 sec)
あとは必要な連番に合わせてt3, t4 ... を増やしていけば好きな連番を取得可能です。
必要な連番が10の倍数でない場合はLIMITを指定すれば大丈夫です。
mysql> SELECT @num := @num+1 AS value FROM
(SELECT 1 AS value UNION ALL SELECT 2 ... UNION ALL SELECT 10) AS t1,
(SELECT 1 AS value UNION ALL SELECT 2 ... UNION ALL SELECT 10) AS t2,
(SELECT 1 AS value UNION ALL SELECT 2 ... UNION ALL SELECT 10) AS t3,
(SELECT @num := 0) AS v LIMIT 995;
+-------+
| value |
+-------+
| 1 |
| 2 |
| ... |
| 995 |
+-------+
995 rows in set, 2 warnings (0.00 sec)
で、これって何に使うの？？
自分は検証用のデータをINSERTするときに利用することが多いです。
例えば下記のようなテーブルがあったとします。
CREATE TABLE `test_data` (
`no` int unsigned NOT NULL,
`name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`genre` int unsigned NOT NULL,
PRIMARY KEY (`no`)
);
ここに1,000件のテストデータを登録したいと思ったときに、下記のようなSQLを実行すればテストデータを一括登録できます。
mysql> INSERT INTO test_data (no, name, genre)
WITH RECURSIVE seq AS (SELECT 1 AS value UNION ALL SELECT value + 1 FROM seq WHERE value < 1000)
SELECT
value AS no,
CONCAT('name-', value) AS name,
CASE WHEN MOD(value, 3) = 0 THEN 1 WHEN MOD(value, 3) = 1 THEN 2 ELSE 99 END AS genre
FROM seq;
スクリプトの実行環境なしでもSQLが実行できる環境だけあればいいので、DB周りの検証をしたいときに便利だったりします。
他に良い使い所がありましたら、教えていただけるとめっちゃ嬉しいです＞＜
おしまい
ということで、MySQLで1～Nの連番を取得する方法4選でした。
4選とか言いながら、ぶっちゃけ普段は3番目の方法をコピペしまくってて他の方法はほとんど使ってません。
明日は再び@tkek321 さんの記事となります。
CloudFrontの記事ということなので、AWSについて勉強したい人は必見ですね！
自分も楽しみにしています。