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'),
    ]) : [],
],

おわりに

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

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

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

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

ではでは〜❗

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

この記事が役立ちましたらシェアお願いします😊✨ by 九保すこひ
また、わかりにくい部分がありましたらお問い合わせからお気軽にご連絡ください。
(また、個人レッスンも承ってます👍)
このエントリーをはてなブックマークに追加       follow us in feedly