ツイート
シェア
LINEで送る
B! はてぶでブックマーク
Pocketでブックマーク
RSSフィード

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

mysql, mariaDB logo
イラストダウンロードサイト【イラストAC】
の画像をもとに加工しています。

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

SQLに慣れてない人のために解説をつけました。

(自分もよく忘れるので備忘録でもある。)

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

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

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

SELECT * FROM test_date;
dateval1val2
2019/11/0810test1
2019/12/0920test2
2020/02/1030test3
2020/06/1140test4
2021/03/1250test5
2021/09/1360test6

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

連番をつけるSQL
SELECT
  @num:=@num+1 as `num`, t1.*
FROM
  (SELECT * FROM test_date) AS t1,
  (SELECT @num:=0) AS dummy_t;
numdateval1val2
12019/11/0810test1
22019/12/0920test2
32020/02/1030test3
42020/06/1140test4
52021/03/1250test5
62021/09/1360test6

細かいことは忘れても(わからなくても)、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;
numdateval1val2
12019/11/0810test1
22019/12/0920test2
32020/02/1030test3
42020/06/1140test4
52021/03/1250test5
62021/09/1360test6
1回目の結果
numdateval1val2
72019/11/0810test1
82019/12/0920test2
92020/02/1030test3
102020/06/1140test4
112021/03/1250test5
122021/09/1360test6
2回目の結果

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

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

@num:=0

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

SQLの変数はグローバル変数。

値は2回目以降のSQL実行に引き継がれるし、ほかのSQL文にも引き継がれる。

OracleやPostgresについてはよく分かりません。ここ数年使ってないので。それぞれ調べて下さい。

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

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

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

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

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

もちろん、変数名に使えるものには制限があります。予約後は使えないとか。

そのへんは公式ドキュメントで確認してください。

変数初期化の入れる場所

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

連番をつけるSQL
SET @num:=0;
SELECT
  @num:=@num+1 as `num`, t1.*
FROM
  (SELECT * FROM test_date) AS t1;

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

select()

where()

order()

etc...。

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

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

連番をつけるSQL
SELECT
  @num:=@num+1 as `num`, t1.*
FROM
  (SELECT * FROM test_date) AS t1,
  (SELECT @num:=0) AS dummy_t;

理由はひとつです。

ひとつのSQLで、初期化処理はインクリメント処理の前に1回だけ実行するため。

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

実行順
1FROM句内のクエリ
(SELECT * FROM test_date)
を実行。
2FROM句のクエリ
(SELECT @num:=0)
を実行。
31, 2の結果作られる仮想テーブル
(t1, dummy_tのクロス結合の結果)から
SELECT
@num:=@num+1 as num,
t1.*
FROM
t1, dummy_t
クエリを実行。

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

SELECT @num:=0は省略形

エンジニア
SELECT @num:=0ってSQLまちがってるんじゃない?

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

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

SELECT @num:=0 FROM dual;

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

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

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

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

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

連番をつけるSQL
SELECT *
FROM
  (SELECT * FROM test_date) AS t1,
  (SELECT @num:=0) AS dummy_t;
dateval1val2@num:=0
2019/11/0810test10
2019/12/0920test20
2020/02/1030test30
2020/06/1140test40
2021/03/1250test50
2021/09/1360test60

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

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

@num:=0
0

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

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

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

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

インクリメントの前に1回の初期化処理

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

(でも考え方は全部同じ。)

コメントを残す

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください