Skip to main content

sql_mode=only_full_group_by报错

#sql_mode=only_full_group_by报错

报错信息是:

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'yiqicefu.sg.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (SQL: SELECT sg.*,u.nick_name,u.mobile,u.phone,mr.title FROM send_goods as sg LEFT JOIN users as u ON sg.users_id = u.id left join merchant_run as mr ON sg.run_id = mr.id where 1 GROUP BY sg.users_id,sg.run_id limit 0,10 )

mysql 的 my.cnf 设置

[mysqld] 项下的 sql_mode 已经去掉了 only_full_group_by

sql_mode=STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

但是Laravel还报这个错。

是因为mysql的连接配置里面有一项 strict 严格模式 默认是true 改为false就行了。

解决办法:

将 'strict' => true 改为 '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', ''),
'username' => env('DB_USERNAME', ''),
'password' => env('DB_PASSWORD', ''),
'unix_socket' => env('DB_SOCKET', ''),
'charset' => 'utf8mb4',
'collation' => 'utf8mb4_unicode_ci',
'prefix' => '',
'prefix_indexes' => true,
'strict' => false,
'engine' => null,
'options' => extension_loaded('pdo_mysql') ? array_filter([
PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
]) : [],
],