Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to modify the SQL_MODE of MySQL". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to modify the SQL_MODE of MySQL".

Check the sql_mode of our current database

Mysql > select @ @ sql_mode

+-+

| | @ @ sql_mode |

+-+

| | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |

+-+

1 row in set (0.00 sec)

Different versions of default SQL_MODE

MySQL 5.5Null

MySQL 5.6: non-strict mode

NO_ENGINE_SUBSTITUTION

MySQL 5.7: strict mode

ONLY_FULL_GROUP_BY

STRICT_TRANS_TABLES,NO_ZERO_IN_DATE

NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

MySQL 8.0: strict mode

ONLY_FULL_GROUP_BY

STRICT_TRANS_TABLES,NO_ZERO_IN_DATE

NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

How to modify SQL_MODE

SQL_MODE can be modified both at the global level and at the session level. You can specify that multiple MODE,MODE be separated by commas.

Global level

Set global sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES'

Session level

Set session sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES'

Complete list of SQL_MODE

ALLOW_INVALID_DATES

In strict mode, the detection of the date is more strict, it must be effective. If the MODE is turned on, the detection of month and day will be relatively relaxed. Among them, month only needs to be between 1 and 12, and day only needs to be between 1 and 31, regardless of whether it is valid or not, such as' 2004-02-31 'below

ERROR_FOR_DIVISION_BY_ZERO

The MODE determines the processing logic with a divisor of 0, and the actual effect also depends on whether strict mode is turned on.

1. Turn on strict mode, turn on the MODE, insert 1Universe 0, and the error will be reported directly.

two。 Only strict mode is enabled, the MODE is not enabled, the insertion of 1 warning,1/0 0 is allowed, and there is no hint that the warning,1/0 will eventually be converted to NULL.

3. Strict mode is not turned on, only the MODE is enabled, allowing the insertion of 1max 0, but prompting warning.

4. The strict mode is not enabled, and the MODE is not enabled, allowing the insertion of 1 warning 0, and does not prompt for the same as 2.

NO_ENGINE_SUBSTITUTION

When the MODE is turned on, when creating a table, if the specified storage engine does not exist or does not support it, it will directly prompt "ERROR".

If not, only "Warning" will be prompted and the default storage engine will be used.

NO_ZERO_DATE

The MODE will affect the insertion of '0000-0000'. The actual effect also depends on whether strict mode is turned on.

1. When strict mode is turned on and the MODE is turned on at the same time, the '0000-00' insertion is not allowed.

two。 Only strict mode is enabled, the MODE is not enabled, the value of '0000-0000' is allowed to be inserted, and the warning is not prompted.

3. Strict mode is not enabled, only the MODE is enabled, allowing the insertion of the value of '0000-0000', but prompting warning.

4. Strict mode is not enabled, and the MODE is not enabled, the value of '0000-0000' is allowed to be inserted, and warning is not prompted.

NO_ZERO_IN_DATE

Similar to NO_ZERO_DATE, except that NO_ZERO_DATE is for '0000-00-00', while NO_ZERO_IN_DATE is for a date on which the year is not zero, but the month or day is 0, for example, '2010-00-01' or '2010-01-00'.

ONLY_FULL_GROUP_BY

When the MODE is turned on, only grouping columns and aggregate functions can appear in the SELECT list.

STRICT_ALL_TABLES

Turn on strict mode for all tables.

STRICT_TRANS_TABLES

Turn on strict mode for the transaction table.

NO_AUTO_CREATE_USER

Prior to MySQL 8.0, direct authorization implicitly created users.

In MySQL 8.0, the grant statement is no longer allowed to create users implicitly, so the MODE does not exist in 8.0 either.

In fact, the MODE only forbids grant statements without a "identified by" clause, but it does not prohibit grant statements with "identified by" clauses.

Common combinations of SQL_MODE

In MySQL 5.7you can also set SQL_MODE to ANSI, DB2, MAXDB, MSSQL, MYSQL323, MYSQL40, ORACLE, POSTGRESQL, TRADITIONAL.

In fact, these MODE are just a combination of the above MODE, designed to be compatible with other databases.

In MySQL 8.0, only two combinations of ANSI and TRADITIONAL are supported.

ANSI: equivalent to

REAL_AS_FLOAT, PIPES_AS_CONCAT,ANSI_QUOTES, IGNORE_SPACE,ONLY_FULL_GROUP_BY

TRADITIONAL: equivalent to

STRICT_TRANS_TABLES, STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION .

Example:

Mysql > set session sql_mode='TRADITIONAL'

Query OK, 0 rows affected (0.00 sec)

Mysql > show session variables like 'sql_mode'

+-+ +

| | Variable_name | Value |

+-+ +

| | sql_mode | STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_ENGINE_SUBSTITUTION |

+-+ +

1 row in set (0.01 sec)

Thank you for reading, the above is the content of "how to modify the SQL_MODE of MySQL", after the study of this article, I believe you have a deeper understanding of how to modify the SQL_MODE of MySQL, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.