laravel 解决 mysql5.7 only_full_group

2021-02-1316:59:16后端程序开发Comments1,645 views字数 2817阅读模式

MySQL 5.7 之后 only_full_group_by 默认是开启的，这就导致 sql 的检测更加严格，将导致报下面的错。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/20952.html

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

解决这个问题也走了不少弯路，按照网上找的方式一个一个试，文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/20952.html

解决思路

查看 sql_mode文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/20952.html

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

首先查看并修改 mysql 配置文件，很尴尬我的这个里面并没有 ONLY_FULL_GROUP_BY

[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

不行，就接着来，想到mysql 有服务端配置，和客户端配置之分，那就增加 [client] 配置

[client]
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

重启mysql ,在MySQL工具查看 sql_mode，确实显示结果和配置的是一样的，但是程序依然报同样的错文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/20952.html

又是一番查找，突然意识到 global session 的问题，也就是文章头部放的两条查询 sql_mode 的语句，就查询一下是啥区别

mysql 变量设置方式分两种，
一种全局配置，也就是 global，作用于全局;
一种会话配置 session, 只作用于当前连接文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/20952.html

会不会是laravel 在当前连接设置了 sql_mode

在 laravel 程序打印 sql_mode文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/20952.html

$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

找到了原来是程序设置了文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/20952.html

第一想到 mysql 的配置有一个严格模式，我一直是开启状态，设置为 false 问题顺利解决

'strict'=>false,

这样可以解决问题，但本着技术就是要搞清楚到底是咋写的，也并不想直接给他设置为 false,

在 vendor/laravel/framework/src/ILLuminate/Database 文件夹下搜索 strict ，直接找到核心代码文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/20952.html

文件：vendor/laravel/framework/src/ILLuminate/Database/Connectors/MySqlConnector.php文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/20952.html

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 配置，有的话就直接使用这个文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/20952.html

来，搞一下文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/20952.html

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

测试，直接问题搞定文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/20952.html

本着寻根刨底的精神，再接着往下看文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/20952.html

如果 strict = true文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/20952.html

将直接设置程序中写死的 sql_mode, laravel 区分了 mysql 和别的版本文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/20952.html

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文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/20952.html

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

到这里问题就彻底解决了文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/20952.html

最终解决方式文章源自菜鸟学院-https://www.cainiaoxueyuan.com/bc/20952.html

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