SQLSTATE[42000]: Syntax error or access violation: 1055 'kf_local.g.group_name' isn't in GROUP BY
来源:互联网 发布:gogs windows安装 编辑:程序博客网 时间:2024/06/03 19:02
这是 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
FROMusers
WHEREverified
= ‘1’
GROUP BYname
,type
,language
,code
ORDER BYcount
DESC LIMIT 0, 25SQL92 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
- SQLSTATE[42000]: Syntax error or access violation: 1055 'kf_local.g.group_name' isn't in GROUP BY
- 16.Laravel报错SQLSTATE[42000] Syntax error or access violation 1055 'xxx' isn't in GROUP BY.txt
- laravel 5.4 报错SQLSTATE[42000] Syntax error or access violation 1055 'xxx' isn't in GROUP BY
- laravel运行带有group by的sql时提示 “SQLSTATE[42000]: Syntax error or access violation: 1055 错误解决
- Laravel:php artisan migrate报错SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default
- 安装Magento报错:SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'perio
- MySQL SQLSTATE[42000]: Syntax error or access violation: 1286 Unknown table engine 'InnoDB' 错误的原因及解决
- Laravel5.5问题:SQLSTATE[42000] Syntax error or access violation 1071 Specified key was too long; max k
- Laravel : Syntax error or access violation: 1055 Error
- error: Apostrophe not preceded by \ (in haven't input accoount or it isn't a legal E-mail)
- Syntax error or access violation: 1286 Unknown storage engine 'InnoDB' 解决办法
- Laravel migrate时报错: Syntax error or access violation: 1071 Specified key was too long
- laravel中报错:Syntax error or access violation: 1115 Unknown character set: 'utf8mb4'
- Laravel中使用GroupBy时产生的错误MySQL : isn't in GROUP BY
- Segmentation fault(bus error or access violation)的原因
- Error:Access Violation
- TFTP error: 'Access violation'
- Caused by: java.sql.SQLException: sql injection violation, syntax error: ERROR. token : DESC
- HTTP协议解析
- size_t与ssize_t
- 火狐重新打开标签页单独显示json的快捷操作方法
- C++函数匹配
- EXPLAIN sql优化方法2 Using temporary ; Using filesort
- SQLSTATE[42000]: Syntax error or access violation: 1055 'kf_local.g.group_name' isn't in GROUP BY
- PS学习笔记-----选区工具------套索工具
- angularjs 前端传递人员验证信息给后台
- 【java】内存映射文件、虚拟内存、RandomAccessFile类
- Java——File
- Eclipse中SVN插件中英文互相转换方法
- 27号华为面试
- HTTP头字段总结
- HDU4185Oil Skimming(行列匹配||棋盘匹配||黑白染色||1X2矩形覆盖)