laravel 解決 mysql only_full_group_by 問題

Wsmallnews發表於2021-01-20
LaravelMySql

MySQL 5.7 之後 only_full_group_by 預設是開啟的，這就導致 sql 的檢測更加嚴格，將導致報下面的錯

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'edu.t_sounds.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

解決這個問題也走了不少彎路，按照網上找的方式一個一個試

解決思路

檢視 sql_mode

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

首先檢視並修改 mysql 配置檔案，my.cnf 很尷尬我的這個裡面並沒有 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，確實顯示結果和配置的是一樣的，但是程式依然報同樣的錯

又是一番查詢，突然意識到 global session 的問題，也就是文章頭部放的兩條查詢 sql_mode 的語句，就查詢一下 是啥區別

mysql 變數設定方式分兩種，
一種全域性配置，也就是 global，作用於全域性;
一種會話配置 session, 只作用於當前連線

會不會是laravel 在當前連線設定了 sql_mode

在 laravel 程式列印 sql_mode

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

$result = \DB::select('SELECT @@SESSION.sql_mode');
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

protected function setModes(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 配置，有的話就直接使用這個

來，搞一下

'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

將直接設定程式中寫死的 sql_mode, laravel 區分了 mysql 8.0.11 和別的版本

protected function strictMode(PDO $connection)
{
    if (version_compare($connection->getAttribute(PDO::ATTR_SERVER_VERSION), '8.0.11') >= 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

總結：走了不少彎路，也花了不少時間，最終問題解決，並且並不需要修改 mysql 的任何配置

本作品採用《CC 協議》，轉載必須註明作者和本文連結

相關文章