比較
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をそのまま使えばよい」