Comment définir SQL_MODE dans MySQL 5.7

sql_mode est une variable qui est facilement négligée. La valeur par défaut dans 5.5 est nulle. Avec ce paramètre, certaines opérations illégales peuvent être autorisées, comme autoriser l'insertion de certaines données illégales.

Ce paramètre de valeur a été renforcé dans la version 5.6 et la version 5.7 a accordé plus d'attention aux règles de sécurité. Cette valeur par défaut est le mode strict

1 sql_mode est utilisé pour résoudre les types de problèmes suivants

En définissant le mode SQL, une rigueur différente. les niveaux peuvent être atteints. La vérification des données garantit efficacement la disponibilité des données.

En définissant le mode SQL sur le mode détendu, nous garantissons que la plupart des SQL sont conformes à la syntaxe SQL standard. De cette façon, lorsque l'application est migrée entre différentes bases de données, il n'est pas nécessaire d'apporter des modifications majeures au SQL métier, et cela. peut être facilement migré vers la base de données cible.

2. Description de la valeur par défaut du paramètre sql_mode dans MySQL5.7 (ce qui suit est la version MySQL 5.7.27)

• ONLY_FULL_GROUP_BYONLY_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

Pour SQL qui utilise GROUP BY pour la requête, ce n'est pas autorisé. Les champs qui n'apparaissent pas dans GROUP BY apparaissent dans la partie SELECT, c'est-à-dire que les champs de la requête SELECT doivent apparaître dans GROUP BY ou utiliser des fonctions d'agrégation ou avoir des attributs uniques.

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

Cette option ne fonctionne que pour les moteurs de stockage transactionnels et n'est pas valide pour les moteurs de stockage non transactionnels. Sa fonction est d'activer le mode SQL strict. En mode SQL strict, si une valeur de champ qui ne répond pas aux exigences est insérée ou mise à jour dans une instruction INSERT ou UPDATE, une erreur sera directement signalée pour interrompre l'opération🎜

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

🎜🎜🎜NO_ZERO_IN_DATE🎜 🎜🎜🎜MySQL La valeur du champ horaire insérée dans MySQL ne permet pas à la date et au mois d'être zéro🎜

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

🎜🎜🎜NO_ZERO_DATE🎜🎜🎜🎜La valeur du champ horaire insérée dans MySQL ne permet pas l'insertion du «0000-00-00’ Date 🎜rrreee🎜🎜🎜ERROR_FOR_DIVISION_BY_ZERO🎜🎜🎜🎜 Dans une instruction INSERT ou UPDATE, si les données sont divisées par 0, un avertissement (en SQL non strict mode) ou une erreur (en mode SQL strict) apparaîtra. 🎜🎜🎜🎜Lorsque cette option est désactivée, le nombre est divisé par 0, NULL est obtenu et aucun avertissement n'est généré🎜🎜🎜🎜Lorsque cette option est activée et en mode SQL non strict, le nombre est divisé par 0 , NULL est obtenu mais un avertissement est généré🎜 🎜🎜🎜Lorsque cette option est activée et en mode sql strict, le nombre est divisé par 0, une erreur se produit et l'opération est interrompue🎜🎜🎜rrreee🎜🎜🎜 NO_AUTO_CREATE_USER🎜🎜🎜🎜Il est interdit à GRANT de créer un mot de passe vide Utilisateur 🎜rrreee🎜🎜🎜NO_ENGINE_SUBSTITUTION🎜🎜🎜🎜Lors de l'utilisation de la syntaxe CREATE TABLE ou ALTER TABLE pour exécuter le moteur de stockage , si le moteur de stockage défini est désactivé ou n'est pas compilé, une erreur se produira. 🎜rrreee🎜3. Paramétrage et modification de sql_mode🎜🎜Méthode 1 : Il s'agit d'une variable globale modifiable🎜rrreee🎜Méthode 2 : En modifiant le fichier de configuration (nécessite un redémarrage pour prendre effet)🎜rrreee

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!