SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'week2.article.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (SQL: select `article`.*, count(article_id) as count from `article` left join `zan` on `article`.`id` = `zan`.`article_id` group by `zan`.`article_id`)
$data = DB::table('article')
->select('article.*',DB::raw("count(article_id) as count"))
->leftJoin('zan','article.id','=','zan.article_id')
->groupBy('zan.article_id')
->get()->toArray();
上面的查询语句在查询数据和点赞数量的时候会报错
查询mysql 1055错误码发现问题为在mysql的配置中如果设置了sql_mode包含ONLY_FULL_GROUP_BY值得话,在进行查询时需要将select的字段都包含在group by 中。
即 select x,y from xxx group by x,y
否则就会报错
但是查看自己的配置my.cnf发现在sql_mode中并没有ONLY_FULL_GROUP_BY这个值
然后去查看Laravel的配置文件,config/database.php,查找mysql的配置,
'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' => env('DB_PREFIX',''),
'strict' => true,
'engine' => null,
],
发现有个strict项,默认为true,上网也没有查找到相关解释,根据字面意思猜测可能为是否开启严格模式,将其修改为false,再次测试发现问题解决,可以输出正确结果
————————————————
版权声明:本文为CSDN博主「张高伟」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/qq_36663951/article/details/77127267