比較的大きな仮想連番の作成方法について説明します。
仮想的に数千～数万件の連番を返すテーブルを取得する SELECT のSQLを作成してみます。
ネットなどでは以下の様な感じで、例が載っています。
（information_schema.COLUMNS はMySQLが持っているテーブル内のカラムに関する情報用のテーブルです。）
select @no := @no + 1 as no from (select @no := 0) as dmy, information_schema.COLUMNS limit 10;
このSQLを実行すると以下の様になります。
MariaDB [test]> select @no := @no + 1 as no
-> from (select @no := 0) as dmy, information_schema.COLUMNS
-> limit 10
-> ;
+------+
| no |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
+------+
10 rows in set (0.14 sec)
information_schema.COLUMNS テーブルと「dmy」テーブルの直積のSQLですが、「dmy」は1件しかないので、 information_schema.COLUMNS がもつレコードが全て対象となります。
この方法には少々問題があります。 information_schema.COLUMNS のレコード数には限界があるからで、 実際、私がテストで使っているMySQLでは以下の様に6333件となり、この件数が限界となります。
MariaDB [(none)]> SELECT count(*) FROM information_schema.COLUMNS; +----------+ | count(*) | +----------+ | 6333 | +----------+ 1 row in set (7.27 sec)
もう少し、仮想連番テーブルの最大番号を大きくし、制御できる様に考えてみます。
information_schema.COLUMNS は必ずどのデータベースにも存在するので、これを利用するのですが、 information_schema.COLUMNS から10件ずつ取得したものから連番を発生させるサブクエリを2個用意して、 それらの直積を取る形で、仮想連番テーブル用の元テーブルとします。
文章で説明してもピンときませんので、以下のSQLを見て下さい。
SELECT * FROM (
(select @no1 := @no1 + 1 as no1 from (select @no1 := 0) as dmy, information_schema.COLUMNS limit 10) X
,(select @no2 := @no2 + 1 as no2 from (select @no2 := 0) as dmy, information_schema.COLUMNS limit 10) X1
);
これを実行すると以下の様に「10×10」のデータが表示されます。
MariaDB [(none)]> SELECT * FROM (
-> (select @no1 := @no1 + 1 as no1 from (select @no1 := 0) as dmy, information_schema.COLUMNS limit 10) X
-> ,(select @no2 := @no2 + 1 as no2 from (select @no2 := 0) as dmy, information_schema.COLUMNS limit 10) X1
-> );
+------+------+
| no1 | no2 |
+------+------+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 1 |
| 5 | 1 |
| 6 | 1 |
| 7 | 1 |
| 8 | 1 |
| 9 | 1 |
| 10 | 1 |
| 1 | 2 |
| 2 | 2 |
| 3 | 2 |
... (省略) ...
| 7 | 10 |
| 8 | 10 |
| 9 | 10 |
| 10 | 10 |
+------+------+
100 rows in set (0.30 sec)
このクエリを元にして、仮想連番テーブルを生成するSQLを作ると以下の様になります。
SELECT @no := @no + 1 AS idx
FROM (select @no := 0) as dmy,
(
SELECT * FROM (
(select @no1 := @no1 + 1 as no1 from (select @no1 := 0) as dmy, information_schema.COLUMNS limit 10) X
,(select @no2 := @no2 + 1 as no2 from (select @no2 := 0) as dmy, information_schema.COLUMNS limit 10) X1
)
) XX
LIMIT 10;
これを実行すると以下の様に1～10の連番を返すことができます。
MariaDB [(none)]> SELECT @no := @no + 1 AS idx
-> FROM (select @no := 0) as dmy,
-> (
-> SELECT * FROM (
-> (select @no1 := @no1 + 1 as no1 from (select @no1 := 0) as dmy, information_schema.COLUMNS limit 10) X
-> ,(select @no2 := @no2 + 1 as no2 from (select @no2 := 0) as dmy, information_schema.COLUMNS limit 10) X1
-> )
-> ) XX
-> LIMIT 10;
+------+
| idx |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
+------+
10 rows in set (0.25 sec)
サブクエリの LIMIT で生成されるデータ数の最大を決定していますが、この値を「100」にすれば最大は「10000」になります。 また、サブクエリの SELECT 文を更に追加して以下の様にすれば「10×10×10×10」が最大となります。
さらに「10」のところを「100」にすれば1億までの連番を返すことができます。（そこまでの必要性はありませんが）
SELECT @no := @no + 1 AS idx
FROM (select @no := 0) as dmy,
(
SELECT * FROM (
(select @no1 := @no1 + 1 as no1 from (select @no1 := 0) as dmy, information_schema.COLUMNS limit 10) X
,(select @no2 := @no2 + 1 as no2 from (select @no2 := 0) as dmy, information_schema.COLUMNS limit 10) X1
,(select @no3 := @no3 + 1 as no3 from (select @no3 := 0) as dmy, information_schema.COLUMNS limit 10) X2
,(select @no4 := @no4 + 1 as no4 from (select @no4 := 0) as dmy, information_schema.COLUMNS limit 10) X3
)
) XX
LIMIT 10;
コメント