MySQLで1～Nの連番を取得する方法4選

この記事は [ミライトデザイン Advent Calendar 2022](https://qiita.com/advent-calendar/2022/miraito-inc) の3日目の記事です。

今年も懲りずに乱入させていただきました。

昨日は@tkek321 さんのS3 で静的ページを公開する記事でしたね。
ちょっとしたページを公開したいけどWebサーバ作るのはちょっと、、、というときに重宝しそうですね。

本日は MySQL に関するちょっとした小ネタをご紹介したいと思います。

## やりたいこと

MySQL上でいい感じのクエリを実行して下記のような連番を取得したいです。
テーブルやビューなどは作成せず、Nを自由に変更できるのが理想です。

```sql
mysql> SELECT ???
+-------+
| value |
+-------+
|     1 |
|     2 |
|   ... |
|     N |
+-------+
```

見た目は簡単そうですが、知らないと意外と手こずるかもしれません（自分は以前めっちゃ時間を溶かしました(T_T)）。

## 環境

```bash
mysql --version
mysql  Ver 8.0.31 for Linux on x86_64 (MySQL Community Server - GPL)
```

## 1. `UNION ALL`を使用する

おそらく一番シンプルな方法です。

```sql
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](https://qiita.com/tarosuke777000/items/391b0291faae45974be1)

## 2. `VALUES ROW()`を使用する

MySQL 8.0.19 以降であれば、`VALUES ROW()`を使用すると`UNION ALL`と同様の結果を取得できます。

https://dev.mysql.com/doc/refman/8.0/ja/values.html

```sql
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 以降であれば、再帰的な共通テーブル式を使用して連番を取得することも可能です。

https://dev.mysql.com/doc/refman/8.0/ja/with.html#common-table-expressions-recursive

```sql
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より大きいと下記のエラーが発生します。

```sql
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より大きい連番が必要な場合はシステム変数を変更してあげましょう。

```sql
SET cte_max_recursion_depth=2000;
```

自分の環境では1千万件の連番が7秒ちょっとで取得できたので、よほど大量の連番が必要でない限りはこの方式で十分そうです。

特に制限事項がなければ自分はこの方式を使っています。

## 4. `UNION ALL`・直積・ユーザー定義変数で頑張る

大きな連番（例えば1000とか）が必要で、かつ諸事情によりMySQL 8が使用できない場合は気合でなんとかします。
まず、1～10の連番を`UNION ALL`を使って取得します。

```sql
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}$に増幅させます。

※直積については下記のサイトが参考になるかと思います。

https://www.javadrive.jp/sqlite/join/index3.html

```sql
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 ユーザー定義変数](https://dev.mysql.com/doc/refman/8.0/ja/user-variables.html)

```sql
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`を指定すれば大丈夫です。

```sql
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するときに利用することが多いです。

例えば下記のようなテーブルがあったとします。

```sql
 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を実行すればテストデータを一括登録できます。

```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について勉強したい人は必見ですね！
自分も楽しみにしています。

## 参考URL

- [Generating Numeric Sequences in MySQL - Percona Database Performance Blog](https://www.percona.com/blog/2020/07/27/generating-numeric-sequences-in-mysql/)
- [MySQLで1〜Nの数列を扱う | 晴耕雨読](https://tex2e.github.io/blog/database/mysql-numerical-sequence)