Error related to only

I have upgraded my system and have installed MySql 5.7.9 with php for a web application I am working on. I have a query that is dynamically created, and when run in older versions of MySql it works fine. Since upgrading to 5.7 I get this error:

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'support_desk.mod_users_groups.group_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Note the Manual page for Mysql 5.7 on the topic of Server SQL Modes.

This is the query that is giving me trouble:

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

I did some googling on the issue, but I don't understand only_full_group_by enough to figure out what I need to do to fix the query. Can I just turn off the only_full_group_by option, or is there something else I need to do?

Let me know if you need more information.

---

I would just add group_id to the GROUP BY .

When SELECT ing a column that is not part of the GROUP BY there could be multiple values for that column within the groups, but there will only be space for a single value in the results. So, the database usually needs to be told exactly how to make those multiple values into one value. Commonly, this is done with an aggregate function like COUNT() , SUM() , MAX() etc... I say usually because most other popular database systems insist on this. However, in MySQL prior to version 5.7 the default behaviour has been more forgiving because it will not complain and then arbitrarily choose any value! It also has an ANY_VALUE() function that could be used as another solution to this question if you really needed the same behaviour as before. This flexibility comes at a cost because it is non-deterministic, so I would not recommend it unless you have a very good reason for needing it. MySQL are now turning on the only_full_group_by setting by default for good reasons, so it's best to get used to it and make your queries comply with it.

So why my simple answer above? I've made a couple of assumptions:

1) the group_id is unique. Seems reasonable, it is an 'ID' after all.

2) the group_name is also unique. This may not be such a reasonable assumption. If this is not the case and you have some duplicate group_names and you then follow my advice to add group_id to the GROUP BY , you may find that you now get more results than before because the groups with the same name will now have separate rows in the results. To me, this would be better than having these duplicate groups hidden because the database has quietly selected a value arbitrarily!

It's also good practice to qualify all the columns with their table name or alias when there's more than one table involved...

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

---

您可以尝试通过执行以下操作来禁用only_full_group_by设置：

mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
mysql> set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

---

you can turn off the warning message as explained in the other answers or you can understand what's happening and fix it.

As of MySQL 5.7.5, the default SQL mode includes ONLY_FULL_GROUP_BY which means when you are grouping rows and then selecting something out of that groups, you need to explicitly say which row should that selection be made from.

Mysql needs to know which row in the group you're looking for, which gives you two options

You can also add the column you want to the group statement group by rect.color, rect.value which can be what you want in some cases otherwise would return duplicate results with the same color which you may not want

you could also use aggregate functions of mysql to indicate which row you are looking for inside the groups like AVG() MIN() MAX() complete list

AND finally you can use ANY_VALUE() if you are sure that all the results inside the group are the same. doc

链接地址: http://www.djcxy.com/p/54930.html

上一篇: 服务没有应用程序（非

下一篇: 错误仅与相关