mysql - laravel groupby 正在我的本地上运行，但不在实时服务器上运行

Question

我正在使用 groupBy() 对数据进行分组以在图表中显示。它正在我的本地主机上运行，​​但是当我将代码推送到实时站点时，它会抛出“不是按错误分组”的错误。

错误。

SQLSTATE[42000]:语法错误或访问冲突:1055 'dds_sa.appointments.date_time' 不在 GROUP BY 中

代码:

public function dailyReports(){
     return DB::table('appointments')
        ->select(DB::raw("
        sum(case when status = 'scheduled' then 1 else 0 end) as scheduled,
        sum(case when status = 'complete' then 1 else 0 end) as complete,
        sum(case when status = 'sold' then 1 else 0 end) as sold,
        DATE_FORMAT(date_time,'%W %M %e %Y') as date"))
        ->groupBy('date')
        ->orderBy('date_time')
        ->get();
}

.ENV

DB_CONNECTION=mysql

DB_HOST=127.0.0.1

DB_PORT=3306

DB_DATABASE=db_name

DB_USERNAME=user_name

DB_PASSWORD=db_pass

数据库默认设置:

'mysql' => [
        'driver' => 'mysql',
        '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' => '',
        'strict' => true,
        'engine' => null,
    ],

有人可以指导我解决它吗？

Answer 1

MySQL 已启用 ONLY_FULL_GROUP_BY。您可以使用以下命令禁用它:

mysql > SET sql_mode=(SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));

要查看启用模式的列表，请运行:

mysql > SELECT @@sql_mode 

在配置中，还要更改:

'strict' => false,