laravel 解析 mysql5.7 only_full_group

terry 2年前 (2023-09-25) 阅读数 61 #后端开发

从 MySQL 5.7 开始，默认开启 only_full_group_by，导致 SQL 检测更加严格，会导致报如下错误。

SQLSTATE[42000]: Syntax error or access violation:1055 Expression #1ofSELECT list is not inGROUPBY clause and contains nonaggregated column '' which is not functionally dependent on columns inGROUPBY clause;this is incompatible with sql_mode=only_full_group_by

为了解决这个问题我也走了很多弯路。我按照网上找到的方法一一尝试过。

解决思路

检查sql_mode

select @@GLOBAL.sql_mode;SELECT @@SESSION.sql_mode

首先检查并更改mysql配置文件。我很尴尬，里面什么都没有。如果没有 ONLY_FULL_GROUP_BY

[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

，它就无法工作，所以让我们继续。如果你认为mysql有服务端配置和客户端配置，那么添加【客户端】配置

[client]
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

重启mysql，检查MySQL工具中的sql_mode，实际显示结果和配置。是一样的，但是程序还是报同样的错误

再次查找，突然发现全局session的问题，就是文章最上面放的两条sql_mode的查询语句，于是查了一下区别在于

变量设置方式Mysql分为两种，
全局配置，即全局，全局工作；
仅在当前连接上起作用的会话配置会话

laravel 会在当前连接上使用吗？设置sql_mode

在laravel中打印sql_mode

$result = \DB::select('SELECT @@');print_r($result);exit;
结果：
STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

$result = \DB::select('SELECT @@');print_r($result);exit;
结果：
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

，发现是程序设置的

我首先想到的是mysql配置有严格模式。我总是将其打开并将其设置为 false。问题顺利解决了

'strict'=>false,

这样就可以解决问题了，但是基于技术，我们需要弄清楚它是怎么写的，我们不想直接设置为false。

在vendor/laravel/framework/src/ILLuminate/Database文件夹中找到strict。直接找到代码库文件

：vendor/laravel/framework/src/ILLuminate/Database/Connectors/MySqlConnector.php

protectedfunctionsetModes(PDO $connection, array $config){if(isset($config['modes'])){
        $this->setCustomModes($connection, $config);}elseif(isset($config['strict'])){if($config['strict']){
            $connection->prepare($this->strictMode($connection))->execute();}else{
            $connection->prepare("set session sql_mode='NO_ENGINE_SUBSTITUTION'")->execute();}}}

第一个判断是直接判断是否有模式配置。如果是的话，直接用这个

来吧，运行一下测试

'modes'=>['STRICT_TRANS_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','ERROR_FOR_DIVISION_BY_ZERO','NO_AUTO_CREATE_USER','NO_ENGINE_SUBSTITUTION'],

，直接解决问题

本着root的精神，我们继续往下看

如果strict = true，就直接设置

程序中硬编码sql_mode，区分laravel Mysql和其他版本

protectedfunctionstrictMode(PDO $connection){if(version_compare($connection->getAttribute(PDO::ATTR_SERVER_VERSION),'')>=0){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_ENGINE_SUBSTITUTION'";}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'";}

然后，如果strict = false，直接将sql_mode设置为NO_ENGINE_SUBSTITUTION

$connection->prepare("set session sql_mode='NO_ENGINE_SUBSTITUTION'")->execute();

至此问题彻底解决

最终解决方案

'strict'=>true,'modes'=>['STRICT_TRANS_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','ERROR_FOR_DIVISION_BY_ZERO','NO_AUTO_CREATE_USER','NO_ENGINE_SUBSTITUTION'],

保持strict = true，添加modes选项，里面的参数都是基本的laravel配置，只去掉了ONLY_FULL_GROUP_BY