SQL mode

今天我們來(lái)分享一下MySQL的SQL mode , 這也是我們比較容易忽略的一點(diǎn)，我們?cè)谝婚_(kāi)始安裝數(shù)據(jù)庫(kù)的時(shí)候其實(shí)就要先考慮要保留哪些SQL mode，去除哪些，合理的配置能夠減少很多不必要的麻煩。

MySQL 5.7默認(rèn)的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, and NO_ENGINE_SUBSTITUTION。

這是MySQL官網(wǎng)的原文描述：“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 MySQL 5.7.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;

set sql mode

設(shè)置為GLOBAL，那么所有的客戶(hù)端都會(huì)受到影響，不過(guò)要擁有SUPER權(quán)限才能進(jìn)行設(shè)置，也就是root用戶(hù)，設(shè)置SESSION，那么受影響的只是當(dāng)前的連接會(huì)話。

SET GLOBAL sql_mode ='ONLY_FULL_GROUP_BY'

SET SESSION sql_mode ='ONLY_FULL_GROUP_BY'

下面我們就針對(duì)默認(rèn)設(shè)置的這幾種SQL mode進(jìn)行詳細(xì)的講解，其他的哪些大家可以去官網(wǎng)參考。

docs.oracle.com/cd/E17952_0…

默認(rèn)的SQL mode

ONLY_FULL_GROUP_BY

設(shè)置了這個(gè)值，如果使用GROUP BY，在SELECT后面出現(xiàn)的字段，在GROUP BY后面必須出現(xiàn)，不然報(bào)錯(cuò)如下

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默認(rèn)的sql_mode

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

那么下面的語(yǔ)句就會(huì)報(bào)錯(cuò)，因?yàn)镚ROUP BY后面只有一個(gè)字段，而SELECT * 是查出所有字段，所以就報(bào)錯(cuò)。

SELECT * FROM shop GROUP BY article 

這樣寫(xiě)就不會(huì)報(bào)錯(cuò)

SELECT * FROM shop GROUP BY article , dealer , price

不過(guò)我們不可能使用一個(gè)GROUP BY，后面還要跟著所有字段，顯然不合理，那么就應(yīng)該將其關(guān)閉，只需要將其去掉就行

STRICT_TRANS_TABLES

嚴(yán)格模式控制MySQL如何處理數(shù)據(jù)更改語(yǔ)句中的無(wú)效或缺失值，如INSERT或UPDATE。一個(gè)值可能因多種原因無(wú)效。例如，它可能具有列的錯(cuò)誤數(shù)據(jù)類(lèi)型，或者它可能超出了范圍。如果要插入的新行不包含定義中沒(méi)有顯式DEFAULT子句的非null列的值，則該值缺失。

比如我們的某個(gè)字段設(shè)置不能為NULL，而我們插入的數(shù)據(jù)這個(gè)字段為NULL，那么就不能通過(guò)，就會(huì)報(bào)錯(cuò)如下：

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

那么這個(gè)問(wèn)題要怎么解決呢？我相信這個(gè)問(wèn)題大家經(jīng)常遇到，一般是我們?cè)诓迦霐?shù)據(jù)的時(shí)候?qū)嶓w的屬性沒(méi)有賦值，所以導(dǎo)致這個(gè)問(wèn)題，所以我們會(huì)去檢查代碼，然后給屬性賦值，另外一種做法就是去除STRICT_TRANS_TABLES,這樣就不會(huì)進(jìn)行校驗(yàn)，不過(guò)是極其不推薦這樣做的，因?yàn)橐覀円ＷC數(shù)據(jù)的完整性，所以必須在代碼層面做好工作。

NO_ZERO_IN_DATE

NO_ZERO_IN_DATE模式會(huì)影響服務(wù)器是否允許年部分不為零但月或日部分為0的日期。(該模式影響日期，如“2010-00-01”或“2010-01-00”，但不影響“0000-00-00”。要控制服務(wù)器是否允許'0000-00-00'，請(qǐng)使用NO_ZERO_DATE模式。)NO_ZERO_IN_DATE的效果還取決于是否啟用嚴(yán)格SQL模式，如果沒(méi)有啟用嚴(yán)格SQL模式STRICT_TRANS_TABLES，那么啟用了NO_ZERO_IN_DATE也沒(méi)用。

如下SQL的日期月和日為0，啟用了嚴(yán)格模式STRICT_TRANS_TABLES和NO_ZERO_IN_DATE，那么就會(huì)報(bào)錯(cuò)。

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

去除嚴(yán)格模式STRICT_TRANS_TABLES和NO_ZERO_IN_DATE就不會(huì)報(bào)錯(cuò)。

NO_ZERO_DATE

上面的NO_ZERO_IN_DATE可以插入'0000-00-00'，如果使用了嚴(yán)格模式STRICT_TRANS_TABLES和NO_ZERO_DATE，那么就不可以插入'0000-00-00'。

ERROR_FOR_DIVISION_BY_ZERO

對(duì)于INSERT或者UPDATE中，如果被除數(shù)為0，那么就會(huì)產(chǎn)生錯(cuò)誤，數(shù)據(jù)無(wú)法插入，MOD(N,M)也是一樣

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

對(duì)于SELECT，如果被除數(shù)為0，那么就會(huì)返回NULL，MOD(N,M)也一樣。

SELECT price / 0  FROM shop

報(bào)錯(cuò)信息： 1365 - Division by 0

NO_AUTO_CREATE_USER

不能使用grant命令創(chuàng)建密碼為空的用戶(hù)。

NO_ENGINE_SUBSTITUTION

如果指定了NO_ENGINE_SUBSTITUTION，我們?cè)趧?chuàng)建表或者修改表的時(shí)候，如果去指定了不存在或者不支持的存儲(chǔ)引擎，那么就會(huì)報(bào)錯(cuò)，無(wú)法創(chuàng)建和修改，如果沒(méi)有配置NO_ENGINE_SUBSTITUTION，那么就會(huì)將我們指定的存儲(chǔ)引擎（不支持或者不存在）的存儲(chǔ)引擎替換為默認(rèn)的存儲(chǔ)引擎，MySQL5.7后的默認(rèn)存儲(chǔ)引擎為InnoDB，所以就會(huì)自動(dòng)設(shè)置為InnoDB。

如下我們創(chuàng)建表，將存儲(chǔ)引擎設(shè)置為一個(gè)不存在的InnoDBTest，因?yàn)槲覀內(nèi)コ薔O_ENGINE_SUBSTITUTION，所以不會(huì)報(bào)錯(cuò)，并且會(huì)替換成默認(rèn)的InnoDB

創(chuàng)建sql

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

查看創(chuàng)建過(guò)程

SHOW CREATE TABLE store

結(jié)果

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

MySQL存儲(chǔ)引擎

SHOW ENGINES;

關(guān)于MySQL的SQL mode，就說(shuō)到這里，我只列舉了MySQL5.7默認(rèn)的幾種并對(duì)其進(jìn)行講解，有興趣的話可以去了解其他的選項(xiàng)

以上就是MySQL默認(rèn)sql_mode淺析的詳細(xì)內(nèi)容，更多關(guān)于MySQL默認(rèn)sql_mode的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

最新評(píng)論