Mysql SQL Mode usage detailed _mysql

A, Mysql SQL mode Introduction

In general, the MySQL server works in different SQL modes and can apply these patterns in different ways for different clients. This allows applications to tailor server operations to suit their needs. This type of pattern defines 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 to change its mode by setting the Sql_mode variable.

Usually after installing MySQL under Linux, the default Sql-mode value is null, in which case MySQL performs an irregular check, such as Date field can be inserted ' 0000-00-00 00:00:00 ', and if the length of the field you want to insert exceeds the length of the column definition, then MySQL does not terminate the operation, it automatically truncates the following characters to continue the insert operation, as follows:

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 character was automatically truncated, but if we intended to make an error if the length exceeded the limit, then we could set Sql_mode to Strict_trans_tables, as follows:

Mysql> set session sql_mode= ' Strict_trans_tables '

So that we do the same thing again, 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 often used:

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-transaction tables, the statement is discarded if the value appears at line 1th of a single-line statement or multiline statement. 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 inserting an incorrect value into the column. Note: Discard insert/update as soon as you find the error. If you are using a non-transactional storage engine, this is not what you want, because the data changes made prior to the error will not "scroll", and the result is a "partial" update.

Note: If the value of Sql_mode is set to the next two values (that is, the strict mode we say), MySQL will give an error when inserting or updating incorrect values in the column, and discard the insert/update operation. These two modes are recommended in our general application, instead of using the default NULL or ANSI mode. But the point to note is 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 having two DML statements in a group of SQL, if one of the following is a problem, but the previous operation is successful, Then MySQL does not rollback the previous operation. Therefore, it is necessary for the application staff to weigh the pros and cons of setting Sql_mode to get a suitable choice.

There are a lot of sql_mode values, here is no longer tired, you can refer to the relevant manuals.

Second, SQL mode and portability

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

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

Three, SQL mode and data parity

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

1. Validity of date data.
2. During the insert or update process, if 0 is removed (or mod (x,0)), an error
3 is generated. Treats ' ' ' as the identifier quotation mark (' ' quote character ')
4. Disables the backslash character (' \ ') as the exit character within the string. No_backslash_escapes mode is enabled, and the backslash is a normal character.
5. Will | | is treated as a string concatenation operator (+) (same as concat ()) and not as an OR.