MySQL 5.7之關(guān)于SQL_MODE的設(shè)置
sql_mode是個(gè)容易被忽視的變量,在5.5默認(rèn)值是空值,在這種設(shè)置下是可以允許一些非法操作的,比如允許一些非法數(shù)據(jù)的插入。
在5.6中強(qiáng)化了該值設(shè)置,5.7中更注重了安全規(guī)范性,這個(gè)值默認(rèn)為嚴(yán)格模式
一、sql_mode用來解決下面幾類問題
通過設(shè)置sql mode,可以完成不同嚴(yán)格程度的數(shù)據(jù)校驗(yàn),有效保障數(shù)據(jù)準(zhǔn)備性。
通過設(shè)置sql mode 為寬松模式,來保證大多數(shù)sql符合標(biāo)準(zhǔn)的sql語法,這樣應(yīng)用在不同數(shù)據(jù)庫之間進(jìn)行遷移時(shí),則不需要對業(yè)務(wù)sql進(jìn)行較大的修改,可以很方便的遷移到目標(biāo)數(shù)據(jù)庫中。
二、MySQL5.7中sql_mode參數(shù)默認(rèn)值的說明(如下為MySQL 5.7.27版本)
ONLY_FULL_GROUP_BY
對于使用 GROUP BY 進(jìn)行查詢的SQL,不允許 SELECT 部分出現(xiàn) GROUP BY 中未出現(xiàn)的字段,也就是 SELECT 查詢的字段必須是 GROUP BY 中出現(xiàn)的或者使用聚合函數(shù)的或者是具有唯一屬性的。
create table test(name varchar(10),value int); insert into test values ('a',1),('a',20),('b',23),('c',15),('c',30); #默認(rèn)情況是可能會寫出無意義或錯(cuò)誤的聚合語句: SET sql_mode=''; select * from test group by name; select value,sum(value) from test group by name; # 使用該模式后,寫法必須標(biāo)準(zhǔn) SET sql_mode='ONLY_FULL_GROUP_BY'; select name,sum(value) from test group by name; -- 錯(cuò)誤寫法則報(bào)錯(cuò) select value,sum(value) from test group by name; # 報(bào)錯(cuò)終止 ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.test.value' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
STRICT_TRANS_TABLES
該選項(xiàng)針對事務(wù)性存儲引擎生效,對于非事務(wù)性存儲引擎無效,該選項(xiàng)表示開啟strict sql模式。在strict sql模式下,在INSERT或者UPDATE語句中,插入或者更新了某個(gè)不符合規(guī)定的字段值,則會直接報(bào)錯(cuò)中斷操作
create table test(value int(1)); SET sql_mode=''; #默認(rèn)只要第一個(gè)值 ? insert into test(value) values('a'),(1); #不報(bào)錯(cuò) insert into test(value) values(2),('a'); #不報(bào)錯(cuò) select * from test; +------------+ | value ? ? ?| +------------+ | ? ? ? ? ?0 | | ? ? ? ? ?1 | | ? ? ? ? ?2 | | ? ? ? ? ?0 | +------------+ #后面刪除表不再說明! drop table test;? create table test(value int(1)); ? SET sql_mode='STRICT_TRANS_TABLES'; #每個(gè)值都判斷 ? insert into test(value) values('a'),(1); #報(bào)錯(cuò),第一行'a'錯(cuò)誤。 ERROR 1366 (HY000): Incorrect integer value: 'a' for column 'value' at row 1
NO_ZERO_IN_DATE
MySQL中插入的時(shí)間字段值,不允許日期和月份為零
create table test(value date); SET sql_mode=''; insert into test(value) values('2020-00-00'); #結(jié)果為 '2020-00-00' ? SET sql_mode='NO_ZERO_IN_DATE'; insert into test(value) values('2021-00-00'); #不符合,轉(zhuǎn)為 '0000-00-00'
NO_ZERO_DATE
MySQL中插入的時(shí)間字段值,不允許插入 ‘0000-00-00’ 日期
create table test(value date); ? SET sql_mode=''; insert into test(value) values('0000-00-00'); #無警告 warning ? SET sql_mode='STRICT_TRANS_TABLES'; insert into test(value) values('0000-00-00'); #無警告 warning ? SET sql_mode='NO_ZERO_DATE'; insert into test(value) values('0000-00-00'); #有警告 warning ? SET sql_mode='NO_ZERO_DATE,STRICT_TRANS_TABLES' insert into test(value) values('0000-00-00'); # 報(bào)錯(cuò)終止 ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'value' at row 1
ERROR_FOR_DIVISION_BY_ZERO
INSERT或者UPDATE語句中,如果數(shù)據(jù)被0除,則出現(xiàn)警告(非strict sql模式下)或者錯(cuò)誤(strict sql模式下)。
- 當(dāng)該選項(xiàng)關(guān)閉時(shí),數(shù)字被0除,得到NULL且不會產(chǎn)生警告
- 當(dāng)該選項(xiàng)開啟且處于非strict sql模式下,數(shù)字被0除,得到NULL但是會產(chǎn)生警告
- 當(dāng)該選項(xiàng)開啟且處于strict sql模式下,數(shù)字被0除,產(chǎn)生錯(cuò)誤且中斷操作
create table test(value int); ? SET sql_mode=''; ? select 10/0; ?#無警告 warning insert into test(value) values(10/0); ? #無警告 warning ? SET sql_mode='STRICT_TRANS_TABLES';? select 10/0; ? #無警告 warning insert into test(value) values(10/0); ?#無警告 warning ? SET sql_mode='ERROR_FOR_DIVISION_BY_ZERO';? select 10/0; ?#有警告 warning insert into test(value) values(10/0); ?#有警告 warning ? SET sql_mode='ERROR_FOR_DIVISION_BY_ZERO,STRICT_TRANS_TABLES'; select 10/0; #有警告 warning insert into test(value) values(10/0);? #報(bào)錯(cuò):ERROR 1365 (22012): Division by 0
NO_AUTO_CREATE_USER
禁止GRANT創(chuàng)建密碼為空的用戶
SET sql_mode=''; grant all on test.* to test01@'localhost'; ?#不報(bào)錯(cuò)(無需要設(shè)置密碼) SET sql_mode='NO_AUTO_CREATE_USER'; # 報(bào)錯(cuò) ERROR 1133 (42000): Can't find any matching row in the user table #正確 寫法,需要設(shè)置密碼 grant all on test.* to test01@'localhost' identified by 'test01...';
NO_ENGINE_SUBSTITUTION
在使用CREATE TABLE或者ALTER TABLE語法執(zhí)行存儲引擎的時(shí)候,如果設(shè)定的存儲引擎被禁用或者未編譯,會產(chǎn)生錯(cuò)誤。
# 查看當(dāng)前支持的存儲引擎 show engines; set sql_mode=''; create table test(id int) ENGINE="test"; Query OK, 0 rows affected, 2 warnings (0.03 sec) select table_name,engine from information_schema.tables where table_schema='test' and table_name='test'; # 轉(zhuǎn)為默認(rèn)存儲引擎 +------------+--------+ | table_name | engine | +------------+--------+ | test ? ? ? | InnoDB | +------------+--------+ SET sql_mode='NO_ENGINE_SUBSTITUTION'; create table test(id int) ENGINE=test; # 報(bào)錯(cuò) ERROR 1286 (42000): Unknown storage engine 'test'
三、sql_mode 設(shè)置和修改
方式一: 這是一個(gè)可修改全局變量
> show variables like '%sql_mode%'; > set @@sql_mode="NO_ENGINE_SUBSTITUTION" > set session sql_mode='STRICT_TRANS_TABLES';
方式二: 通過修改配置文件(需要重啟生效)
# vim /etc/my.cnf [mysqld] ...... sql_mode="NO_ENGINE_SUBSTITUTION" ......
總結(jié)
SQL_MODE在非嚴(yán)格模式下,會出現(xiàn)很多意料不到的結(jié)果。建議線上開啟嚴(yán)格模式。但對于線上老的環(huán)境,如果一開始就運(yùn)行在非嚴(yán)格模式下,切忌直接調(diào)整,畢竟兩者的差異性還是相當(dāng)巨大。
官方默認(rèn)的SQL_MODE一直在發(fā)生變化,MySQL 5.5, 5.6, 5.7就不盡相同,但總體是趨嚴(yán)的,在對數(shù)據(jù)庫進(jìn)行升級時(shí),其必須考慮默認(rèn)的SQL_MODE是否需要調(diào)整。
在進(jìn)行數(shù)據(jù)庫遷移時(shí),可通過調(diào)整SQL_MODE來兼容其它數(shù)據(jù)庫的語法。
以上為個(gè)人經(jīng)驗(yàn),希望能給大家一個(gè)參考,也希望大家多多支持腳本之家。
相關(guān)文章
mysql 記錄不存在時(shí)插入 記錄存在則更新的實(shí)現(xiàn)方法
相信很多人都需要用到這個(gè)語句,請看下文:(在4.1以后的版本才有效)2008-08-08mysql中的find_in_set字符串查找函數(shù)解析
這篇文章主要介紹了mysql中的find_in_set字符串查找函數(shù),具有很好的參考價(jià)值,希望對大家有所幫助。如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2022-08-08Mysql存儲引擎MyISAM的常見問題(表損壞、無法訪問、磁盤空間不足)
這篇文章主要介紹了Mysql存儲引擎MyISAM的常見問題,針對表損壞、無法訪問、磁盤空間不足等問題進(jìn)行解決,感興趣的小伙伴們可以參考一下2016-05-05Mysql GROUP BY查詢每組某值最大的一條數(shù)據(jù)
這篇文章主要介紹了Mysql GROUP BY查詢每組某值最大的一條數(shù)據(jù)問題,具有很好的參考價(jià)值,希望對大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2023-08-08MySQL刪除表數(shù)據(jù)與MySQL清空表命令的3種方法淺析
刪除現(xiàn)有MySQL表非常容易,但是刪除任何現(xiàn)有的表時(shí)要非常小心,因?yàn)閯h除表后丟失的數(shù)據(jù)將無法恢復(fù),下面這篇文章主要給大家介紹了關(guān)于MySQL刪除表數(shù)據(jù)與MySQL清空表命令的3種方法的相關(guān)資料,需要的朋友可以參考下2022-08-08