MySQL + Laravel 中使用 GROUP BY 分组查询

[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：

idfirst_namelast_namecityage1JasonMartinToronto152AlisonMathewsVancouver163JamesMathewsVancouver144CeliaRiceVancouver145DavidLarryNew York16

比如，我们根据 city 分组，但我们只需要查询出每一组的数据条数大于 1 的。

SELECT *,count(*) FROM book GROUP BY city having count(*)>1

结果集为：

idfirst_namelast_namecityagecount(*)2AlisonMathewsVancouver163 二、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());