laravel5.8(二十五)解决使用groupby报错SELECT list is not in GROUP BY clause and contains nonaggregated column

在laravel中数据库查询使用groupby报错:

SQLSTATE[
42000
]: Syntax error or access violation: 
1055
 Expression #
1
 
of
 SELECT list is not 
in
 GROUP BY clause and contains nonaggregated column 
'sns.sns_users.md_img'
 which is not functionally dependent on columns 
in
 GROUP BY clause; 
this
 is incompatible 
with
 sql_mode
=
only_full_group_by
 (SQL: select 
`sns_users`
.
`md_img`
, 
`phone`
, 
`sns_users`
.
`id`
 
from
 
`sns_blogs`
 left join 
`sns_users`
 on 
`user_id`
 
=
 
`sns_users`
.
`id`
 where 
date
(sns_blogs.created_at) 
>=
 
DATE_SUB
(
CURDATE
(), INTERVAL 
2
 DAY) group by 
`phone`
 order by 
count
(
*
) desc limit 
9
)

大概意思就是说数据库的执行模式中有only_full_group_by,使用groupby可能会发生问题,禁止使用groupby。

百度了一下,网上大多数的答案都是修改sql_mode,将only_full_group_by去掉。

但有的时候,我们需要确定一下问题的所在,再去寻找解决办法。

我将在框架中执行的sql,放到了navicat中取执行,执行成功没有报错,那就说明,不是数据库的问题,可能是框架配置的问题。

修改位置如下:

框架根目录下config/database.php

'strict'
 => 
false
,

具体代码如下,注意注释部分

  'mysql' => [
        'driver' => 'mysql',
        'url' => env('DATABASE_URL'),
        'host' => env('DB_HOST', '127.0.0.1'),
        'port' => env('DB_PORT', '3306'),
        'database' => env('DB_DATABASE', 'forge'),
        'username' => env('DB_USERNAME', 'forge'),
        'password' => env('DB_PASSWORD', ''),
        'unix_socket' => env('DB_SOCKET', ''),
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'prefix' => '',
        'prefix_indexes' => true,
        // strict这个配置,改为false,groupby就能用了
        'strict' => false,
        'engine' => null,
        'options' => extension_loaded('pdo_mysql') ? array_filter([
            PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
        ]) : [],

遇到问题了,记录一下原因。

有好的建议,请在下方输入你的评论。

欢迎访问个人博客
https://guanchao.site

欢迎访问小程序:
laravel5.8(二十五)解决使用groupby报错SELECT list is not in GROUP BY clause and contains nonaggregated column

上一篇:Pandas 的groupby操作


下一篇:mysql版本过高,groupby出现问题