Detailed explanation of the use of MySQL sql_mode

Preface

I believe that those who have read the previous article "MySQL Case: A Data Loss Tragedy" should realize that sql_mode is a very critical configuration. Next, I will bring a detailed analysis of this configuration item.

sql_mode explained

sql_mode directly affects SQL syntax support and data validation. It contains many options, among which the default value of version 5.7 is

"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"

, it is generally not recommended to modify it.

The most important options

The most important options of sql_mode include the following three:

(1) ANSI: This option determines the SQL syntax support. If it is set to ANSI, the standard SQL syntax will be more adhered to.

(2) STRICT_TRANS_TABLES: This option determines data validation. For transactional storage engines, when an illegal value appears, the transaction fails and rolls back. For non-transactional storage engines, if the illegal value appears in the first row, the transaction fails. If the illegal value appears in the middle, the illegal value is adjusted and an alarm is thrown.

(3) TRADITIONAL: This option determines the performance consistent with traditional relational databases; for some abnormal operations, failure errors are directly reported instead of warning prompts.

All options

sql_mode also includes the following options:

(4) ALLOW_INVALID_DATES: This option decides not to perform strict date validation; it only verifies whether the month range is 1-12 and the day range is 1-31, but does not verify whether the specific date is valid. For example, the illegal date 2020-04-31 is allowed.

(5) ANSI_QUOTES: This option determines the quote character; it allows the double quote " to be used as a quote character, just like the back quote `.

(6) ERROR_FOR_DIVISION_BY_ZERO: This option determines the return value of division by zero. If not enabled, the return value of division by zero is null and no warning is generated. If enabled but in non-strict mode, the return value of division by zero is null and a warning is generated. If enabled and in strict mode, division by zero will result in an error.

(7) HIGH_NOT_PRECEDENCE: This option determines the priority of the not operation; when enabled, NOT a BETWEEN b AND c is parsed as NOT (a BETWEEN b AND c); in some older versions, NOT a BETWEEN b AND c is parsed as (NOT a) BETWEEN b AND c.

(8) IGNORE_SPACE: This option determines whether to ignore the spaces between the function name and the parentheses. If enabled, count (*) will not report an error.

(9) NO_AUTO_CREATE_USER: This option determines that the grant statement will not automatically create a user; it is deprecated and the grant statement will not create a user.

(10) NO_AUTO_VALUE_ON_ZERO: This option determines the generation of auto-increment columns. Generally speaking, if you insert 0 or null into an auto-increment column, the system will automatically generate the next auto-increment column. If enabled, inserting 0 into an auto-increment column will retain the original value of 0, and inserting null will automatically generate the next auto-increment column.

(11) NO_BACKSLASH_ESCAPES: This option determines the effect of the backslash \; when enabled, the backslash \ is no longer used as an escape character, but as a normal character.

(12) NO_DIR_IN_CREATE: This option determines whether to ignore all INDEX DIRECTORY and DATA DIRECTORY instructions when creating a table; this option is only effective in the slave database.

(13) NO_ENGINE_SUBSTITUTION: This option determines whether a non-existent/unsupported storage engine is specified when creating a table, and it is automatically converted to the default storage engine.

(14) NO_FIELD_OPTIONS: Deprecated.

(15) NO_KEY_OPTIONS: Deprecated.

(16) NO_TABLE_OPTIONS: Deprecated.

(17) NO_UNSIGNED_SUBTRACTION: Normally, when subtracting integers (one of which is unsigned), the result is unsigned, and an error is reported if the result is negative. With this option enabled, negative numbers can be handled normally.

(18) NO_ZERO_DATE: This option determines whether '0000-00-00' can be inserted. If it is not enabled, '0000-00-00' can be inserted without warning. If it is enabled but in non-strict mode, '0000-00-00' can be inserted but a warning will be generated. If it is enabled and in non-strict mode, '0000-00-00' cannot be inserted and an error will be reported directly.

(19) NO_ZERO_IN_DATE: This option determines whether the month and date can be 00. If it is not enabled, the month and date can be 00 and no warning will be generated. If it is enabled but in non-strict mode, the month and date can be 00 but a warning will be generated. If it is enabled and in non-strict mode, an error will be reported if the month and date cannot be 00.

(20) ONLY_FULL_GROUP_BY: This option determines the non-aggregate fields following select/having/order by and must appear in the group by clause.

(21) PAD_CHAR_TO_FULL_LENGTH: Normally, when querying a char type field, the trailing empty data will be trimmed. After this option is enabled, when querying a char type field, the trailing empty data will not be trimmed.

(22) PIPES_AS_CONCAT: This option determines whether the || symbol is treated as a string concatenation operator rather than as a synonym for OR.

(23) REAL_AS_FLOAT: This option determines whether REAL is treated as a synonym for FLOAT rather than a synonym for DOUBLE.

(24) STRICT_ALL_TABLES: This option determines data validation. For transactional storage engines, when an illegal value appears, the transaction will fail and roll back. For non-transactional storage engines, if the illegal value appears in the first row, the transaction will fail. If the illegal value appears in the middle, the previous operation will succeed and the subsequent operation will directly report an error, resulting in a partial success and partial failure of the transaction.

Summarize

Through the above learning, I believe everyone has a more detailed understanding of the various options of sql_mode; regarding how to set sql_mode, my personal suggestions are as follows:

(1) For versions 5.5/5.6, it is recommended to set sql_mode according to the default value of 5.7;

(2) For version 5.7, keep the default value of sql_mode;

(3) For version 8.0, sql_mode can also remain at the default value.

The sql_mode setting allows MySQL to run in various modes very flexibly, but it also brings various risks. As MySQL is widely used in various important systems, it is recommended to strictly review the settings of sql_mode and standardize the management of development codes. In fact, this can also be seen from the official default values. With the iteration of MySQL versions, the sql_mode settings are becoming more and more stringent.

The above is the detailed content of the detailed explanation of the use of MySQL sql_mode. For more information on the use of MySQL sql_mode, please pay attention to other related articles on 123WORDPRESS.COM!