歯抜け数字を埋めるSQL - sakaikの日々雑感～(T)編

MySQLユーザ会のメーリングリストで、こんな話題がありました。
INSERT SELECT でのテーブル別名の使い方

「歯抜けの数字を、連番になるようにレコードを追加する」という方法について、相関サブクエリを使用して解決したものですが、これ、相関サブクエリを使わずに今ならどう書くかな～と思って試してみました。　MLへの投稿の本題からはずれているので、自分の日記にて。

やりたいこととデータの準備

　PKとして「歯抜け連番」がセットされているテーブルにて、抜けている数字のレコードを追加したい、というのがやりたいことです。

CREATE TABLE tbl2 (id INTEGER PRIMARY KEY,
                   name varchar(10),
                   somedata varchar(10));

INSERT INTO tbl2 VALUES (2, "test2", "some2");
INSERT INTO tbl2 VALUES (3, "test3", "some3");
INSERT INTO tbl2 VALUES (4, "test4", "some4");
INSERT INTO tbl2 VALUES (6, "test6", "some6");
INSERT INTO tbl2 VALUES (10, "test10", "some10");
INSERT INTO tbl2 VALUES (11, "test11", "some11");
INSERT INTO tbl2 VALUES (18, "test18", "some18");
INSERT INTO tbl2 VALUES (21, "test21", "some21");
INSERT INTO tbl2 VALUES (24, "test24", "some22");

内容確認：

mysql> SELECT * FROM tbl2;
+----+-------+----------+
| id | name  | somedata |
+----+-------+----------+
|  2 | test2 | some2    |
|  3 | test2 | some2    |
|  4 | test2 | some2    |
|  6 | test2 | some2    |
| 10 | test2 | some2    |
| 11 | test2 | some2    |
| 18 | test2 | some2    |
| 21 | test2 | some2    |
| 24 | test2 | some2    |
+----+-------+----------+

　このテーブルで、抜けている、1, 5, 7, 8, 9... のレコードを追加したい、というわけです。

方針

　必要となる全部の数字が入ったテーブルを用意し、対象テーブルとのJOINにより不足数字を見つけ出す方法を採ることにしました。
もちろん本当に数字が入ったテーブルを作成するわけではなく、WITH句を使います。
　Window関数を使って何かできないかと考えたのですが、ちょっと良いアイデアが浮かびませんでした。

不足数字をSELECTするSQLの確認

　こんな感じ。

mysql> WITH RECURSIVE num(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM num WHERE n<(SELECT MAX(id) FROM tbl2))
    -> SELECT n id, null, null FROM num LEFT OUTER JOIN tbl2 ON (num.n=tbl2.id) WHERE tbl2.id IS NULL;
+------+------+------+
| id   | NULL | NULL |
+------+------+------+
|    1 | NULL | NULL |
|    5 | NULL | NULL |
|    7 | NULL | NULL |
|    8 | NULL | NULL |
|    9 | NULL | NULL |
|   12 | NULL | NULL |
|   13 | NULL | NULL |
|   14 | NULL | NULL |
|   15 | NULL | NULL |
|   16 | NULL | NULL |
|   17 | NULL | NULL |
|   19 | NULL | NULL |
|   20 | NULL | NULL |
|   22 | NULL | NULL |
|   23 | NULL | NULL |
+------+------+------+
15 rows in set (0.00 sec)

INSERT する

　SELECTできることが確認できたので、そのままつっこめばいいだけ。

mysql> INSERT INTO tbl2
    -> WITH RECURSIVE num(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM num WHERE n<(SELECT MAX(id) FROM tbl2))
    -> SELECT n id, null, null FROM num LEFT OUTER JOIN tbl2 ON (num.n=tbl2.id) WHERE tbl2.id IS NULL;
Query OK, 15 rows affected (0.01 sec)
Records: 15  Duplicates: 0  Warnings: 0

結果確認：

mysql> SELECT * FROM tbl2 ORDER BY id;
+----+--------+----------+
| id | name   | somedata |
+----+--------+----------+
|  1 | NULL   | NULL     |
|  2 | test2  | some2    |
|  3 | test3  | some3    |
|  4 | test4  | some4    |
|  5 | NULL   | NULL     |
|  6 | test6  | some6    |
|  7 | NULL   | NULL     |
|  8 | NULL   | NULL     |
|  9 | NULL   | NULL     |
| 10 | test10 | some10   |
| 11 | test11 | some11   |
| 12 | NULL   | NULL     |
| 13 | NULL   | NULL     |
| 14 | NULL   | NULL     |
| 15 | NULL   | NULL     |
| 16 | NULL   | NULL     |
| 17 | NULL   | NULL     |
| 18 | test18 | some18   |
| 19 | NULL   | NULL     |
| 20 | NULL   | NULL     |
| 21 | test21 | some21   |
| 22 | NULL   | NULL     |
| 23 | NULL   | NULL     |
| 24 | test24 | some22   |
+----+--------+----------+