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'),
]) : [],
],