[PR]上記の広告は3ヶ月以上新規記事投稿のないブログに表示されています。新しい記事を書く事で広告が消えます。
比較的大きな仮想連番の作成方法について説明します。
仮想的に数千~数万件の連番を返すテーブルを取得する 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;
コメント