mysql 8 的 sql mode 查詢及設置

MYSQL 升級後, 出現了 Field 'XXX' doesn't have a default value。 是因為新版的 mysql 開啟了嚴格模式(STRICT_TRANS_TABLES)，以下指令可以查詢並修改相關內容。

查詢 sql_mode

mysql> select @@GLOBAL.sql_mode;
+-------------------------------------------------------------------------------
| @@GLOBAL.sql_mode
+-------------------------------------------------------------------------------
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_
+-------------------------------------------------------------------------------
1 row in set (0.00 sec)

mysql> select @@SESSION.sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@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 |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

設置 sql_mode

mysql> set GLOBAL sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> set SESSION sql_mode='';
Query OK, 0 rows affected (0.00 sec)

在 ubuntu 的 mysql 8 , 可直接修改 mysql 設定檔

nano /etc/mysql/mysql.conf.d/mysqld.cnf

最後一行加入

sql_mode = '' 

即可

sql_mode常用值： 

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

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

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

NO_ZERO_IN_DATE：在嚴格模式下，排除日期和月份為零

NO_ZERO_DATE：設置該值，mysql數據庫重複插入零日期，插入零日期會引發錯誤而不是警告。

ERROR_FOR_DIVISION_BY_ZERO：在INSERT或UPDATE過程中，如果數據被零除，則產生錯誤而不警告。如果未命名該模式，那麼數據被零除時MySQL返回NULL

NO_AUTO_CREATE_USER：禁止GRANT創建密碼為空的用戶

NO_ENGINE_SUBSTITUTION：如果需要的存儲引擎被替換或未編譯，則引發錯誤。不設置此值時，用替換的存儲引擎替代，並拋出一個異常

PIPES_AS_CONCAT：將“ ||”視為串行的連接操作符而非或運算符，這和Oracle數據庫是一樣的，也和串聯的拼接函數Concat相類似

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