SQL Mode usage in Mysql

I. Introduction to Mysql SQL Mode

Generally, the MySQL server can work in different SQL modes and apply these modes to different clients in different ways. In this way, applications can customize server operations to meet their own needs. This mode defines the SQL syntax that MySQL should support and the validation check on the data. In this way, it is easier to use MySQL with other database servers in many different environments. You can use the "-- SQL-mode =" modes "option to set the default SQL mode by starting mysqld. Starting from MySQL 4.1, you can also use the SET [SESSION | GLOBAL] SQL _mode = 'modes 'statement to change the mode by setting the SQL _mode variable.

Generally, after mysql is installed in linux, the default SQL-mode value is null. In this case, mysql performs a non-strict check, for example, you can insert a value such as '2014-00-00 00:00:00 'For a date field. If the length of the field to be inserted exceeds the length defined in the column, mysql will not terminate the operation, the system automatically truncates the subsequent characters to continue the insert operation, as shown 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 are automatically truncated, but if we want to report an error if the length exceeds the limit, we can set SQL _mode to STRICT_TRANS_TABLES, as shown below:

mysql> set session sql_mode='STRICT_TRANS_TABLES'

Then we execute the same operation, mysql will tell us that the inserted value is too long and the operation is terminated, as shown below:

mysql> insert into t5 values('abcd');ERROR 1406 (22001): Data too long for column 'c1' at row 1

Commonly used SQL _mode value:

SQL _mode Value	Description
ANSI	Modify 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, discard the statement. For non-transaction tables, if the value appears in the 1st rows of a single-row or multi-row statement, the statement is discarded. A more detailed description is provided later in this section.
TRADITIONAL	Make MySQL acts like a "traditional" SQL database system. A simple description of this mode is to "give an error rather than warning" when an incorrect value is inserted in the column ". Note: if an error is found, immediately discard INSERT/UPDATE. If you use a non-transaction storage engine, this method is not what you want, because the data changes made before an error are not "rolled", and the result is that the update is "only a part ".

Note: If you set the SQL _mode value to the following two values (that is, the strict mode we call), when an incorrect value is inserted or updated in the column, mysql will give an error and discard the insert/update operation. We recommend that you use these two modes in our general applications, instead of using the default null or ANSI mode. However, if the database runs in strict mode and your storage engine does not support transactions, there is a risk of data inconsistency, for example, if there are two dml statements in a group of SQL statements, mysql cannot roll back the previous operation if a problem occurs but the previous operation is successful. Therefore, setting SQL _mode requires the application staff to weigh the various gains and losses to get a proper choice.

There are still many SQL _mode values. I will not describe them here. Please refer to the relevant manual.

Ii. SQL Mode and portability

If you need to port data between mysql and other heterogeneous databases, the following SQL _mode combination 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

Iii. SQL Mode and data validation

SQL Mode also supports data verification and transfer functions, such:

1. Validity of validity period data.
2. During the INSERT or UPDATE process, if it is divided by zero (or MOD (X, 0), an error is generated.
3. Think of '"' as an identifier quotation mark (''' quotation mark character)
4. Disable the backslash character ('\') as the exit character in the string. Enable NO_BACKSLASH_ESCAPES mode, and the backslash is a common character.
5. Treat | as a String concatenation operator (+) (same as CONCAT (), not OR.