システムをアップグレードし、作業中の Web アプリケーション用に MySQL 5.7.9 と PHP をインストールしました。動的に作成されるクエリがあり、MySQL の古いバージョンで実行すると正常に動作します。5.7 にアップグレードしてから、次のエラーが発生します。
SELECT リストの式 #1 は GROUP BY 句に含まれておらず、GROUP BY 句の列に機能的に依存しない非集計列 'support_desk.mod_users_groups.group_id' が含まれています。これは sql_mode=only_full_group_by と互換性がありません。
MySQL 5.7のマニュアルページには、サーバーSQLモード。
問題となっているクエリは次のとおりです:
SELECT mod_users_groups.group_id AS 'value',
group_name AS 'text'
FROM mod_users_groups
LEFT JOIN mod_users_data ON mod_users_groups.group_id = mod_users_data.group_id
WHERE mod_users_groups.active = 1
AND mod_users_groups.department_id = 1
AND mod_users_groups.manage_work_orders = 1
AND group_name != 'root'
AND group_name != 'superuser'
GROUP BY group_name
HAVING COUNT(`user_id`) > 0
ORDER BY group_name
only_full_group_by
クエリを修正するには何をすればよいのか、よくわかりません。only_full_group_by
オプションをオフにするだけでよいのでしょうか、それとも他に何かする必要があるのでしょうか?
ベストアンサー1
group_id
に追加したいと思いますGROUP BY
。
SELECT
の一部ではない列を ingすると、GROUP BY
グループ内にその列の値が複数存在する可能性がありますが、結果には 1 つの値しか表示されません。そのため、通常COUNT()
、データベースに、それらの複数の値を 1 つの値にする方法を正確に指示する必要があります。一般的に、これは、、SUM()
などの集約関数を使用して行われます。通常と言うMAX()
のは、他のほとんどの一般的なデータベース システムがこれを要求しているためです。ただし、バージョン 5.7 より前の MySQL では、デフォルトの動作はより寛容で、エラーを報告せずに任意の値を任意に選択することはありません。また、以前と同じ動作が本当に必要な場合に、この問題の別の解決策として使用できる関数もあります。この柔軟性は非決定論的であるためコストがかかります。そのため、必要な十分な理由がない限り、お勧めしません。MySQL は現在、正当な理由でこの設定をデフォルトでオンにしているので、それに慣れてクエリをそれに準拠させるのが最善です。ANY_VALUE()
only_full_group_by
では、なぜ私は上記のように簡単な答えをしたのでしょうか? 私はいくつかの仮定を立てました:
1) はgroup_id
一意です。結局のところ、これは「ID」なので、妥当なようです。
2) もgroup_name
一意です。これは、それほど合理的な仮定ではないかもしれません。そうでない場合、重複があり、私のアドバイスに従って にgroup_names
追加すると、同じ名前のグループが結果で別々の行に表示されるため、以前よりも多くの結果が表示されることがあります。私にとっては、データベースが勝手に値を選択したためにこれらの重複グループが非表示になるよりも、このほうが良いでしょう。group_id
GROUP BY
複数のテーブルが関係する場合は、すべての列をテーブル名またはエイリアスで修飾することもお勧めします...
SELECT
g.group_id AS 'value',
g.group_name AS 'text'
FROM mod_users_groups g
LEFT JOIN mod_users_data d ON g.group_id = d.group_id
WHERE g.active = 1
AND g.department_id = 1
AND g.manage_work_orders = 1
AND g.group_name != 'root'
AND g.group_name != 'superuser'
GROUP BY
g.group_name,
g.group_id
HAVING COUNT(d.user_id) > 0
ORDER BY g.group_name