So legen Sie SQL_MODE in MySQL 5.7 fest

sql_mode ist eine Variable, die leicht übersehen wird. Der Standardwert in 5.5 ist null. Unter dieser Einstellung können einige illegale Vorgänge zugelassen werden, beispielsweise das Einfügen einiger illegaler Daten.

Diese Werteinstellung wurde in 5.6 verstärkt und 5.7 hat den Sicherheitsvorschriften mehr Aufmerksamkeit geschenkt. Dieser Wert ist standardmäßig auf den strikten Modus eingestellt.

1 wird verwendet, um die folgenden Arten von Problemen zu lösen Niveaus können erreicht werden. Durch die Datenüberprüfung wird die Datenbereitschaft effektiv sichergestellt.

Durch die Einstellung des SQL-Modus auf den entspannten Modus stellen wir sicher, dass die meisten SQL-Anweisungen der Standard-SQL-Syntax entsprechen. Auf diese Weise müssen bei der Migration der Anwendung zwischen verschiedenen Datenbanken keine größeren Änderungen an der Business-SQL vorgenommen werden können problemlos in die Zieldatenbank migriert werden.

2. Beschreibung des Standardwerts des sql_mode-Parameters in MySQL5.7 (das Folgende ist die MySQL 5.7.27-Version)

ONLY_FULL_GROUP_BY

• 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

这个选项只对事务型存储引擎起作用，对非事务型存储引擎无效，其作用是启用严格 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

  Für SQL, das GROUP BY verwendet Für die Abfrage ist es nicht zulässig, dass Felder, die nicht in GROUP BY erscheinen, im SELECT-Teil erscheinen, das heißt, die Felder in der SELECT-Abfrage müssen in GROUP BY erscheinen oder Aggregatfunktionen verwenden oder eindeutige Attribute haben.

# 查看当前支持的存储引擎
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'

STRICT_TRANS_TABLES

Diese Option funktioniert nur für transaktionale Speicher-Engines und ist für nicht-transaktionale Speicher-Engines ungültig. Ihre Funktion besteht darin, den strikten SQL-Modus zu aktivieren. Wenn im strikten SQL-Modus ein Feldwert, der die Anforderungen nicht erfüllt, in einer INSERT- oder UPDATE-Anweisung eingefügt oder aktualisiert wird, wird direkt ein Fehler gemeldet, der den Vorgang unterbricht

> show variables like '%sql_mode%';
> set @@sql_mode="NO_ENGINE_SUBSTITUTION"
> set session sql_mode='STRICT_TRANS_TABLES';

🎜🎜NO_ZERO_IN_DATE🎜 🎜🎜🎜MySQL Der in MySQL eingefügte Zeitfeldwert lässt nicht zu, dass Datum und Monat Null sind🎜

# vim /etc/my.cnf
[mysqld]
......
sql_mode="NO_ENGINE_SUBSTITUTION"
......

🎜🎜🎜NO_ZERO_DATE🎜🎜🎜🎜Der in MySQL eingefügte Zeitfeldwert lässt das Einfügen nicht zu von ‘0000-00-00’ Datum 🎜rrreee🎜🎜🎜ERROR_FOR_DIVISION_BY_ZERO🎜🎜🎜🎜 Wenn in einer INSERT- oder UPDATE-Anweisung die Daten durch 0 geteilt werden, wird eine Warnung (in nicht striktem SQL) angezeigt oder ein Fehler (im strikten SQL-Modus) wird angezeigt. 🎜🎜🎜🎜Wenn diese Option deaktiviert ist, wird die Zahl durch 0 geteilt, NULL wird erhalten und es wird keine Warnung generiert.🎜🎜🎜🎜Wenn diese Option aktiviert ist und sich im nicht-strikten SQL-Modus befindet, wird die Zahl durch 0 geteilt , NULL wird erhalten, es wird jedoch eine Warnung generiert🎜 🎜🎜🎜Wenn diese Option aktiviert ist und im strikten SQL-Modus die Zahl durch 0 geteilt wird, tritt ein Fehler auf und der Vorgang wird unterbrochen🎜🎜🎜rrreee🎜🎜🎜 NO_AUTO_CREATE_USER🎜🎜🎜🎜Es ist GRANT untersagt, ein leeres Passwort zu erstellen. Benutzer 🎜rrreee🎜🎜🎜NO_ENGINE_SUBSTITUTION🎜🎜🎜🎜Bei Verwendung der CREATE TABLE- oder ALTER TABLE-Syntax zum Ausführen der Speicher-Engine Wenn die festgelegte Speicher-Engine deaktiviert oder nicht kompiliert ist, tritt ein Fehler auf. 🎜rrreee🎜3. sql_mode-Einstellung und -Änderung🎜🎜Methode 1: Dies ist eine veränderbare globale Variable🎜rrreee🎜Methode 2: Durch Ändern der Konfigurationsdatei (erfordert einen Neustart, um wirksam zu werden)🎜rrreee

Das obige ist der detaillierte Inhalt vonSo legen Sie SQL_MODE in MySQL 5.7 fest. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!