MySQL(MariaDB) - 連番の仮想表作成！

[ サーバ構築 ] [ MariaDB, MySQL ]

MariaDB(MySQL) で自然数の連番だけのテーブル（表）が必要なことがあります。

以下、作成例と応用例についての備忘録です。

0. 前提条件

  • MariaDB サーバ 5.5.31, MySQL 5.6.19 での作業を想定。

1. 作成例

以下のようにユーザ変数と UNION 句を利用して作成して実行する。

1
2
3
4

SELECT @seq_no := 1 AS seq_no
UNION
SELECT @seq_no := @seq_no + 1 AS seq_no FROM table_name
LIMIT 5

1
2
3
4
5
6
7
8
9

+--------+
| seq_no |
+--------+
|      1 |
|      2 |
|      3 |
|      4 |
|      5 |
+--------+
  1. まず、 @seq_no という任意のユーザ変数に初期値を設定（実際には、初期値を設定したユーザ変数 @seq_no を抽出）する。
    （上記の例では初期値に 1 を設定）
  2. 次に、必要な件数以上のレコードが登録済みのテーブルを抽出元とするが、抽出するカラムは指定せずに @seq_no をインクリメントする。
    これで、2, 3, … , レコード件数 + 1 と抽出できる。
  3. 最後に、これら２つを UNION 結合し必要な件数を LIMIT 句で指定する。（上記の例では 5 を設定）
    これで、1, 2, 3, 4, 5 という内容の表が出来上がる。

2. 応用例

以下のようなテーブルの各レコードに連番をつけて別テーブルに挿入するような場合に応用できる。
（ここでは、抽出元テーブルを table_a, 挿入先テーブルを table_c, 仮想表を table_b としている。また、連番用のカラムは整数型であること）

同様のことをストアドプロシージャでインデックスをインクリメントしながらループさせて挿入する方法も考えられるだろうが、レコード件数が多くなると速度が支障になる。（実際、今回の方法より何倍から何十倍も遅い）

table_a
1
2
3
4
5
6
7

+------+
| code |
+------+
| 101  |
| 102  |
| 103  |
+------+

次のような SQL を生成して実行する。

1
2
3
4
5
6
7
8

INSERT INTO table_c
      (code, seq_no)
SELECT a.code, b.seq_no
  FROM table_a AS a,
      (SELECT @seq_no := 1 AS seq_no
       UNION
       SELECT @seq_no := @seq_no + 1 AS seq_no FROM table_b
       LIMIT 5) AS b

挿入先のテーブルに以下のように挿入される。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

+------+--------+
| code | seq_no |
+------+--------+
| 101  |      1 |
| 101  |      2 |
| 101  |      3 |
| 101  |      4 |
| 101  |      5 |
| 102  |      1 |
| 102  |      2 |
| 102  |      3 |
| 102  |      4 |
| 102  |      5 |
| 103  |      1 |
| 103  |      2 |
| 103  |      3 |
| 103  |      4 |
| 103  |      5 |
+------+--------+

ストアドでループ処理で行っていた挿入処理が格段に高速化することができました。

以上。