19
18

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

MySQLで結果セットに連番を割り振る

Last updated at Posted at 2015-05-26

比較

Before

オーソドックスに結果セットをイテレートしながら連番を割り振ります。

$sql
SELECT id, name
FROM user
ORDER BY name
PHP
foreach ($pdo->query($sql) as $i => $row) {
    $rows[] = array_merge(['rank' => $i + 1], $row->fetch(PDO::FETCH_ASSOC));
}

After

いちいち結果セットを取得した後にPHPでfor,foreach文をぶん回したりする必要もなく、SQLだけで実現できるようです。

$sql
SELECT @n:=@n+1 AS rank, t1.*
FROM (SELECT id, name FROM user ORDER BY name) t1, (SELECT @n:=0) t2
PHP
$rows = $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);

追記

一応タイトルにも「MySQLで」と明記してありますが、@variable形式のユーザー定義変数はMySQLの独自仕様なので、移植性を考えるのであればあまり使うべき方法ではありません。更に、最適化の過程で必ずしも変数が期待通りの順序で評価されることも保証されてはいないようです。よって

  • そもそも結果セットの中に番号を含める必要はなく、イテレート時に得られるキー$iをそのまま使えばよい
  • ORDER BYを用い、サブクエリ中で基準になるキーの大小関係を利用すればよい

以上どちらかの選択肢のほうが良いと思われます。後者の例を以下に示します。

$sql
SELECT (SELECT COUNT(*) FROM user t1 WHERE t1.name <= t2.name) AS rank, id, name
FROM user t2
ORDER BY name

オプティマイザ君がちゃんとやってくれそうだしパフォーマンスは大丈夫だよね…?

PRIMARY KEY以外だと性能のいいオプティマイザでも残念な結果になりそうです。更に最低限UNIQUEで無ければ同じ値が出てきた時に重複する番号が出てしまいます。

結論: 「そもそも結果セットの中に番号を含める必要はなく、イテレート時に得られるキー$iをそのまま使えばよい」

参考

19
18
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
19
18

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?