MySQL's Sql_mode mode

The MySQL database has an environment variable sql_mode, which defines the SQL syntax that MySQL should support, data validation, and so on! We can view the Sql_mode used by the current database in the following ways:

first, the meaning of the Sql_mode value

Only_full_group_by	For a group by aggregation operation, if the column in select does not appear in group by, the SQL is considered illegal because the column is not in the GROUP BY clause
Strict_trans_tables	In this mode, if a value cannot be inserted into a transaction table, the current operation is interrupted and no restrictions are made on the non-transactional table
no_zero_in_date
no_zero_date
Error_for_division_by_zero
No_auto_create_user	Prevents grant from automatically creating new users unless a password is also specified.
No_engine_substitution	Throws an error if the required storage engine is disabled or not compiled. When this value is not set, the default storage engine is substituted and an exception is thrown.

mysql5.0 the above version supports three Sql_mode modes:

ANSI Mode	Relaxed mode, check the inserted data, if not conform to the definition type or length, the data type adjustment or truncation save, reported warning warning.
Traditional mode	Strict mode, when inserting data into the MySQL database, the strict verification of data, to ensure that the error data can not be inserted, reported error errors. When used in things, things are rolled back.
Strict_trans_tables mode	Strict mode, strict data validation, error data can not be inserted, reported error errors.

Two, ANSI mode

In ANSI mode, when data is inserted and the column length requirement is not met, the data is also inserted successfully, but the field that exceeds the column length is truncated, and a warning warning is reported. For example:

SET @ @sql_mode=ANSI; CREATE TABLE testtable( NAME VARCHAR(2), PASSWORD VARCHAR(2) ); INSERT into testtable VALUES(' 11111111 ',' 222222222222 ');

Operation Result:

Three, Strict_trans_tables mode

In strict_trans_tables mode, when inserting data, MySQL will strictly check the data, when found that the insertion column value does not meet the requirements, directly report error errors, ensure that the error data can not be inserted into the database. For example:

SET = strict_trans_tables ;     INSERT into testtable values< Span style= "color:navy;" > ( 11111111 ' , ' 222222222222 ' );

Operation Result:

Four, traditional mode

The results of traditional mode execution with Strict_trans_tables mode are consistent in this case.

Comments:

Discard insert/update As soon as errors are found. If you use a non-transactional storage engine, this is not what you want because the data changes made before the error do not "scroll" and the result is "only part of the update".

Attention:

The Sql_mode set here are at the session level. Alternatively, you can modify the My.ini file directly

MySQL's Sql_mode mode