使用laravel分页时会引发MySQL错误 [英] MySQL error is thrown when laravel pagination is used

问题描述

我创建了一个数据库视图，当我对这个视图使用laravel分页时，它会抛出:

I have a database view created and when I use laravel pagination against this view, it throws:

SQLSTATE[42000]: Syntax error or access violation: 1140 Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause (SQL: select count(*) as aggregate from `parameter_log_site_detail` where `site_id` = EPE)

但是此错误仅在实时服务器中弹出.分页在本地服务器上工作正常.

But this error pops up only in live server. Pagination works fine in local server.

数据库视图: (parameter_log_site_detail)

select `t`.`site_id` AS `site_id`,cast(`t`.`logged_at` as date) AS `logged_on`,`t`.`daily_generation` AS `daily_generation`,`t`.`reading` AS `tot_reading` from `parameter_log_tab` `t` order by cast(`t`.`logged_at` as date) desc

型号:

public function scopeSiteDailyReadings($query)
{
    return $query->from('parameter_log_site_detail');
}

控制器:

$generations = EnergyGeneration::siteDailyReadings()->where('site_id', $site_id)->orderBy('logged_on', 'desc')->paginate(15);

实时服务器信息

MySQL Server version: 5.6.37 - MySQL Community Server (GPL)
PHP version: 5.6.30
Laravel version: 5.4

本地服务器信息(正常运行的地方)

MySQL Server version: 5.7.19 - MySQL Community Server (GPL)
PHP version: 7.1.7
Laravel version: 5.4

有人可以解释一下这是什么原因吗?我读到有关ONLY_FULL_GROUP_BY的文章.但是不能弄清楚这是服务器版本为5.6.*的原因.

Could someone explain what can be the reason for this? I read about ONLY_FULL_GROUP_BY. But cannot figure out is it the reason since the server version is 5.6.*.

推荐答案

可以在MySQL 5.6中设置SQL模式ONLY_FULL_GROUP_BY，但默认情况下未设置(请参见

It's possible to set SQL mode ONLY_FULL_GROUP_BY in MySQL 5.6, but it's not set by default (see https://dev.mysql.com/doc/refman/5.6/en/sql-mode.html).

啊哈，我看到您的评论出现在上方，您已经确认在本地服务器(MySQL 5.7)上设置了ONLY_FULL_GROUP_BY.

Aha, I see your comment appeared above, you have confirmed that ONLY_FULL_GROUP_BY is set on your local server (MySQL 5.7).

我认为您在问题描述中遗漏了一些内容.如果本地服务器只有ONLY_FULL_GROUP_BY而实时服务器没有，则应该在本地服务器上收到错误，但在实时服务器上则不会.

I think you have misstated something in your problem description. You should get the error on your local server, but not on the live server, if local has ONLY_FULL_GROUP_BY and live does not.

我建议您确保在开发中使用的版本与在生产中使用的版本相同，并且还要匹配相同的SQL模式.这样可以防止开发过程中的混乱.

I suggest you make sure to use the same version in development as the version you use in production, and also match the same SQL mode. This will prevent confusion during development.

我对PHP版本提出了相同的建议.如果您在开发中使用了一些新的PHP 7功能，然后将其部署到PHP 5.6实时服务器，则它们将无法工作.

I make the same suggestion about version of PHP. If you use some new PHP 7 features in development, and then deploy to your PHP 5.6 live server, they won't work.

您描述的SQL应该没问题:

The SQL you describe should be fine:

select count(*) as aggregate from `parameter_log_site_detail` where `site_id` = EPE

这实际上可以，即使您只有ONLY_FULL_GROUP_BY.对表中所有匹配的行进行select count(*)当然是合法的.您不需要此查询的GROUP BY子句.

This is actually okay, even if you have ONLY_FULL_GROUP_BY. It's certainly legal to do a select count(*) of all matching rows in the table. You don't need a GROUP BY clause for this query.

但是，如果将聚合列与非聚合列混合使用，则会违反ONLY_FULL_GROUP_BY的要求，因为非聚合列将是不明确的.

But if you mix aggregated columns with non-aggregated columns, you would violate the ONLY_FULL_GROUP_BY requirements, because the non-aggregated columns would be ambiguous.

select id, count(*) as aggregate from ...

我想知道Laravel是否在准备查询之前在您的选择列表中插入了额外的列.必须确保启用MySQL查询日志.

I wonder if Laravel is inserting extra column(s) into your select-list before preparing the query. You'd have to enable the MySQL query log to be sure.

我注意到在Laravel问题上对此错误进行了一些讨论: https://github .com/laravel/framework/issues/15232

I notice that there is some discussion of this error on Laravel issues: https://github.com/laravel/framework/issues/15232

该线程中几个用户说的解决问题的解决方案是在您的Laravel config/database.php中设置'strict'=>false.

The solution several users in that thread said fixes the problem is to set 'strict'=>false in your Laravel config/database.php.

但是我敢打赌，根本原因是Laravel正在修改您的SQL查询.

But I would bet that the root cause is that Laravel is modifying your SQL query.

这篇关于使用laravel分页时会引发MySQL错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！