MySQL/MariaDB, 検索クエリの結果に連番をつける方法

MySQL/MariaDBで検索クエリ（SELECT）結果に連番をつける方法はかんたんに見つかりますが、細かい解説がすぐには見つかりません。

SQLに慣れてない人のために解説をつけました。@numって何だ？とか。これくらいのレベルで行きます。

（自分もよく忘れるので備忘録でもある。）

最初に答えをいうと、ユーザー定義変数（@num）を使います。

（じつは@numでなくてもいい。あとで説明。）

まずは、テスト用テーブルを用意します。今回はテーブルをひとつだけ使います。

SELECT * FROM test_date;

これに連番をつけます。いきなりSQLの答えから。

SELECT
  @num:=@num+1 as `num`, t1.*
FROM
  (SELECT * FROM test_date) AS t1,
  (SELECT @num:=0) AS dummy_t;

細かいことは忘れても（わからなくても）、SQLをさわったことがあればなんとくなくやっていることは分かります。

SQLもそこまで複雑じゃないので。

でも、シンプルの中にけっこうなことを詰め込んでます。ひとつずつ見ていきましょう。

ユーザー定義変数（@num）

連番には変数を使います。プログラミングでよくあるこの形。

num = num + 1;

SQLでは変数を使うことができます。ただし、DBMSごとに定義の仕方はちがいますが。

今回は、MySQL, MariaDBでの変数の使い方です。

（OracleとかPostgresは5年以上使っていないので忘れた。）

変数のインクリメントで連番

SELECT @num:=@num+1 as `num`

SELECT句にインクリメントを入れることで、検索クエリの結果の1行に対して『num = num +1』が実行され連番の列（column）が作られます。

これは説明しなくてもなんとなく分かりますね？

変数は初期化が必要

FROM
  (SELECT * FROM test_date) AS t1,
  (SELECT @num:=0) AS dummy_t;

もうひとつ変数を使ってるところがあります。FROM句です。

@num:=0ってなんぞや？

と思う人もいるでしょう。これは変数値の初期化です。たとえば、次のSQLを1回以上実行してみてください。

初期化をはずしたSQLです。

SELECT
    @num:=@num+1 as `num`, t1.*
FROM
   (SELECT *
   FROM test_date) AS t1;

1回目の連番の続きになってしまいました。変数の値は、1回のSQL実行だけで使うものではありません。そしてひとつのSQL文でリセットされることもありません。

複数のSQL文で同じ変数を使えば値が引きつがれます。

は1回のSQL実行で値をリセットしています。（初期値0）

変数名は『num』じゃなくてもいい

ググって調べると、変数名は『num』を使っているし、『@numでできます。』と言っているものもあります。

でもこれは『変数名』なので、とくに決まりはありません。『no』でもいいし『counter』でもいいです。

なんとなくですが、変数名ではなくて『@num』というSQLのルールだと思っているフシもあるような気がします。

（たんに説明してないだけかもしれませんが。）

変数初期化の入れる場所

じつは変数の初期化にはこのような方法もあります。

SET @num:=0;
SELECT
  @num:=@num+1 as `num`, t1.*
FROM
  (SELECT * FROM test_date) AS t1;

この方法はWebフレームワークなどで使えるかどうか分かりません。フレームワークでは、SQL文作成のクラス・メソッドが用意されているからです。

なぜFROM句に初期化処理を入れるのか？

じゃあ、どうしてFROM句に初期化処理を入れるのか？ もう一回SQL文を見てみましょう。

SELECT
  @num:=@num+1 as `num`, t1.*
FROM
  (SELECT * FROM test_date) AS t1,
  (SELECT @num:=0) AS dummy_t;

理由はひとつです。

SQLの実行順を見れば分かります。

インクリメント処理は3、初期化処理は2の1回で、初期化処理の条件を満たしています。

SELECT @num:=0は省略形

SELECT @num:=0ってSQLまちがってるんじゃない？

と思う人もいるかも知れません。（ボクは思いました。）

MySQL, MariaDBでは、テーブルのカラムを使わないSELECT句はFROM句の省略ができます。

の省略形だと思えばいいでしょう。

（dualテーブルはMySQL, MariaDBに用意されている仮想テーブル）

FROM句はどうゆうテーブルを作るのか？

SQLをちょっと変更して、FROM句のクロス結合の結果を見てみます。

（最終的なクエリ（SELECT句）で使うテーブルの結果のこと。）

SELECT *
FROM
  (SELECT * FROM test_date) AS t1,
  (SELECT @num:=0) AS dummy_t;

最終的に使ってないだけで、じつは不要な列（column）ができています。クロス結合は複数テーブルの結果の全パターン結合だから。

『SELECT @num:=0』の結果が

なので、t1のクエリ結果に追加されます。

方法自体はすぐに見つかる

ひとつ言い忘れてました。この連番のつけ方はボクがオリジナルで考えたわけではなく、ググるとすぐに出てくる情報です。

調べてみると分かりますが、

の条件さえ守れば、いろいろな方法があります。

（でも考え方は全部同じ。）