Detailed description of mysql SQL _MODE configuration

Recently, I took over a running project and encountered a problem when pulling it to the local machine for running. When I inserted the database, the error Incorrect integer value: "for column 'id' at row 1 was reported.

Literally, the auto-increment primary key ID is written as null, leading to insertion failure. The original server runs normally. It should be an environment problem.

Print the following SQL statement:

Insert into 'config' ('id', 'key', 'value') VALUES (", 'AAA', 'BBB ');

It's awkward. Normally, the auto-incrementing primary key should not be written. I don't know what the program was thinking.

An error is also reported when you directly execute an SQL statement. It can be seen that this statement is incorrect. My local mysql version is 5.7. After compilation, the configuration is rarely changed, except when you run other projects, another change to SQL _mode may be due to this problem. I did not go into details at that time, so I checked it carefully.

Let's talk about how to solve the problem first, and then copy the information I found online.

I checked my local. cnf: I wrote SQL _MODE, which is empty by default, as follows: SQL _mode = NO_ENGINE_SUBSTITUTION and STRICT_TRANS_TABLES. I changed SQL _MODE to SQL _mode = 'no _ ENGINE_SUBSTITUTION.

The following are configurable items for online replication of SQL _MODE:

STRICT_TRANS_TABLES: In this mode, if a value cannot be inserted into a transaction table (for example, the storage engine of the table is InnoDB, the interruption of the current operation does not affect non-transaction tables (for example, the storage engine of the table is MyISAM ).

ALLOW_INVALID_DATES: This option does not fully check the validity of the date. It only checks whether the month is between 1 and 1 ~ Between 12, whether the date is between 1 ~ Between 31. This mode is only valid for DATE and DATETIME types, but not for TIMESTAMP, because TIMESTAMP always requires a valid input.

ANSI_QUOTES: after ANSI_QUOTES is enabled, the string cannot be referenced using double quotation marks because it will be interpreted as an identifier.

ERROR_FOR_DIVISION_BY_ZERO: During the INSERT or UPDATE process, if the data is divided by zero (or MOD (X, 0), an error is generated (otherwise, it is a warning ). If this mode is not provided, MySQL returns NULL when data is divided by zero. If insert ignore or update ignore is used, MySQL generates a zero division warning, but the operation result is NULL.

This article is from the Ruesin blog
HIGH_NOT_PRECEDENCE NOT: the priority of operators is expressions. For example, NOT a BETWEEN B AND c is interpreted as NOT (a BETWEEN B AND c). In some old MySQL versions, the previous expression is interpreted as (NOT) BETWEEN B AND c. Enable HIGH_NOT_PRECEDENCE SQL Mode to obtain results with higher priority from earlier versions.

IGNORE_SPACE: there is a space between the function name and the Bracket. This option does not seem to be of any benefit except for some additional troubles. To access a database, table, or column name that is saved as a keyword, you must reference this option. For example, if a table contains the user column and MySQL database has the user function, the user is interpreted as a function. If you want to select the user column, you need to reference it.

NO_AUTO_CREATE_USER: prohibit GRANT from creating users with blank passwords.

NO_AUTO_VALUE_ON_ZERO: This option affects column auto-increment insertion. By default, insert 0 or NULL to generate the next auto-growth value. If you want to insert a value of 0, and the column is auto-incrementing, this option is useful.

NO_BACKSLASH_ESCAPES: the backslash "\" serves as a common character rather than an escape character.

NO_DIR_IN_CREATE: Ignore all options of index directory and data directory when creating a table.

NO_ENGINE_SUBSTITUTION: if the required storage engine is disabled or not compiled, an error is thrown. By default, it is replaced by the default storage engine and an exception is thrown.

NO_UNSIGNED_SUBTRACTION: As mentioned earlier, if this option is enabled, the two UNSIGNED types are subtracted and the SIGNED type is returned.

NO_ZERO_DATE: In non-strict mode, an invalid date such as "0000-00-00 00:00:00" can be inserted. MySQL database only throws one warning. When this option is enabled, the MySQL database does not allow zero-date insertion. An error is thrown instead of a warning.

NO_ZERO_IN_DATE: in strict mode, zero date and month are not allowed. Formats such as "2011-00-01" and "2011-01-00" are not allowed. MySQL directly throws an error instead of a warning when the format of zero date or month is used.

ONLY_FULL_GROUP_BY: for group by aggregation operations, if the column in SELECT is not in group by, this SQL statement is invalid because Column a is not in the GROUP BY clause,

PAD_CHAR_TO_FULL_LENGTH: For CHAR fields, do not truncate empty data. Empty data is automatically filled with data with a value of 0 × 20. First, let's look at the performance of the MySQL database by default.

PIPES_AS_CONCAT: Regards "|" as the concatenation operator rather than the OR operator of the string, which is the same as that of the Oracle database and similar to the Concat function of the string concatenation function.

REAL_AS_FLOAT: Treats REAL as a synonym for FLOAT, rather than a synonym for DOUBLE.

STRICT_ALL_TABLES: strict mode is enabled for all engine tables. (STRICT_TRANS_TABLES only enables the strict mode for tables that support transactions ).

In strict mode, the current operation is terminated once any operation data has problems. For non-transaction engines that enable the STRICT_ALL_TABLES option, data may stay in an unknown state. This may not be a situation that all non-transaction engines are willing to see, so you need to be very careful about the potential impact of this option.

The following SQL _MODE settings are a combination of the options discussed earlier:

ANSI: equivalent to the combination of REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, and IGNORE_SPACE.

ORACLE: equivalent to the combination of PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, and NO_AUTO_CREATE_USER.

Ruesin.com
TRADITIONAL: equivalent to STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO,

The combination of NO_AUTO_CREATE_USER and NO_ENGINE_SUBSTITUTION.

MSSQL: equivalent to the combination of PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, and NO_FIELD_OPTIONS.

DB2: equivalent to the combination of PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, and NO_FIELD_OPTIONS.

MYSQL323: equivalent to the combination of NO_FIELD_OPTIONS and HIGH_NOT_PRECEDENCE.

MYSQL40: equivalent to the combination of NO_FIELD_OPTIONS and HIGH_NOT_PRECEDENCE.

MAXDB: equivalent to the combination of PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, and NO_AUTO_CREATE_USER.

The SQL _MODE setting is actually a risky setting, because some illegal operations can be allowed in this setting, for example, NULL can be inserted into a field NOT NULL, you can also insert invalid dates, such as 2012-12-32 ". Therefore, it is strongly recommended that developers set this value to a strict mode in the production environment, so that some problems can be discovered in the database design and development phase, if such problems are found after running the database in the production environment, the cost of modification will become very huge. In addition, setting SQL _MODE correctly can also perform some constraints checks.

Please leave the minimum copyright to the blogger. Thank you!