MySQL's SQL model

SQL model (SQL mode):
Restrict user behavior by defining certain rules, and define the corresponding processing mechanism.

Common models:
Ansi
Relaxed mode, check the inserted data, if not conform to the definition type or length, the data type adjustment or truncation save, reported warning warning.

Traditional
Strict mode, when inserting data into the MySQL database, the strict verification of data, to ensure that the error data can not be inserted, reported error errors. When used in things, things are rolled back.

Strict_trans_tables
Strict mode, strict data validation, does not allow the insertion of illegal data into a table of supporting things to report error errors.

Strict_all_tables
If not set, all illegal values are allowed and a warning message is returned. Settings are not allowed to fill in and return an error whenever a data rule is violated.

ANSI QUOTES
Double quotes and anti-quotes work the same, can only be used to refer to field names/table names, etc., and single quotation marks can only be referenced in strings. The default 3 in MySQL is free to refer to.

Ignore_space
Ignore extra spaces in built-in functions


To view the default model:
Mysql>show GLOBAL VARIABLES like ' Sql_mode ';

To modify a model:
Mysql>set SESSION sql_mode=mode1,...; To modify the current session level
Or
To modify the global level #vim the/ETC/MY.CNF configuration file
Sql_mode=mode1,...

Instance:
1. View the values for global and session Sql_mode
Mysql>show GLOBAL VARIABLES like ' Sql_mode ';
Mysql>show VARIABLES like ' Sql_mode ';
Or
Mysql>select @ @global. Sql_mode;
Mysql>select @ @session. Sql_mode;

2. Modify the value of Sql_mode to Strict_all_tables and verify that the dynamic adjustment is in effect.
Mysql>set GLOBAL sql_mode= ' strict_all_tables ';
Mysql>select @ @session. Sql_mode;
Mysql>select @ @global. Sql_mode;
Strict_all_tables
mysql>quit;

#mysql-uroot-p
Mysql>select @ @session. Sql_mode;
Strict_all_tables

Mysql>set SESSION sql_mode= ' strict_trans_tables ';
Mysql>select @ @session. Sql_mode;
Strict_trans_tables
Mysql>[email protected] @global. sql_moed;
Strict_all_tables
mysql>quit;

#mysql-uroot-p
Mysql>select @ @session. Sql_mode;
Strict_all_tables

This article is from "Small Private blog" blog, please be sure to keep this source http://ggvylf.blog.51cto.com/784661/1679327

MySQL's SQL model