MySQL預設的sql mode怎麼設定

SQL mode

MySQL 5.7預設的SQL mode包含ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES#, NO_ZERO_IN_DATE, NO_ZERO_ ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, and NO_ENGINE_SUBSTITUTION。

這是MySQL官網的原文描述：「These modes were added to the default SQL mode in MySQL 5.7: The

ONLY_FULL_GROUP_BY and STRICT_TRANS_TABLES modes were added in My 5.77 .5. The NO_AUTO_CREATE_USER mode was added in MySQL 5.7.7. The ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, and NO_ZERO_IN_DATE modes were added in MySQL 5.7.8. For additional discussion regarding these changes to the default SQL mode value, see SQL Mode Changes in MySQL 5.7.”

show sql mode

SELECT @@GLOBAL.sql_mode;

SELECT @@SESSION.sql_mode;

setsql# mode

設定為GLOBAL，那麼所有的客戶端都會受到影響，不過要擁有SUPER權限才能進行設置，也就是root用戶，設置SESSION，那麼受影響的只是當前的連接會話。

SET GLOBAL sql_mode ='ONLY_FULL_GROUP_BY'

SET SESSION sql_mode ='ONLY_FULL_GROUP_BY'

下面我們就針對預設設定的這幾種SQL mode進行詳細的講解，其他的哪些大家可以去官網參考。

docs.oracle.com/cd/E17952_0…

預設的SQL mode

ONLY_FULL_GROUP_BY

設定了這個值，如果使用GROUP BY，在SELECT後面出現的字段，在GROUP BY後面必須出現，不然報錯如下

Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'blue.shop.price' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

如下使用的是MySQL預設的sql_mode# ,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

那麼下面的語句就會報錯，因為GROUP BY後面只有一個字段，而SELECT * 是查出所有字段，所以就報錯。

SELECT * FROM shop GROUP BY article

這樣寫就不會報錯

SELECT * FROM shop GROUP BY article , dealer , price

不過我們不可能使用一個GROUP BY，後面還要跟著所有字段，顯然不合理，那麼就應該將其關閉，只需要將其去掉就行

STRICT_TRANS_TABLES

MySQL的嚴格模式可以控制如何處理INSERT或UPDATE語句中的無效或缺少的值。一個值可能因多種原因無效。例如，它可能具有列的錯誤資料類型，或者它可能超出了範圍。缺少非空列的值沒有在定義中明確指定預設值，則插入的新行會出現該問題。

例如我們的某個欄位設定不能為NULL，而我們插入的資料這個欄位為NULL，那麼就不能通過，就會報錯如下：

1364 - Field ' dealer' doesn't have a default value

那麼這個問題要怎麼解決呢？我相信這個問題大家常常遇到，一般是我們在插入資料的時候實體的屬性沒有賦值，所以導致這個問題，所以我們會去檢查程式碼，然後給屬性賦值，另外一種做法就是去除

STRICT_TRANS_TABLES

,這樣就不會進行校驗，不過是極度不建議這樣做的，因為要我們要保證資料的完整性，所以必須在程式碼層面做好工作。

NO_ZERO_IN_DATE

NO_ZERO_IN_DATE模式會影響伺服器是否允許年部分不為零但月或日部分為0的日期。 (此模式影響日期，如“2010-00-01”或“2010-01-00”，但不影響“0000-00-00”。若要控制伺服器是否允許'0000-00-00'，請使用NO_ZERO_DATE模式。)NO_ZERO_IN_DATE的效果也取決於是否啟用嚴格SQL模式，如果沒有啟用嚴格SQL模式

STRICT_TRANS_TABLES

，那麼啟用了NO_ZERO_IN_DATE也沒用。

如下SQL的日期月和日為0，啟用了嚴格模式STRICT_TRANS_TABLES

和NO_ZERO_IN_DATE，那麼就會報錯。

INSERT INTO `blue`.`shop` (`article`, `dealer` ,`price`,`date`) VALUES ('商品5', '5', 5.00, '2022-00-00');

1292 - Incorrect datetime value: '2022-00-00' for column 'date' at row 1

移除嚴格模式

STRICT_TRANS_TABLES

移除嚴格模式STRICT_TRANS_TABLES

移除嚴格模式

STRICT_TRANS_TABLES和NO_ZERO_IN_DATE

就不會報錯。

NO_ZERO_DATE

上面的NO_ZERO_IN_DATE可以插入'0000-00-00'，如果使用了嚴格模式

STRICT_TRANS_TABLES

和NO_ZERO_DATE，那麼就不可以插入'0000-00 -00'。

ERROR_FOR_DIVISION_BY_ZERO

對於INSERT或UPDATE中，如果被除數為0，那麼就會產生錯誤，資料無法插入，MOD(N,M)也是一樣

INSERT INTO `blue`.`shop` (`article`,dealer ,`price`,`date`) VALUES ('商品5', '5', MOD(10,0), '0000-00-00');

對於SELECT，如果被除數為0，那麼就會回傳NULL，MOD(N,M)也是一樣。

SELECT price / 0  FROM shop

### 報錯訊息： 1365 - Division by 0##########NO_AUTO_CREATE_USER######不能使用grant指令建立密碼為空的使用者。 ###

NO_ENGINE_SUBSTITUTION

如果指定了NO_ENGINE_SUBSTITUTION，我们在创建表或者修改表的时候，如果去指定了不存在或者不支持的存储引擎，那么就会报错，无法创建和修改，如果没有配置NO_ENGINE_SUBSTITUTION，那么就会将我们指定的存储引擎（不支持或者不存在）的存储引擎替换为默认的存储引擎，MySQL5.7后的默认存储引擎为InnoDB，所以就会自动设置为InnoDB。

如下我们创建表，将存储引擎设置为一个不存在的InnoDBTest，因为我们去除了NO_ENGINE_SUBSTITUTION，所以不会报错，并且会替换成默认的InnoDB

创建sql

CREATE TABLE store ( `name` VARCHAR ( 255 ) DEFAULT NULL ) ENGINE = InnoDBTest

查看创建过程

SHOW CREATE TABLE store

结果

CREATE TABLE `store` (
  `name` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

MySQL存储引擎

SHOW ENGINES;

以上是MySQL預設的sql mode怎麼設定的詳細內容。更多資訊請關注PHP中文網其他相關文章！