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の中でインクリメント(+1)できるので連番生成に利用できるようになる。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で+1しながら@aを表示していっている。表示の回数はfrom以降で指定したテーブルの個数で表示される。つまり、テーブルの中身は一切利用していないが、個数が必要となる。標準でインストールされた information_schema.columnsテーブルは私の環境では5000個程度のタプルがあり、5000まで生成できる。

 今度はこの連番QueryをサブクエリとしてJOINすることで、これをエンジンとして、クロス集計の際の抜けたインデックスを生成することができる。