How to Resolve MySQL's 'only_full_group_by' Error?

MySQL 5.7

Error: Detailed explanation and solution only_full_group_by In mysql 5.7, encounter "Expression #1 of Select list is not in Group by Clause" and "only_full_group_by" error may be confusing. This article aims to clarify this problem and provide solutions.

Problem analysis

5.7 The previous MySQL version allows the SELECT list to include columns that appear in the Group By clause, even if the values ​​of these columns may be different in the packet. However, in version 5.7, the default behavior has been changed, and it is allowed to do so only when these columns are aggregated. This is enforced by "only_full_group_by" mode.

Problem solution

The error indicates that the query list contains an uncompromising column, which does not have function dependencies between columns in the column in the group by clause. To solve this problem, you can delete the uncompromising columns or add it to the group by clause. Recommended solution

For this specific query, it is recommended to add the "group_id" to the group by clause to ensure that all the uncompromising columns are related to the columns in the Group By clause:

Other precautions

In multi -table query, it is best to use the table name or alias limited list name, as shown in the recommended solution.

<code class="language-sql">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_id,
  g.group_name
HAVING
  COUNT(d.user_id) > 0
ORDER BY
  g.group_name</code>

It is not recommended to turn off "only_full_group_by" because it allows any uncompromising columns, which may cause inconsistent results and unpredictable.

The above is the detailed content of How to Resolve MySQL's 'only_full_group_by' Error?. For more information, please follow other related articles on the PHP Chinese website!