解決laravel出現Syntax error or access violation: 1055 ‘***‘ isn‘t in GROUP BY

Q-u-a-n發表於2020-12-28

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,
],

相關文章