[905]MySQL的sql_mode解析與設定和MySQLdb._exceptions.OperationalError: (1055, “Expression

周小董發表於2020-10-24
MySqlExceptionErrorExpress

問題：mysql (_mysql_exceptions.OperationalError) (1055, "Expression #1 of SELECT list is not in GROUP BY clause

方法1

檢視及更改sql_model

show VARIABLES like '%sql_mode%';
or
mysql> select @@sql_mode;

更改sql_model值
set global sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

方法2

sudo vi /etc/mysql/my.cnf

在開啟的my.cnf檔案中新增

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

儲存，退出

重啟mysql：/etc/init.d/mysql restart
登入mysql：mysql -u root -p
mysql ->select @@sql_mode;
驗證sql_mode的值是否改變。

sql_mode值的含義：

  • ONLY_FULL_GROUP_BY：
    對於GROUP BY聚合操作，如果在SELECT中的列，沒有在GROUP BY中出現，那麼將認為這個SQL是不合法的，因為列不在GROUP BY從句中

  • STRICT_TRANS_TABLES：
    在該模式下，如果一個值不能插入到一個事務表中，則中斷當前的操作，對非事務表不做任何限制

  • NO_ZERO_IN_DATE：
    在嚴格模式，不接受月或日部分為0的日期。如果使用IGNORE選項，我們為類似的日期插入’0000-00-00’。在非嚴格模式，可以接受該日期，但會生成警告。

  • NO_ZERO_DATE：
    在嚴格模式，不要將 '0000-00-00’做為合法日期。你仍然可以用IGNORE選項插入零日期。在非嚴格模式，可以接受該日期，但會生成警告

  • ERROR_FOR_DIVISION_BY_ZERO：
    在嚴格模式，在INSERT或UPDATE過程中，如果被零除(或MOD(X，0))，則產生錯誤(否則為警告)。如果未給出該模式，被零除時MySQL返回NULL。如果用到INSERT IGNORE或UPDATE IGNORE中，MySQL生成被零除警告，但操作結果為NULL。

  • NO_AUTO_CREATE_USER
    防止GRANT自動建立新使用者，除非還指定了密碼。

  • NO_ENGINE_SUBSTITUTION：
    如果需要的儲存引擎被禁用或未編譯，那麼丟擲錯誤。不設定此值時，用預設的儲存引擎替代，並丟擲一個異常

  • NO_AUTO_VALUE_ON_ZERO
    該值影響自增長列的插入。預設設定下，插入0或NULL代表生成下一個自增長值。如果使用者希望插入的值為0，而該列又是自增長的，那麼這個選項就有用了。

  • PIPES_AS_CONCAT
    將"||"視為字串的連線操作符而非或運算子，這和Oracle資料庫是一樣是，也和字串的拼接函式Concat想類似

  • ANSI_QUOTES
    啟用ANSI_QUOTES後，不能用雙引號來引用字串，因為它被解釋為識別符

據說是MySQL5.0以上版本支援三種sql_mode模式：ANSI、TRADITIONAL和STRICT_TRANS_TABLES。

1、ANSI模式：寬鬆模式，更改語法和行為，使其更符合標準SQL。對插入資料進行校驗，如果不符合定義型別或長度，對資料型別調整或截斷儲存，報warning警告。

將當前資料庫模式設定為ANSI模式：

mysql> set @@sql_mode=ANSI;

2、TRADITIONAL模式：嚴格模式，當向mysql資料庫插入資料時，進行資料的嚴格校驗，保證錯誤資料不能插入，報error錯誤，而不僅僅是警告。用於事物時，會進行事物的回滾。 註釋：一旦發現錯誤立即放棄INSERT/UPDATE。如果你使用非事務儲存引擎，這種方式不是你想要的，因為出現錯誤前進行的資料更改不會“滾動”，結果是更新“只進行了一部分”。

將當前資料庫模式設定為TRADITIONAL模式：

mysql> set @@sql_mode=TRADITIONAL;

3、STRICT_TRANS_TABLES模式：嚴格模式，進行資料的嚴格校驗，錯誤資料不能插入，報error錯誤。如果不能將給定的值插入到事務表中，則放棄該語句。對於非事務表，如果值出現在單行語句或多行語句的第1行，則放棄該語句。

將當前資料庫模式設定為STRICT_TRANS_TABLES模式：

mysql> set @@sql_mode=STRICT_TRANS_TABLES;

沒有最好與最壞的模式，只有最合適的模式。需要根據自己的實際情況去選擇那個最適合的模式！！！

參考：https://blog.csdn.net/ccccalculator/article/details/70432123
https://www.cnblogs.com/cekong/p/10117814.html
https://www.cnblogs.com/kiko2014551511/p/11527480.html

相關文章