mysqlで集計をする際、連番・毎日という用途でインデックスを作成したい場合がある。このような場合にはmysqlの変数を利用して簡単に元となる整数値を生成することができる。なお、最新のmariadbではシーケンスエンジンも利用できるのだが、RedHat7系のmariadbは5.x系なので標準で利用できないため、昔からある変数を利用して生成するのが望ましい。
mysqlの変数
まずmysqlの変数は＠マークから始まる文字で変数を定義できる。
set @a ;
set @b=0;
set @c=1,@d=2;
設定した変数はセッション中は有効であり、グローバル変数のように利用できる。すなわち、設定した変数を変更した場合には、セッション終了まで同じ値を利用できる。
値を入れた変数は以下のように表示・確認できる。
select @c;
変数の値を更新したい場合には、コロンを利用する。
MariaDB []> set @a=0;
Query OK, 0 rows affected (0.00 sec)
MariaDB []> select @a:=@a+1;
+----------+
| @a:=@a+1 |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
MariaDB []> select @a;
+------+
| @a |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
このように、selectの中でインクリメント（＋１）できるので連番生成に利用できるようになる。select中で一度でも変更したものはセッションが終了するまで、同じ値を保存している。またsetで再度@a=0のように初期化することもできる。ここまでで変数の定義と、更新方法を説明した。次はこれを応用して連番データを生成する。
連番データの生成
結論から言うと、以下のように実行することで作成できる。
set @a=0;select @a:=@a+1 from ＜適度にタプルの多いテーブル＞;
set @a=0;select @a:=@a+1 from information_schema.columns;
1行で書いているが最初のsetで変数を初期化し、次のselectで＋１しながら@aを表示していっている。表示の回数はfrom以降で指定したテーブルの個数で表示される。つまり、テーブルの中身は一切利用していないが、個数が必要となる。標準でインストールされた information_schema.columnsテーブルは私の環境では5000個程度のタプルがあり、5000まで生成できる。
今度はこの連番QueryをサブクエリとしてJOINすることで、これをエンジンとして、クロス集計の際の抜けたインデックスを生成することができる。