MySQL「GROUP BY」エラー解決法・全4件

こんにちは。フリーランス・コンサルタント&エンジニアの 九保すこひ です。

さてさて、これは開発者がたまに遭遇する「あるある」な話かもしれませんが、DBからGROUP BYでデータ取得するとき以下のようなエラーが発生したことはないでしょうか。

Syntax error or access violation: 1055 Expression #(数字) of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘(フィールド名)’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (SQL: select (フィールド名), id from (テーブル名) group by (グループ化フィールド名))

このことを個人的に「ONLY_FULL_GROUP_BY問題」と呼んでいるのですが、なぜこのエラーが発生するのかを知っておかないと、長時間ハマってしまうこともあったりします。

そこで!

今回はGROUP BYしたときのエラーについてご紹介します。
ぜひ皆さんのお役に立てると嬉しいです😊✨

「かく言う私もよくハマってました😂」

どんな時にエラーになる?

例えば、次のようなテーブルがあるとします。

このテーブルには、各ユーザーが投稿した内容が保存されています。

では、「ユーザーごとの最新投稿」を取得する場合を考えてみましょう。
おそらく一番に思いつくのはこんなSQLではないでしょうか。

SELECT *, MAX(id) FROM posts GROUP BY user_id

しかし、残念ながらこの場合はエラーになります。(注:MySQLの設定にもよります)

では、次の場合はどうでしょう。

SELECT user_id, MAX(id) FROM posts GROUP BY user_id

実はこの場合エラーは発生せず次のような結果を取得できます。

つまり、グループ化したときにエラーが発生するのは次の場合です。

  • “GROUP BY” で指定していないカラムが “SELECT” に入っている
  • ただし、MAX() や MIN() などの集計関数を使っていればどのカラムでもOK

なぜエラーになるのか?

ざっくり言うとMySQLが優柔不断で、

「いやいや、僕にはどのデータか決めらんないよ・・・😫」

となっています。

では、実際の例を見てみましょう。
例えば次のようなテーブルです。

見ていただくと分かるとおり、ユーザーID1のデータを2で色分けしています。

では、user_idでグループ化します。
(分かりやすくデータはidcontentだけ)

では、この状態でcontentを取得しようとすると、MySQLはどう思うでしょうか。

「グループ化したのはいいけど、contentはいっぱいあるからどれを取ってくればいいか分からない・・・😥」となります。

これがエラーの全貌です。

MAX()MIN()を使うとエラーが出なくなるのもこれが理由です。(「どれ???」がなくなるからです)

解決するには

では、以下のテーブルで「ユーザーごとの最新記事を取得する」ためにはどうすればいいかを見ていきましょう。

※なお、最後のもの以外は、Laravel 7.xを使ったコードと実際のSQL文の2つをご紹介します。

コードを分ける

まず最もシンプルなのが、コードを分割する方法です。

つまり、

  1. ユーザーごとの最新IDを取得する
  2. そのIDを元にpostsデータを取得する

となります。

// 1.ユーザーごとの最新IDを取得
$ids = \App\Post::selectRaw('MAX(id) AS id')
    ->groupBy('user_id')
    ->pluck('id');

// 2.IDを元にデータを取得
$posts = \App\Post::whereIn('id', $ids)
    ->orderBy('id', 'DESC')
    ->get();

実際のSQL文はこうなります。

SELECT MAX(id) AS id FROM posts GROUP BY user_id

SELECT * FROM posts WHERE id IN ((ここに取得したID)) ORDER BY id DESC

WHERE句にサブクエリを使う

WHERE *** IN句の中にサブクエリを作って実装する方法です。

$posts = \App\Post::whereIn('id', function($q) {

    // 👇 サブクエリ
    return $q->from('posts')
        ->selectRaw('MAX(id) as max_id')
        ->groupBy('user_id');

})
->orderBy('id', 'DESC')
->get();

実行されるSQL文はこうなります。

SELECT * FROM posts 
WHERE id IN 
  (
    SELECT MAX(id) AS max_id FROM posts GROUP BY user_id
  )
ORDER BY id DESC

サブクエリで結合する

こちらもサブクエリを使いますが、使う場所はINNER JOINの中です。

$posts = \App\Post::join(
    \DB::raw('(SELECT MAX(id) AS max_id FROM posts GROUP BY user_id) AS latest'),
    'posts.id', '=', 'latest.max_id'
)
->orderBy('id', 'DESC')
->get();

実行されるSQL文はこうなります。

SELECT * FROM posts 
  INNER JOIN
  (
    SELECT MAX(id) AS max_id FROM posts GROUP BY user_id
  )
  AS latest 
  ON posts.id = latest.max_id 
ORDER BY id DESC

only_full_group_byを解除する

MySQLが厳格なSQLを要求しないようにすることができます。
Laravelの場合、configファイルで設定します。

⚠ ただし、この方法はあまりおすすめしません。行のデータ整合性がくずれる可能性があるからです。(例:idは最新のものなのに、contentは別の行から取得されてしまう)

/config/database.php

'mysql' => [
    'driver' => 'mysql',
    'url' => env('DATABASE_URL'),
    'host' => env('DB_HOST', '127.0.0.1'),
    'port' => env('DB_PORT', '3306'),
    'database' => env('DB_DATABASE', 'forge'),
    'username' => env('DB_USERNAME', 'forge'),
    'password' => env('DB_PASSWORD', ''),
    'unix_socket' => env('DB_SOCKET', ''),
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => '',
    'prefix_indexes' => true,
    'strict' => false, // 👈 ここを "false" にする
    'engine' => null,
    'options' => extension_loaded('pdo_mysql') ? array_filter([
        PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
    ]) : [],
],
開発のご依頼お待ちしております
開発のご依頼はこちらから: お問い合わせ
どうぞよろしくお願いいたします! by 九保すこひ

おわりに

ということで、今回はMySQLGROUP BYのエラーについてご紹介しました。

なお、その昔はこのような問題は発生していませんでしたので、もしかするとMySQLをアップグレードして急にエラーが発生することがあるかもしれません(MySQL 5.7以降、ONLY_FULL_GROUP_BYは初期状態で有効です)

ただ、やはり新しいバージョンで有効になっているということは、そちらの方があるべき姿だと判断されたということですので、ONLY_FULL_GROUP_BYは有効にして開発した方がいいかと思います。

ぜひ皆さんも一度この問題を頭の中で整理してみてくださいね。

ではでは〜❗

「一回ちゃんと覚えても、
時間が経つと忘れちゃうんですよね(笑)」

このエントリーをはてなブックマークに追加       follow us in feedly