How to set the default sql mode of MySQL

SQL mode

The default SQL mode of MySQL 5.7 includes 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.

This is the original description from the MySQL official website: "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

Set to GLOBAL, then all clients will be affected, but you must have SUPER permission to set it, that is, the root user, set SESSION, then only the current connection session will be affected.

SET GLOBAL sql_mode ='ONLY_FULL_GROUP_BY'

SET SESSION sql_mode ='ONLY_FULL_GROUP_BY'

Below we will explain in detail the default SQL modes. You can refer to the official website for other information.

docs.oracle.com/cd/E17952_0…

Default SQL mode

ONLY_FULL_GROUP_BY

Set this value, if you use GROUP BY, in The fields that appear after SELECT must appear after GROUP BY, otherwise the error will be as follows

#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

The following uses MySQL's default sql_mode

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE ,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

Then the following statement will report an error, because there is only one field after GROUP BY, and SELECT * is to find all fields, so an error will be reported.

SELECT * FROM shop GROUP BY article

Writing like this will not report an error

SELECT * FROM shop GROUP BY article , dealer , price

However, it is impossible for us to use a GROUP BY followed by all fields. It is obviously unreasonable, so it should be turned off and just need to Just remove it

STRICT_TRANS_TABLES

MySQL's strict mode can control how to handle invalid or missing values ​​​​in INSERT or UPDATE statements. A value may be invalid for many reasons. For example, it might have the wrong data type for the column, or it might be out of range. This problem occurs for new rows inserted where the value of a non-null column is missing and the default value is not explicitly specified in the definition.

For example, one of our field settings cannot be NULL, and the field of the data we insert is NULL, then it will not pass and an error will be reported as follows:

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

So how to solve this problem? I believe everyone often encounters this problem. Generally, the attribute of the entity is not assigned a value when we insert data, which causes this problem, so we will check the code and then assign a value to the attribute. Another way is to remove STRICT_TRANS_TABLES , In this way, verification will not be performed, but it is extremely not recommended, because we need to ensure the integrity of the data, so we must do a good job at the code level.

NO_ZERO_IN_DATE

The NO_ZERO_IN_DATE mode affects whether the server allows dates where the year part is non-zero but the month or day part is 0. (This mode affects dates such as '2010-00-01' or '2010-01-00', but not '0000-00-00'. To control whether the server allows '0000-00-00', use NO_ZERO_DATE Mode.) The effect of NO_ZERO_IN_DATE also depends on whether strict SQL mode is enabled. If strict SQL modeSTRICT_TRANS_TABLES is not enabled, then enabling NO_ZERO_IN_DATE is useless.

The date month and day of the following SQL are 0, and strict mode STRICT_TRANS_TABLES and NO_ZERO_IN_DATE are enabled, then an error will be reported.

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

Remove strict modeSTRICT_TRANS_TABLES and NO_ZERO_IN_DATE will not report an error.

NO_ZERO_DATE

The NO_ZERO_IN_DATE above can insert '0000-00-00'. If strict mode STRICT_TRANS_TABLES and NO_ZERO_DATE are used, then '0000-00 cannot be inserted. -00'.

ERROR_FOR_DIVISION_BY_ZERO

For INSERT or UPDATE, if the dividend is 0, an error will occur and the data cannot be inserted. The same is true for MOD(N,M)

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

For SELECT, if the dividend is 0, NULL will be returned, as will MOD(N,M).

SELECT price / 0  FROM shop

Error message: 1365 - Division by 0

NO_AUTO_CREATE_USER

You cannot use the grant command to create a user with a blank password.

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;

The above is the detailed content of How to set the default sql mode of MySQL. For more information, please follow other related articles on the PHP Chinese website!