SQL _mode of MySQL

MySQL can run in different SQL mode modes. SQL mode defines the SQL syntax and data verification that MySQL should support!
I. Setting method:

[Mysqld] -- Add the following settings

Transaction-isolation = serializable
SQL-mode = ANSI

Ii. Mode description:
View the default SQL mode:
Select @ SQL _mode;
My database is:
Strict_trans_tables, no_auto_create_user, no_engine_substitution
In this mode, if the inserted Data Length is greater than the defined length, an error is returned!

It is null by default.

Set session SQL _mode = 'real _ as_float, pipes_as_concat, ansi_quotes, ignore_space, ANSI ';
In this mode, when the length of the inserted data is greater than the defined value, it is intercepted and warned, but it can be inserted.
Session indicates that it is valid only for this time
Global: indicates that the connection does not take effect, but it takes effect for the new connection.

Enable no_backslash_escapes mode to make the backslash a common character. When importing data, if there is a backslash in the data, enabling this mode is a good choice.

Enable the pipes_as_cncat mode and regard | as a normal string

Common SQL mode:
SQL mode value description
ANSI: 'real _ as_float, pipes_as_concat, ansi_quotes, ignore_space, and ANSI composite '. This mode makes the syntax and behavior more compliant with standard SQL statements.
Strict_trans_tables: Used in strict mode with transaction and non-transaction tables
Traditional: it is also a strict mode. Errors rather than warnings are given for incorrectly inserted values. Roll back immediately when an error occurs during a transaction.

Under mysql5.0, the default values (SQL mode parameter) include real_as_float, pipes_as_concat, ansi_quotes, gnore_space, and ANSI. In these modes, you can insert data that exceeds the defined length of the field or element data (such as Enum) that is not defined in the field ). However, there will be a warning after the insert (you can use show warnings to view it ). In MySQL or later versions, three SQL mode modes (ANSI, traditional, and strict_trans_tables (strict mode) can be used to solve the following problems:
(1). By setting different SQL modes, data verification can be performed in different strict procedures, effectively ensuring data accuracy.
(2 ). you can set the SQL mode to ANSI to ensure that most SQL statements comply with the standard SQL syntax. Therefore, during migration between different databases, you do not need to modify the business too much. you can set the SQL mode to strict_trans_tables (strict mode) to strictly check the data so that the error data cannot be inserted, thus ensuring data accuracy. The traditional mode is also a strict mode, which can also implement strict verification, so that error data cannot be inserted, so as to ensure data accuracy. However, in this mode, the result returned by Max (x, 0) is null, so the SQL mode is set based on the actual situation in the Operation containing Max. enum is a string object whose value comes from a column value explicitly enumerated in the column Conventions when the table is created. In some cases, The enum value can also be a null string ('') or NULL: If you insert an invalid value into Enum (that is, a string other than the allowed value column ), an empty string is inserted as a special error value. This string is different from a "normal" null string and has a value of 0.
If the enum column is declared to allow null, the null value is a valid value of the column, and the default value is null. If the enum column is declared as not null, its default value is 1st elements of the allowed value column. (1) The SQL mode is ansimysql> Create Table Test (ID bigint (20) auto_increment primary key, browsertype Enum ('ie', 'Firefox ', 'other ')); mysql> insert into test (browsertype) values ('ie ');
Query OK, 1 row affected (0.07 Sec) mysql> insert into test (browsertype) values ('maxthon ');
Query OK, 1 row affected (0.03 Sec) mysql> show warnings; (although data is successfully inserted, there is warning)
+ --------- + ------ + -------------------------------------------------- +
| Level | code | message |
+ --------- + ------ + -------------------------------------------------- +
| Warning | 1265 | data truncated for column 'browsertype' at Row 1 |
+ --------- + ------ + -------------------------------------------------- +
1 row in SET (0.01 Sec) mysql> select * from test;
+ ---- + ------------- +
| ID | browsertype |
+ ---- + ------------- +
| 1 | ie |
| 2 |
+ ---- + ------------- +
2 rows in SET (0.01 Sec) (2) use the strict mode (strict_trans_tables) mysql> set session SQL _mode = 'strict _ trans_tables ';
Query OK, 0 rows affected (0.00 Sec) mysql> select @ SQL _mode;
+ --------------------- +
| @ SQL _mode |
+ --------------------- +
| Strict_trans_tables |
+ --------------------- +
1 row in SET (0.00 Sec) mysql> insert into test (browsertype) values ('maxthon ');
Error 1265: Data truncated for column 'browsertype' at Row 1
Mysql> insert into test (browsertype) values ('Firefox ');
Query OK, 1 row affected (0.00 Sec) mysql> select * from test;
+ ---- + ------------- +
| ID | browsertype |
+ ---- + ------------- +
| 1 | ie |
| 2 |
| 3 | Firefox |
+ ---- + ------------- +
3 rows in SET (0.00 Sec) (3) traditional mode mysql> Create Table T11 (I INT );
Query OK, 0 rows affected (0.02 Sec) mysql> set SQL _mode = 'ansi ';
Query OK, 0 rows affected (0.00 Sec) mysql> insert into T11 values (9% 0 );
Query OK, 1 row affected (0.00 Sec) mysql> select * from T11;
+ ------ +
| I |
+ ------ +
| Null |
+ ------ +
1 row in SET (0.00 Sec) mysql> set SQL _mode = 'traditional ';
Query OK, 0 rows affected (0.00 Sec) mysql> insert into T11 values (9% 0 );
Error 1365: Division by 0 mysql> show warnings;
+ ------- + ------ + --------------- +
| Level | code | message |
+ ------- + ------ + --------------- +
| Error | 1365 | division by 0 |
+ ------- + ------ + --------------- +
1 row in SET (0.02 Sec)