SQL mode for MySQL

MySQL after the 5.6 version of the default SQL mode is Strict_trans_tables, the rigor and consistency of data more than the previous version to be improved.

First, view the current SQL mode

Mysql> SELECT @ @sql_mode; +--------------------------------------------+| @ @sql_mode                                 |+--------------------------------------------+| Strict_trans_tables,no_engine_substitution |+--------------------------------------------+1 row in Set (0.00 sec)

second, the specified mode
5.6 After the default my.cnf file mode configuration is Sql_mode=no_engine_substitution,strict_trans_tables, so start specifying this configuration file will automatically take these two parameter values
The first parameter value no_engine_substitution, refers to does not support the ENGINE of SQL will be error, support Engin can show engines view the second parameter value Strict_trans_tables, refers to the strict mode of the current session , which is different from the previous void, that is, the less restrictive pattern
There are other interesting parameters, such as No_auto_create_user, can refer to the official website

Iii. Examples
1. In strict mode

Mysql> SELECT @ @sql_mode; +--------------------------------------------+| @ @sql_mode                                 |+--------------------------------------------+| Strict_trans_tables,no_engine_substitution |+--------------------------------------------+1 row in Set (0.00 sec) Mysql> CREATE TABLE Tbl_kenyon (ID int null,vname varchar (6)); Query OK, 0 rows affected (0.12 sec) mysql> insert into Tbl_kenyon values (1, ' 123456 '); Query OK, 1 row Affected (0.00 sec) mysql> INSERT into Tbl_kenyon values (1, ' 1234567 '); ERROR 1406 (22001): Data too long for column ' VName ' in row 1mysql> insert into Tbl_kenyon values (' ', ' 123456 '); ERROR 1366 (HY000): Incorrect integer value: ' for column ' id ' at row 1mysql> insert into Tbl_kenyon values (NULL, ' 1234 56 '); Query OK, 1 row affected (0.01 sec) mysql> select * from tbl_kenyon;+------+--------+| ID   | vname  |+------+--------+|    1 | 123456 | | NULL | 123456 |+------+--------+2 rows in Set (0.00 sec)

2. In non-strict mode

mysql> SET @ @sql_mode = '; Query OK, 0 rows Affected (0.00 sec) mysql> SELECT @ @sql_mode; +------------+|            @ @sql_mode |+------------+| |+------------+1 Row in Set (0.00 sec) mysql> INSERT into Tbl_kenyon values (99, ' 123456 '); Query OK, 1 row Affected (0.00 sec) mysql> INSERT into Tbl_kenyon values (1, ' 1234567 '); Query OK, 1 row affected, 1 Warning (0.00 sec) mysql> INSERT into Tbl_kenyon values (', ' 123456 '); Query OK, 1 row affected, 1 Warning (0.00 sec) mysql> Show warnings;+---------+------+-------------------------------- ----------------------+| Level | Code | Message |+---------+------+-------------------------------------------------- ----+| Warning | 1366 | Incorrect integer value: ' for column ' id ' at row 1 |+---------+------+------------------------------------------------ ------+1 Row in Set (0.00 sec) mysql> SELECT * from tbl_kenyon;+------+--------+| ID |    VName |+------+--------+| 1 | 123456 | | NULL | 123456 | | 99 |    123456 | | 1 |    123456 | | 0 | 123456 |+------+--------+5 rows in Set (0.00 sec)

Iv. Application Scenarios
Each of the two models have pros and cons, usually the production will be set up strict mode, can guarantee the integrity of the data;
Non-strict mode is not necessarily bad, in scenarios where data consistency is not strict, some ignore warning actions may be more convenient, such as non-critical data import of different versions of databases:
mysql> load Data infile '/data/source_data/trace/tbl_msg.sql ' into tabletbl_msg;
ERROR 1366 (HY000): Incorrect integer value: ' NULL ' for column ' pid ' at row 1

To import a different mode:
Mysql> SET @ @session. sql_mode= ";
Query OK, 0 rows Affected (0.00 sec)

mysql> load Data infile '/data/source_data/trace/tbl_msg. sql ' into tabletbl_msg;
Query OK, 23525 rows affected, 65535 warnings (2.80 sec)
records:23525 deleted:0 skipped:0 warnings:185707

v. References:
Https://dev.mysql.com/doc/refman/5.6/en/sql-mode.html

SQL mode for MySQL