About Mysql SQL mode

The

MySQL server works in different SQL modes and can be applied in different ways for different clients. In this way, the application can tailor the server operation to suit its needs. Such patterns define the SQL syntax that MySQL should support, and what validation checks should be performed on the data. This makes it easier to use MySQL in many different environments, along with other database servers. You can use the "--sql-mode=" modes "" option to set the default SQL mode by starting mysqld. Starting with MySQL 4.1, you can also start with Set [session| GLOBAL] sql_mode= ' modes ' statement, changing the mode by setting the Sql_mode variable.

Usually after MySQL is installed under Linux, the default Sql-mode value is empty, in which case MySQL performs an irregular check, such as a date field can be inserted in ' 0000-00-00 00:00:00 ' and if the length of the field to be inserted exceeds the length defined by the column, then MySQL will not terminate the operation, but will automatically truncate the subsequent character to continue the insert operation, as in the following example:
mysql> CREATE TABLE T5 (c1 char (3));
Mysql> INSERT into T5 values (' ABCD ');
Mysql> SELECT * from T5;
+------+
| c1 |
+------+
| abc |
+------+
1 row in Set (0.00 sec)

We found that the inserted characters were automatically truncated, but if we meant to I hope if the length exceeds the limit error, then we can set Sql_mode to Strict_trans_tables, as follows:
Mysql> set session sql_mode= ' Strict_trans_tables '
So we do the same thing again, and MySQL tells us that the inserted value is too long and the operation is terminated as follows:
mysql> insert into T5 values (' ABCD ');
ERROR 1406 (22001): Data too long for column ' C1 ' at row 1

Sql_mode values that are used frequently

Sql_mode Value	Description
Ansi	Change the syntax and behavior to make it more compliant with standard SQL.
Strict_trans_tables	If the given value cannot be inserted into the transaction table, the statement is discarded. For non-transactional tables, if the value appears in line 1th of a single statement or multiline statement, the statement is discarded. A more detailed description is given later in this section.
Traditional	Make MySQL behaves like a "traditional" SQL database system. A simple description of the pattern is "give an error instead of a warning" when an incorrect value is inserted in the column. Note: Once you find an error, discard insert/update immediately. 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".

Note: If you set the value of Sql_mode to the next two values (that is, we mean strict mode), then when inserting or updating an incorrect value in the column, MySQL will give an error and discard the insert/update operation. These two modes are recommended in our general application instead of using the default NULL or ANSI mode. However, it is important to note that if the database is running in strict mode and your storage engine does not support transactions, there is a risk of data inconsistency, such as two DML statements in a set of SQL, if there is a problem in the following, but the previous operation succeeded, Then MySQL cannot roll back the previous operation. So setting up Sql_mode requires the application to weigh the pros and cons to get a suitable choice.

There are a lot of sql_mode values, which are no longer described here, and can be consulted in the relevant manuals.

SQL Mode and portability

If there is a need for data migration between MySQL and other heterogeneous databases, then the following combination of Sql_mode settings can achieve the corresponding effect:

Database	Sql_mode Value
DB2	Pipes_as_concat, Ansi_quotes, Ignore_space, No_key_options, No_table_options, no_field_options
MAXDB	Pipes_as_concat, Ansi_quotes, Ignore_space, No_key_options, No_table_options, No_field_options, NO_AUTO_CREATE_USER
MSSQL	Pipes_as_concat, Ansi_quotes, Ignore_space, No_key_options, No_table_options, no_field_options
ORACLE	Pipes_as_concat, Ansi_quotes, Ignore_space, No_key_options, No_table_options, No_field_options, NO_AUTO_CREATE_USER
POSTGRESQL	Pipes_as_concat, Ansi_quotes, Ignore_space, No_key_options, No_table_options, no_field_options

SQL mode and data validation

SQL Mode can also implement data validation and transfer functions such as:

1. validation date data legitimacy.
4. Disables the backslash character (' \ ') as an exit character within a string. When No_backslash_escapes mode is enabled, the backslash becomes a normal character.
5. will | | Treated as a string join operator (+) (same as concat ()), not as or.