阅读目录
[Err] 1055 sql_mode=only_full_group_by
[SQL]select `fromid`, `toid`, `fromname` from `chat_communication` where `toid` = 85 GROUP BY fromname
[Err] 1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'workman.chat_communication.fromid' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
解决方案
1、查询mysql 相关mode
show variables like '%sql_mode%'
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
2、show variables like '%sql_mode%' 命令查询，发现里面有 ONLY_FULL_GROUP_BY ， 将其删除掉。
3、在把：sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
粘贴到
4、重启MySQL
service mysqld stop 停止mysql服务命令
service mysqld start 启动mysql服务命令
5、预览效果
知识点
MySQL 的 sql_mode 模式
MySQL数据库的中有一个环境变量sql_mode,定义了MySQL应该支持的sql语法，数据校验等！
我们可以通过以下方式查看当前数据库使用的sql_mode：show variables like ‘%sql_mode%’
sql_mode 值的含义
| ONLY_FULL_GROUP_BY | 对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么将认为这个SQL是不合法的，因为列不在GROUP BY从句中 |
| STRICT_TRANS_TABLES | 在该模式下，如果一个值不能插入到一个事务表中，则中断当前的操作，对非事务表不做任何限制 |
| NO_ZERO_IN_DATE | 在严格模式，不接受月或日部分为0的日期。如果使用IGNORE选项，我们为类似的日期插入'0000-00-00'。在非严格模式，可以接受该日期，但会生成警告。 |
| NO_ZERO_DATE | 在严格模式，不要将 '0000-00-00'做为合法日期。你仍然可以用IGNORE选项插入零日期。在非严格模式，可以接受该日期，但会生成警告 |
| ERROR_FOR_DIVISION_BY_ZERO | 在严格模式，在INSERT或UPDATE过程中，如果被零除(或MOD(X，0))，则产生错误(否则为警告)。如果未给出该模式，被零除时MySQL返回NULL。如果用到INSERT IGNORE或UPDATE IGNORE中，MySQL生成被零除警告，但操作结果为NULL。 |
| NO_AUTO_CREATE_USER | 防止GRANT自动创建新用户，除非还指定了密码。 |
| NO_ENGINE_SUBSTITUTION | 如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置此值时，用默认的存储引擎替代，并抛出一个异常。 |
解决 laravel 出现 Syntax error or access violation: 1055 ‘***‘ isn‘t in GROUP BY
laravel 5.3 以后默认开启 mysql 严格模式(strict)
在mysql在严格模式下, 并且开启了 ONLY_FULL_GROUP_BY 的情况下,
group by 的字段没有出现在 select 的语句中会报错.关闭了严格模式就不会报错.
Demo
public static function orders($phase)
{
return self::select(DB::raw("SUM(orders) as orders"),"type","users_id","name")
->groupBy("type","name")
->where("phase",$phase)
->get();
}
会出现如下错误，当然*号内的内容是不同的
Syntax error or access violation: 1055 '***' isn't in GROUP BY
解决办法
laravel出现上述错误是 sql_mode 设置导致的问题,修改 config/database.php 配置文件的 strict => false 即可关闭了严格模式:
'mysql' => [
'driver' => 'mysql',
'host' => env('DB_HOST', 'localhost'),
'port' => env('DB_PORT', '3306'),
'database' => env('DB_DATABASE', 'forge'),
'username' => env('DB_USERNAME', 'forge'),
'password' => env('DB_PASSWORD', ''),
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',
'prefix' => '',
'strict' => false,
'engine' => null,
],
一、介绍
1.1 MySQL使用 GROUP BY 分组查询
在 MySQL 中，GROUP BY 关键字可以根据一个或多个字段对查询结果进行分组。
使用 GROUP BY 关键字的语法格式如下：GROUP BY <字段名>
其中，“字段名”表示需要分组的字段名称，多个字段时用逗号隔开。
下面根据 tb_students_info 表中的 sex 字段进行分组查询，SQL 语句和运行结果如下：
mysql> SELECT `name`,`sex` FROM tb_students_info GROUP BY sex;
+-------+------+
| name | sex |
+-------+------+
| Henry | 女 |
| Dany | 男 |
+-------+------+
2 rows in set (0.01 sec)
结果中只显示了两条记录，这两条记录的 sex 字段的值分别为“女”和“男”。
1.2 GROUP BY 和 GROUP_CONCAT() 一起使用
GROUP BY 关键字可以和 GROUP_CONCAT() 函数一起使用。GROUP_CONCAT() 函数会把每个分组的字段值都显示出来。
下面根据 tb_students_info 表中的 sex 字段进行分组查询，使用 GROUP_CONCAT() 函数将每个分组的 name 字段的值都显示出来。
SQL 语句和运行结果如下：
mysql> SELECT `sex`, GROUP_CONCAT(name) FROM tb_students_info GROUP BY sex;
+------+----------------------------+
| sex | GROUP_CONCAT(name) |
+------+----------------------------+
| 女 | Henry,Jim,John,Thomas,Tom |
| 男 | Dany,Green,Jane,Lily,Susan |
+------+----------------------------+
2 rows in set (0.00 sec)
结果中可以看到，查询结果分为两组，sex 字段值为 “女” 的是一组，值为 “男” 的是一组，且每组的学生姓名都显示出来了。
下面根据 tb_students_info 表中的 age 和 sex 字段进行分组查询。
SQL 语句和运行结果如下：
mysql> SELECT age,sex,GROUP_CONCAT(name) FROM tb_students_info GROUP BY age,sex;
+------+------+--------------------+
| age | sex | GROUP_CONCAT(name) |
+------+------+--------------------+
| 21 | 女 | John |
| 22 | 女 | Thomas |
| 22 | 男 | Jane,Lily |
| 23 | 女 | Henry,Tom |
| 23 | 男 | Green,Susan |
| 24 | 女 | Jim |
| 25 | 男 | Dany |
+------+------+--------------------+
7 rows in set (0.00 sec)
上面实例在分组过程中，先按照 age 字段进行分组，当 age 字段值相等时，再把 age 字段值相等的记录按照 sex 字段进行分组。
多个字段分组查询时，会先按照第一个字段进行分组。如果第一个字段中有相同的值，MySQL 才会按照第二个字段进行分组。如果第一个字段中的数据都是唯一的，那么 MySQL 将不再对第二个字段进行分组。
1.3 GROUP BY 与聚合函数
在数据统计时，GROUP BY 关键字经常和聚合函数一起使用。
聚合函数包括 COUNT()，SUM()，AVG()，MAX() 和 MIN()。
COUNT() 用来统计记录的条数；
SUM() 用来计算字段值的总和；
AVG() 用来计算字段值的平均值；
MAX() 用来查询字段的最大值；
MIN() 用来查询字段的最小值。
下面根据 tb_students_info 表的 sex 字段进行分组查询，使用 COUNT() 函数计算每一组的记录数。
SQL 语句和运行结果如下：
mysql> SELECT sex,COUNT(sex) FROM tb_students_info GROUP BY sex;
+------+------------+
| sex | COUNT(sex) |
+------+------------+
| 女 | 5 |
| 男 | 5 |
+------+------------+
2 rows in set (0.00 sec)
结果显示：
sex 字段值为“女”的记录是一组，有 5 条记录；
sex 字段值为“男”的记录是一组，有 5 条记录。
1.4 GROUP BY 与 WITH ROLLUP
WITH POLLUP 关键字用来在所有记录的最后加上一条记录，这条记录是上面所有记录的总和，即统计记录数量。
下面根据 tb_students_info 表中的 sex 字段进行分组查询，并使用 WITH ROLLUP 显示记录的总和。
mysql> SELECT sex,GROUP_CONCAT(name) FROM tb_students_info GROUP BY sex WITH ROLLUP;
+------+------------------------------------------------------+
| sex | GROUP_CONCAT(name) |
+------+------------------------------------------------------+
| 女 | Henry,Jim,John,Thomas,Tom |
| 男 | Dany,Green,Jane,Lily,Susan |
| NULL | Henry,Jim,John,Thomas,Tom,Dany,Green,Jane,Lily,Susan |
+------+------------------------------------------------------+
3 rows in set (0.00 sec)
查询结果显示，GROUP_CONCAT(name) 显示了每个分组的 name 字段值。
同时，最后一条记录的 GROUP_CONCAT(name) 字段的值刚好是上面分组 name 字段值的总和。
1.5 group by having用于指示被选择的行必须满足的条件
现在有这样一个数据表 book：
| id | first_name | last_name | city | age |
| 1 | Jason | Martin | Toronto | 15 |
| 2 | Alison | Mathews | Vancouver | 16 |
| 3 | James | Mathews | Vancouver | 14 |
| 4 | Celia | Rice | Vancouver | 14 |
| 5 | David | Larry | New York | 16 |
比如，我们根据 city 分组，但我们只需要查询出每一组的数据条数大于 1 的。
SELECT *,count(*) FROM book GROUP BY city having count(*)>1
结果集为：
| id | first_name | last_name | city | age | count(*) |
| 2 | Alison | Mathews | Vancouver | 16 | 3 |
二、laravel 中使用 groupBy 分组查询，分组统计，预加载查询和统计
1、使用分组查询
2、使用分组统计
3、预加载统计
4、预加载查询
三、知识点
1.1 groupBy 链表顺序问题
groupBy 放在 table 后和 where 后好使，放在 get 后将失效
DB::enableQueryLog();
$info = DB::table('chat_communication')->groupBy('fromid')->where('toid',$fromid)->get(['fromid','toid','fromname'])->toArray();
dd(DB::getQueryLog());
1014
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
