MySQL?5.7之SQL_MODE怎么設(shè)置

這篇“MySQL 5.7之SQL_MODE怎么設(shè)置”文章的知識(shí)點(diǎn)大部分人都不太理解，所以小編給大家總結(jié)了以下內(nèi)容，內(nèi)容詳細(xì)，步驟清晰，具有一定的借鑒價(jià)值，希望大家閱讀完這篇文章能有所收獲，下面我們一起來看看這篇“MySQL 5.7之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ǔ)法，這樣應(yīng)用在不同數(shù)據(jù)庫(kù)之間進(jìn)行遷移時(shí)，則不需要對(duì)業(yè)務(wù)sql進(jìn)行較大的修改，可以很方便的遷移到目標(biāo)數(shù)據(jù)庫(kù)中。

二、MySQL5.7中sql_mode參數(shù)默認(rèn)值的說明（如下為MySQL 5.7.27版本）

• ONLY_FULL_GROUP_BY

對(duì)于使用 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)情況是可能會(huì)寫出無意義或錯(cuò)誤的聚合語(yǔ)句：
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)針對(duì)事務(wù)性存儲(chǔ)引擎生效，對(duì)于非事務(wù)性存儲(chǔ)引擎無效，該選項(xiàng)表示開啟strict sql模式。在strict sql模式下，在INSERT或者UPDATE語(yǔ)句中，插入或者更新了某個(gè)不符合規(guī)定的字段值，則會(huì)直接報(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語(yǔ)句中，如果數(shù)據(jù)被0除，則出現(xiàn)警告（非strict sql模式下）或者錯(cuò)誤(strict sql模式下)。

• 當(dāng)該選項(xiàng)關(guān)閉時(shí)，數(shù)字被0除，得到NULL且不會(huì)產(chǎn)生警告

• 當(dāng)該選項(xiàng)開啟且處于非strict sql模式下，數(shù)字被0除，得到NULL但是會(huì)產(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語(yǔ)法執(zhí)行存儲(chǔ)引擎的時(shí)候，如果設(shè)定的存儲(chǔ)引擎被禁用或者未編譯，會(huì)產(chǎn)生錯(cuò)誤。

# 查看當(dāng)前支持的存儲(chǔ)引擎
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)存儲(chǔ)引擎
+------------+--------+
| 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"
......

以上就是關(guān)于“MySQL 5.7之SQL_MODE怎么設(shè)置”這篇文章的內(nèi)容，相信大家都有了一定的了解，希望小編分享的內(nèi)容對(duì)大家有幫助，若想了解更多相關(guān)的知識(shí)內(nèi)容，請(qǐng)關(guān)注億速云行業(yè)資訊頻道。