MySQL 5.7之關於SQL_MODE的設定

2022-08-24 18:01:35

一、sql_mode用來解決下面幾類問題

通過設定sql mode,可以完成不同嚴格程度的資料校驗，有效保障資料準備性。

通過設定sql mode 為寬鬆模式，來保證大多數sql符合標準的sql語法，這樣應用在不同資料庫之間進行遷移時，則不需要對業務sql進行較大的修改，可以很方便的遷移到目標資料庫中。

二、MySQL5.7中sql_mode引數預設值的說明（如下為MySQL 5.7.27版本）

ONLY_FULL_GROUP_BY

對於使用 GROUP BY 進行查詢的SQL，不允許 SELECT 部分出現 GROUP BY 中未出現的欄位，也就是 SELECT 查詢的欄位必須是 GROUP BY 中出現的或者使用聚合函數的或者是具有唯一屬性的。

create table test(name varchar(10),value int);
insert into test values ('a',1),('a',20),('b',23),('c',15),('c',30);
#預設情況是可能會寫出無意義或錯誤的聚合語句：
SET sql_mode='';
select * from test group by name;
select value,sum(value) from test group by name;
# 使用該模式後，寫法必須標準
SET sql_mode='ONLY_FULL_GROUP_BY';
select name,sum(value) from test group by name;
-- 錯誤寫法則報錯
select value,sum(value) from test group by name;
# 報錯終止
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

該選項針對事務性儲存引擎生效，對於非事務性儲存引擎無效，該選項表示開啟strict sql模式。在strict sql模式下，在INSERT或者UPDATE語句中，插入或者更新了某個不符合規定的欄位值，則會直接報錯中斷操作

create table test(value int(1));
SET sql_mode=''; #預設只要第一個值
 
insert into test(value) values('a'),(1); #不報錯
insert into test(value) values(2),('a'); #不報錯
select * from test;
+------------+
| value      |
+------------+
|          0 |
|          1 |
|          2 |
|          0 |
+------------+
#後面刪除表不再說明！
drop table test; 
create table test(value int(1));
 
SET sql_mode='STRICT_TRANS_TABLES'; #每個值都判斷
 
insert into test(value) values('a'),(1);
#報錯，第一行'a'錯誤。
ERROR 1366 (HY000): Incorrect integer value: 'a' for column 'value' at row 1

NO_ZERO_IN_DATE

MySQL中插入的時間欄位值，不允許日期和月份為零

create table test(value date);
SET sql_mode='';
insert into test(value) values('2020-00-00'); #結果為 '2020-00-00'
 
SET sql_mode='NO_ZERO_IN_DATE';
insert into test(value) values('2021-00-00'); #不符合,轉為 '0000-00-00'

NO_ZERO_DATE

MySQL中插入的時間欄位值，不允許插入 ‘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');
# 報錯終止
ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'value' at row 1

ERROR_FOR_DIVISION_BY_ZERO

INSERT或者UPDATE語句中，如果資料被0除，則出現警告（非strict sql模式下）或者錯誤(strict sql模式下)。

當該選項關閉時，數位被0除，得到NULL且不會產生警告
當該選項開啟且處於非strict sql模式下，數位被0除，得到NULL但是會產生警告
當該選項開啟且處於strict sql模式下，數位被0除，產生錯誤且中斷操作

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); 
#報錯：ERROR 1365 (22012): Division by 0

NO_AUTO_CREATE_USER

禁止GRANT建立密碼為空的使用者

SET sql_mode='';
grant all on test.* to test01@'localhost';  #不報錯（無需要設定密碼）
SET sql_mode='NO_AUTO_CREATE_USER';
# 報錯
ERROR 1133 (42000): Can't find any matching row in the user table

#正確 寫法，需要設定密碼
grant all on test.* to test01@'localhost' identified by 'test01...';

NO_ENGINE_SUBSTITUTION

在使用CREATE TABLE或者ALTER TABLE語法執行儲存引擎的時候，如果設定的儲存引擎被禁用或者未編譯，會產生錯誤。

# 檢視當前支援的儲存引擎
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'; # 轉為預設儲存引擎
+------------+--------+
| table_name | engine |
+------------+--------+
| test       | InnoDB |
+------------+--------+
SET sql_mode='NO_ENGINE_SUBSTITUTION';
create table test(id int) ENGINE=test;
# 報錯
ERROR 1286 (42000): Unknown storage engine 'test'

三、sql_mode 設定和修改

方式一：這是一個可修改全域性變數

> 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"
......

總結

SQL_MODE在非嚴格模式下，會出現很多意料不到的結果。建議線上開啟嚴格模式。但對於線上老的環境，如果一開始就執行在非嚴格模式下，切忌直接調整，畢竟兩者的差異性還是相當巨大。

官方預設的SQL_MODE一直在發生變化，MySQL 5.5, 5.6, 5.7就不盡相同，但總體是趨嚴的，在對資料庫進行升級時，其必須考慮預設的SQL_MODE是否需要調整。

在進行資料庫遷移時，可通過調整SQL_MODE來相容其它資料庫的語法。

以上為個人經驗，希望能給大家一個參考，也希望大家多多支援it145.com。

MySQL 5.7之關於SQL_MODE的設定

目錄

一、sql_mode用來解決下面幾類問題

二、MySQL5.7中sql_mode引數預設值的說明（如下為MySQL 5.7.27版本）

三、sql_mode 設定和修改

總結

熱門文章