SQL Mode and related issues

SQL Mode and related issues 1. Introduction to Mysql SQL Mode 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. After MySQL 4.1 is started, you can 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, instead, the subsequent characters are automatically truncated to continue the insert operation. For 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, however, if we want to report an error if the length exceeds the limit, we can set SQL _mode to STRICT_TRANS_TABLES as follows: mys Ql> set session SQL _mode = 'strict _ TRANS_TABLES 'so that we can perform the same operation again. 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): The SQL _mode values frequently used by Data too long for column 'c1' at row 1 are as follows: the SQL _mode value describes how to change the syntax and behavior of ANSI 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, 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 there is a need to port data between mysql and other heterogeneous databases, the combined settings of the following SQL _mode can achieve the corresponding effect: database SQL _mode values: DB2 sequence, sequence, IGNORE_SPACE, sequence, NO_TABLE_OPTIONS, sequence, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, sequence, IGNORE_SPACE, sequence, NO_TABLE_OPTIONS, and sequence PIPES _ AS_CONCAT, delimiter, IGNORE_SPACE, delimiter, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, delimiter, delimiter, IGNORE_SPACE, delimiter, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS 3. SQL Mode and data validation SQL Mode and transfer functions such: ① validity of valid date data. ② In the INSERT or UPDATE process, if it is divided by zero (or MOD (X, 0 )), an error occurs. ③ "'" is considered as an identifier quotation mark (''' quotation marks). ④ the backslash character (' \ ') is disabled as the exit character in the string. Enable NO_BACKSLASH_ESCAPES mode, and the backslash is a common character. ⑤ Treat | as a String concatenation operator (+) (same as CONCAT (), not OR.