当前位置:首页 > 服务端 > SQLSTATE[42000]: Syntax error or access violation: 1055 'kf_local.g.group_name' isn't in GROUP BY

SQLSTATE[42000]: Syntax error or access violation: 1055 'kf_local.g.group_name' isn't in GROUP BY

这是 ThinPHP 中的一段取数据的代码:

$result = $this->field('g.group_code,g.group_name,count(m.member_code) AS member_num,c.company_name,u.user_uid')
                       ->alias('g')
                       ->join('LEFT JOIN company AS c ON g.company_code=c.company_code LEFT JOIN user AS u ON u.user_code=g.holder_code LEFT JOIN groups_member AS m ON m.group_code=g.group_code')
                       ->where("g.group_name LIKE '%" . $key . "%' OR u.user_uid='" . $key ."'")
                       ->group('g.group_code,g.group_name,c.company_name,u.user_uid')
                       ->select();

生成如下 SQL :

SELECT 
  g.group_code,
  g.group_name,
  COUNT(m.member_code) AS member_num,
  c.company_name,
  u.user_uid 
FROM
  groups g 
  LEFT JOIN company AS c 
    ON g.company_code = c.company_code 
  LEFT JOIN USER AS u 
    ON u.user_code = g.holder_code 
  LEFT JOIN groups_member AS m 
    ON m.group_code = g.group_code 
WHERE (
    g.group_name LIKE '%武汉%' 
    OR u.user_uid = '武汉'
  ) 
GROUP BY g.group_code

乍一看是没什么问题,直接丢到数据库执行也是没什么问题。

“但是” 程序运行就报错,我们来看看错误提示:

SQLSTATE[42000]: Syntax error or access violation: 1055 'kf_local.g.group_name' isn't in GROUP BY

这是 Overflow 里一段与之相关的答案,

You need to have a full group by:

SELECT name, type, language, code
FROM users
WHERE verified = ‘1’
GROUP BY name, type, language, code
ORDER BY count DESC LIMIT 0, 25

SQL92 requires that all columns (except aggregates) in the select clause is part of the group by clause.

create table t (x int, y int);
insert into t (x,y) values (1,1),(1,2),(1,3);
select x,y from t group by x;

+——+——+
| x | y |
+——+——+
| 1 | 1 |
+——+——+

I.e. a random y is select for the group x. One can prevent this behavior by setting > @@sql_mode:
set @@sql_mode=’ONLY_FULL_GROUP_BY’;
select x,y from t group by x;
ERROR 1055 (42000): ‘test.t.y’ isn’t in GROUP BY

其实也就是 sql_mode 的问题,可以试试改为默认的 “宽松”模式:

set @@sql_mode='';


SELECT 
  g.group_code,
  g.group_name,
  COUNT(m.member_code) AS member_num,
  c.company_name,
  u.user_uid 
FROM
  groups g 
  LEFT JOIN company AS c 
    ON g.company_code = c.company_code 
  LEFT JOIN USER AS u 
    ON u.user_code = g.holder_code 
  LEFT JOIN groups_member AS m 
    ON m.group_code = g.group_code 
WHERE (
    g.group_name LIKE '%武汉%' 
    OR u.user_uid = '武汉'
  ) 
GROUP BY u.user_uid

作者:PeterZhaoChina
来源链接:https://www.cnblogs.com/peterzha/p/6853249.html

版权声明:
1、Java侠(https://www.javaxia.com)以学习交流为目的,由作者投稿、网友推荐和小编整理收藏优秀的IT技术及相关内容,包括但不限于文字、图片、音频、视频、软件、程序等,其均来自互联网,本站不享有版权,版权归原作者所有。

2、本站提供的内容仅用于个人学习、研究或欣赏,以及其他非商业性或非盈利性用途,但同时应遵守著作权法及其他相关法律的规定,不得侵犯相关权利人及本网站的合法权利。
3、本网站内容原作者如不愿意在本网站刊登内容,请及时通知本站(javaclubcn@163.com),我们将第一时间核实后及时予以删除。





本文链接:https://www.javaxia.com/server/125375.html

标签: syntax error
分享给朋友:

“SQLSTATE[42000]: Syntax error or access violation: 1055 'kf_local.g.group_name' isn't in GROUP BY” 的相关文章