Comment définir le mode SQL par défaut de MySQL

Mode SQL

Le mode SQL par défaut de MySQL 5.7 inclut ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER et NO_ENGINE_SUBSTITUTION. ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, and NO_ENGINE_SUBSTITUTION。

这是MySQL官网的原文描述：“These modes were added to the default SQL mode in MySQL 5.7: The ONLY_FULL_GROUP_BY and STRICT_TRANS_TABLES modes were added in MySQL 5.7.5. The NO_AUTO_CREATE_USER mode was added in MySQL 5.7.7. The ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, and NO_ZERO_IN_DATE modes were added in MySQL 5.7.8. For additional discussion regarding these changes to the default SQL mode value, see SQL Mode Changes in MySQL 5.7.”

show sql mode

SELECT @@GLOBAL.sql_mode;

SELECT @@SESSION.sql_mode;

set sql mode

设置为GLOBAL，那么所有的客户端都会受到影响，不过要拥有SUPER权限才能进行设置，也就是root用户，设置SESSION，那么受影响的只是当前的连接会话。

SET GLOBAL sql_mode ='ONLY_FULL_GROUP_BY'

SET SESSION sql_mode ='ONLY_FULL_GROUP_BY'

下面我们就针对默认设置的这几种SQL mode进行详细的讲解，其他的哪些大家可以去官网参考。

docs.oracle.com/cd/E17952_0…

默认的SQL mode

ONLY_FULL_GROUP_BY

设置了这个值，如果使用GROUP BY，在SELECT后面出现的字段，在GROUP BY后面必须出现，不然报错如下

Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'blue.shop.price' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

如下使用的是MySQL默认的sql_mode

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

那么下面的语句就会报错，因为GROUP BY后面只有一个字段，而SELECT * 是查出所有字段，所以就报错。

SELECT * FROM shop GROUP BY article

这样写就不会报错

SELECT * FROM shop GROUP BY article , dealer , price

不过我们不可能使用一个GROUP BY，后面还要跟着所有字段，显然不合理，那么就应该将其关闭，只需要将其去掉就行

STRICT_TRANS_TABLES

MySQL的严格模式可以控制如何处理INSERT或UPDATE语句中的无效或缺失的值。一个值可能因多种原因无效。例如，它可能具有列的错误数据类型，或者它可能超出了范围。缺少非空列的值没有在定义中明确指定默认值，则插入的新行会出现该问题。

比如我们的某个字段设置不能为NULL，而我们插入的数据这个字段为NULL，那么就不能通过，就会报错如下：

1364 - Field 'dealer' doesn't have a default value

那么这个问题要怎么解决呢？我相信这个问题大家经常遇到，一般是我们在插入数据的时候实体的属性没有赋值，所以导致这个问题，所以我们会去检查代码，然后给属性赋值，另外一种做法就是去除STRICT_TRANS_TABLES,这样就不会进行校验，不过是极其不推荐这样做的，因为要我们要保证数据的完整性，所以必须在代码层面做好工作。

NO_ZERO_IN_DATE

NO_ZERO_IN_DATE模式会影响服务器是否允许年部分不为零但月或日部分为0的日期。(该模式影响日期，如“2010-00-01”或“2010-01-00”，但不影响“0000-00-00”。要控制服务器是否允许'0000-00-00'，请使用NO_ZERO_DATE模式。)NO_ZERO_IN_DATE的效果还取决于是否启用严格SQL模式，如果没有启用严格SQL模式STRICT_TRANS_TABLES，那么启用了NO_ZERO_IN_DATE也没用。

如下SQL的日期月和日为0，启用了严格模式STRICT_TRANS_TABLES和NO_ZERO_IN_DATE，那么就会报错。

INSERT INTO `blue`.`shop` (`article`, `dealer` ,`price`,`date`) VALUES ('商品5', '5', 5.00, '2022-00-00');

1292 - Incorrect datetime value: '2022-00-00' for column 'date' at row 1

去除严格模式STRICT_TRANS_TABLES和NO_ZERO_IN_DATE就不会报错。

NO_ZERO_DATE

上面的NO_ZERO_IN_DATE可以插入'0000-00-00'，如果使用了严格模式STRICT_TRANS_TABLES

Voici la description originale du site officiel de MySQL : "Ces modes ont été ajoutés au mode SQL par défaut dans MySQL 5.7 : Les modes ONLY_FULL_GROUP_BY et STRICT_TRANS_TABLES ont été ajoutés dans MySQL 5.7.5 . Le mode NO_AUTO_CREATE_USER a été ajouté dans MySQL 5.7.7. Les modes ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE et NO_ZERO_IN_DATE. Les modes ont été ajoutés dans MySQL 5.7.8. Pour une discussion supplémentaire concernant ces modifications apportées à la valeur du mode SQL par défaut, voir Modifications du mode SQL dans MySQL 5.7. clients Tous seront affectés, mais vous devez disposer des autorisations SUPER pour le configurer, c'est-à-dire que l'utilisateur root définit SESSION, alors seule la session de connexion en cours sera affectée.

INSERT INTO `blue`.`shop` (`article`,dealer ,`price`,`date`) VALUES ('商品5', '5', MOD(10,0), '0000-00-00');

Ci-dessous, nous expliquerons en détail les modes SQL par défaut. Vous pouvez vous référer au site officiel pour d'autres informations.

docs.oracle.com/cd/E17952_0…

Le mode SQL par défaut

ONLY_FULL_GROUP_BY

définit cette valeur Si GROUP BY est utilisé, les champs qui apparaissent après SELECT doivent apparaître après GROUP BY, sinon l'erreur sera. rapporté comme suit

L'expression n°3 de la liste SELECT n'est pas dans la clause GROUP BY et contient la colonne non agrégée 'blue.shop.price' qui ne dépend pas fonctionnellement des colonnes de la clause GROUP BY ; ceci est incompatible avec sql_mode=only_full_group_by

🎜 ; 🎜Utiliser comme suit est le mode sql_mode par défaut de MySQL🎜🎜🎜ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION🎜🎜🎜Ensuite, l'instruction suivante signalera une erreur car il n'y a qu'un seul champ après GROUP BY , et SELECT * trouve tous les champs, donc signaler une erreur. 🎜

SELECT price / 0  FROM shop

🎜Écrire comme ça ne signalera pas d'erreur🎜

CREATE TABLE store ( `name` VARCHAR ( 255 ) DEFAULT NULL ) ENGINE = InnoDBTest

🎜Cependant, il nous est impossible d'utiliser un GROUP BY, suivi de tous les champs, ce qui est évidemment déraisonnable, il doit donc être fermé, supprimez-le simplement🎜🎜STRICT_TRANS_TABLES🎜 🎜 Le mode strict de MySQL contrôle la façon dont les valeurs invalides ou manquantes dans les instructions INSERT ou UPDATE sont traitées. Une valeur peut être invalide pour plusieurs raisons. Par exemple, le type de données de la colonne peut être incorrect ou il peut être hors plage. Ce problème se produit pour les nouvelles lignes insérées dans lesquelles la valeur d'une colonne non nulle est manquante et où la valeur par défaut n'est pas explicitement spécifiée dans la définition. 🎜🎜Par exemple, si l'un de nos paramètres de champ ne peut pas être NULL et que le champ des données que nous insérons est NULL, alors il ne passera pas et une erreur sera signalée comme suit : 🎜🎜🎜1364 - Le champ « concessionnaire » ne fonctionne pas 'pas de valeur par défaut🎜 🎜🎜Alors comment résoudre ce problème ? Je pense que tout le monde rencontre souvent ce problème. Généralement, l'attribut de l'entité ne se voit pas attribuer de valeur lorsque nous insérons des données, ce qui provoque ce problème, nous allons donc vérifier le code puis attribuer une valeur à l'attribut. Une autre façon est de le supprimer. STRICT_TRANS_TABLES , aucune vérification ne sera effectuée de cette manière, mais c'est extrêmement déconseillé, car nous devons garantir l'intégrité des données, nous devons donc faire du bon travail au niveau du code. Le mode 🎜🎜NO_ZERO_IN_DATE🎜🎜NO_ZERO_IN_DATE détermine si le serveur autorise les dates où la partie année est différente de zéro mais la partie mois ou jour est 0. (Ce mode affecte les dates telles que « 2010-00-01 » ou « 2010-01-00 », mais pas « 0000-00-00 ». Pour contrôler si le serveur autorise « 0000-00-00 », utilisez le mode NO_ZERO_DATE. .) L'effet de NO_ZERO_IN_DATE dépend également de l'activation ou non du mode SQL strict STRICT_TRANS_TABLES, il est inutile d'activer NO_ZERO_IN_DATE. 🎜🎜La date, le mois et le jour du SQL suivant sont 0, et les modes stricts STRICT_TRANS_TABLES et NO_ZERO_IN_DATE sont activés, alors une erreur sera signalée. 🎜

SHOW CREATE TABLE store

🎜🎜1292 - Valeur datetime incorrecte : '2022-00-00' pour la colonne 'date' à la ligne 1🎜🎜🎜La suppression du mode strict STRICT_TRANS_TABLES et NO_ZERO_IN_DATE ne fonctionne pas travail Une erreur sera signalée. 🎜🎜NO_ZERO_DATE🎜🎜Le NO_ZERO_IN_DATE ci-dessus peut insérer '0000-00-00'. Si le mode strict STRICT_TRANS_TABLES et NO_ZERO_DATE sont utilisés, alors '0000-00-00' ne peut pas être inséré. 🎜🎜ERROR_FOR_DIVISION_BY_ZERO🎜🎜Pour INSERT ou UPDATE, si le dividende est 0, une erreur se produira et les données ne pourront pas être insérées. Il en va de même pour MOD(N,M)🎜

CREATE TABLE `store` (
  `name` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

🎜Pour SELECT, si le dividende est 0, alors NULL sera renvoyé, il en va de même pour MOD(N,M). 🎜

SHOW ENGINES;

🎜🎜Message d'erreur : 1365 - Division par 0🎜🎜🎜NO_AUTO_CREATE_USER🎜🎜Vous ne pouvez pas utiliser la commande Grant pour créer un utilisateur avec un mot de passe vide. 🎜

NO_ENGINE_SUBSTITUTION

如果指定了NO_ENGINE_SUBSTITUTION，我们在创建表或者修改表的时候，如果去指定了不存在或者不支持的存储引擎，那么就会报错，无法创建和修改，如果没有配置NO_ENGINE_SUBSTITUTION，那么就会将我们指定的存储引擎（不支持或者不存在）的存储引擎替换为默认的存储引擎，MySQL5.7后的默认存储引擎为InnoDB，所以就会自动设置为InnoDB。

如下我们创建表，将存储引擎设置为一个不存在的InnoDBTest，因为我们去除了NO_ENGINE_SUBSTITUTION，所以不会报错，并且会替换成默认的InnoDB

创建sql

CREATE TABLE store ( `name` VARCHAR ( 255 ) DEFAULT NULL ) ENGINE = InnoDBTest

查看创建过程

SHOW CREATE TABLE store

结果

CREATE TABLE `store` (
  `name` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

MySQL存储引擎

SHOW ENGINES;

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!