查询mysql 1055错误码发现问题为在mysql的配置中如果设置了sql_mode包含ONLY_FULL_GROUP_BY值得话,在进行查询时需要将select的字段都包含在group by 中。
即 select x,y from xxx group by x,y
否则就会报错
但是查看自己的配置my.cnf发现在sql_mode中并没有ONLY_FULL_GROUP_BY这个值
然后去查看Laravel的配置文件,config/database.php,查找mysql的配置,
'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' => env('DB_PREFIX',''),
'strict' => true,
'engine' => null,
],
发现有个strict项,默认为true,上网也没有查找到相关解释,根据字面意思猜测可能为是否开启严格模式,将其修改为false,再次测试发现问题解决,可以输出正确结果
源代码解释:
vendor\laravel\framework\src\Illuminate\Database\Connectors\MySqlConnector.php 约144行
protected function setModes(PDO $connection, array $config)
{
if (isset($config['modes'])) {
$this->setCustomModes($connection, $config);
} elseif (isset($config['strict'])) {
if ($config['strict']) { //这边有个判断如果是true则执行strictMode()方法所以要改为false
$connection->prepare($this->strictMode())->execute();
} else {
$connection->prepare("set session sql_mode='NO_ENGINE_SUBSTITUTION'")->execute();
}
}
}
protected function strictMode()
{
return "set session sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'";
}
也可以编辑 /etc/my.cnf 文件
在**[mysqld]**追加
~~```
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
~~重启mysql即可。~~