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

周小董發表於2020-10-24

問題: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

相關文章