MySQL与sql_mode = only_full_group_by不兼容 [英] MySQL incompatible with sql_mode=only_full_group_by
问题描述
我有以下查询(更新如下):
I have the following query (UPDATED SEE BELOW):
SELECT
i0_.id AS id_0,
i0_.address AS address_1,
i1_.name AS name_2,
c2_.name AS name_3,
c3_.code AS code_4,
l4_.iso AS iso_5,
c5_.id AS id_6,
c6_.name AS name_7,
i7_.identifier AS identifier_8
FROM institutions i0_
LEFT JOIN institution_languages i1_ ON (i1_.institution_id = i0_.id)
LEFT JOIN countries c3_ ON (c3_.id = i0_.country_id)
LEFT JOIN country_languages c2_ ON (c2_.country_id = c3_.id)
LEFT JOIN country_spoken_languages c8_ ON (c8_.country_id = c3_.id)
LEFT JOIN cities c5_ ON (c5_.id = i0_.city_id)
LEFT JOIN city_languages c6_ ON (c6_.city_id = c5_.id)
LEFT JOIN languages l4_ ON (l4_.id = i1_.language_id)
LEFT JOIN institution_types i7_ ON (i0_.institution_type_id = i7_.id)
WHERE c8_.is_primary = 1
AND c8_.language_id = l4_.id
AND c2_.language_id = 546
AND i7_.identifier = "work_place"
GROUP BY id_6 #here is the issue...
更新查询
UPDATED Query
SELECT
i0_.id AS id_0,
i0_.address AS address_1,
i1_.name AS name_2,
c2_.name AS name_3,
c3_.code AS code_4,
c4_.name AS name_5,
i5_.identifier AS identifier_6
FROM institutions i0_
LEFT JOIN institution_languages i1_ ON (i1_.institution_id = i0_.id)
LEFT JOIN countries c3_ ON (c3_.id = i0_.country_id)
LEFT JOIN country_languages c2_ ON (c2_.country_id = c3_.id AND c2_.language_id = ?)
LEFT JOIN country_spoken_languages c6_ ON (c6_.country_id = c3_.id AND c6_.language_id = ? AND c6_.is_primary = ?)
LEFT JOIN city_languages c4_ ON (c4_.city_id = i0_.city_id)
LEFT JOIN institution_types i5_ ON (i0_.institution_type_id = i5_.id)
WHERE i5_.identifier = ?
GROUP BY i0_.city_id
此查询的GROUP_BY存在问题,我不确定该如何解决:
This query is having a problem with GROUP_BY which I am not sure how to solve:
1055-SELECT列表的表达式#1不在GROUP BY子句中,并且包含非聚合列'database.i0_.id',这不是
在功能上取决于GROUP BY子句中的列;这是
与sql_mode = only_full_group_by不兼容
1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'database.i0_.id' which is not
functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
我知道可以通过设置only_full_group_by轻松解决此问题,但是我可以对我的查询做什么使其正常运行,而不必修改服务器上的MySQL设置?
I know this can be easily solved by setting off the only_full_group_by but what can i do to my query to make it work properly and not having to modify the MySQL setup on my server?
推荐答案
如果不想更改sql_mode = only_full_group_by
if you want not change the sql_mode=only_full_group_by
您可以简单地向不属于分组依据的列添加一个聚集函数(例如min()或max()
you can simply add an aggegation function to the column not involved in group by ( eg min() or max()
(在以前的版本中,该列的结果是不可预测的。通过这种方式,您可以分配一个规则来获取这些列的值)
(in the previuos versione the result for this column was impredictable. in this way you assign a rule for get the value for these columns )
SELECT
i0_.id AS id_0,
min(i0_.address AS) address_1,
min(i1_.name) AS name_2,
min(c2_.name )AS name_3,
min(c3_.code) AS code_4,
min(c4_.name) AS name_5,
min(i5_.identifier) AS identifier_6
FROM institutions i0_
LEFT JOIN institution_languages i1_ ON (i1_.institution_id = i0_.id)
LEFT JOIN countries c3_ ON (c3_.id = i0_.country_id)
LEFT JOIN country_languages c2_ ON (c2_.country_id = c3_.id AND c2_.language_id = ?)
LEFT JOIN country_spoken_languages c6_ ON (c6_.country_id = c3_.id AND c6_.language_id = ? AND c6_.is_primary = ?)
LEFT JOIN city_languages c4_ ON (c4_.city_id = i0_.city_id)
LEFT JOIN institution_types i5_ ON (i0_.institution_type_id = i5_.id)
WHERE i5_.identifier = ?
GROUP BY i0_.city_id
这篇关于MySQL与sql_mode = only_full_group_by不兼容的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!