[DataBase]MySQLの拡張仕様、GROUP BYでカラム指定を省略してSELECTする場合について

Submitted by Derick Ng on 12 July 2012 - 1:10pm

GROUP BY節で、選択したすべてのカラムの名前を列挙する必要はありません。これにより、ごく一部ではありますが、きわめて一般的なクエリのパフォーマンスが向上します。項11.11. 「GROUP BY 句との関数および修飾子の使用」 を参照してください。

MySQL :: MySQL 5.1 リファレンスマニュアル :: 1.8.4 SQL標準に対するMｙSQL拡張機能

SELECT foo_id, bar_id FROM examples GROUP BY foo_id;

MySQLでは、例えばこんな風に、GROUP BYにfoo_idだけを指定して、foo_id, bar_idを取得出来ます。上記の通り、実はこれはMySQLの拡張仕様らしいです。

SELECT foo_id, bar_id FROM examples GROUP BY foo_id, bar_id;

標準SQL規格では、この様にfoo_id, bar_idを取得したい場合は、GROUP BY foo_id, bar_idとする必要があります。

注意

GROUP BY 部から省略したカラムがグループ内で一定していない場合は、この機能を 使用しないで ください。サーバはいかなる値もグループから自由に戻すことができ、すべての値が同じでない限り、結果は不確定です。

MySQL :: MySQL 5.1 リファレンスマニュアル :: 11.11.3 非常時フィールドとの GROUP BY および HAVING

機械翻訳っぽくて分かりにくいですが、前述の例でGROUP BY foo_idでグルーピングした際、bar_idに何が入るか分からないという、落とし穴があるみたいです。これを知らなかったんですが、GROUP BYをあまり使った事が無く不安だったので聞いてみたら、教えてもらえました。

例

foo_id bar_id
1 1
1 2
1 3
2 1
2 1
2 1

分かりにくいし、後で忘れるかもしれないので、例を書いておきます。

こんなテーブルから、GROUP BYを使ってSELECTした場合の、SQL標準とMySQL拡張仕様の2パターンの想定動作*1を書いてみます。

SQL標準
SELECT foo_id, bar_id, count(foo_id) AS count FROM examples GROUP BY foo_id, bar_id;
foo_id bar_id count
1 1 1
1 2 1
1 3 1
2 1 3

SQL標準どおりちゃんと書けば、GROUP BYに指定するカラムはそもそも省略出来ません。foo_idとbar_idの両方が同じ値のレコードをグループにするので、この様になります。

MySQL拡張仕様
SELECT foo_id, bar_id, count(foo_id) AS count FROM examples GROUP BY foo_id;
foo_id bar_id count
1 ? 3
2 1 3

MySQL拡張仕様で、GROUP BYへのカラムを省略してGROUP BY foo_idとすると、foo_idだけ同じ値なら、グルーピングします。この場合、?になっているbar_idには、1, 2, 3の3通りの可能性があります。そして、MySQLの拡張仕様では、実行毎にどれかを入れるし、どれが入るかは分からないという話です。

例2

tech-memo » MySQLのGROUP BYは、寛容すぎて気持ちが悪い。

例を書いておいてなんですが、こっちのエントリーのほうが例がわかりやすいかもしれません。

書いた時期

2011-10-06

*1：実行してません。