MySQL SQL _mode Mode Modification my. cnf

MySQL SQL _mode Mode Modification my. cnf

1. SQL _mode Mode
The mysql database has an environment variable SQL _mode, which defines the SQL syntax and data verification that mysql should support! You can view the SQL _mode used by the current database in the following ways:

Mysql> select @ SQL _mode;
+ ---------------------------------------------------------------- +
| @ SQL _mode |
+ ---------------------------------------------------------------- +
| STRICT_TRANS_TABLES, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION |
+ ---------------------------------------------------------------- +
Or you can view the system variables:
Mysql> show variables like 'SQL _ mode %' \ G;
* *************************** 1. row ***************************
Variable_name: SQL _mode
Value: STRICT_TRANS_TABLES, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION

MySQL and later versions support three SQL _mode modes: ANSI, TRADITIONAL, and STRICT_TRANS_TABLES.
ANSI mode: loose mode, which verifies the inserted data. If the data type or length does not conform to the defined type or length, adjust or cut the data type and save the data, a warning is reported.
TRADITIONAL mode: strict mode. when data is inserted into the mysql database, strict data validation is performed to ensure that the error data cannot be inserted and an error is reported. When used for a transaction, the transaction will be rolled back.
STRICT_TRANS_TABLES mode: strictly checks the data. The error data cannot be inserted and an error is reported.

1.2 ANSI Mode

Mysql> set @ SQL _mode = ANSI;
Query OK, 0 rows affected (0.00 sec)

Mysql> create table test (name varchar (4), pass varchar (4 ));
Query OK, 0 rows affected (0.03 sec)

Mysql> insert into test values ('aaaaa', 'aaaaa'), ('bbbbbb', 'bbbbb ');
Query OK, 2 rows affected, 2 warnings (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 2

Mysql> show warnings;
+ --------- + ------ + --------------------------------------------- +
| Level | Code | Message |
+ --------- + ------ + --------------------------------------------- +
| Warning | 1265 | Data truncated for column 'name' at row 1 |
| Warning | 1265 | Data truncated for column 'pass' at row 1 |
+ --------- + ------ + --------------------------------------------- +
2 rows in set (0.00 sec)

Mysql> select * from test;
+ ------ +
| Name | pass |
+ ------ +
| Aaaa |
| Bbbb |
+ ------ +
2 rows in set (0.00 sec)

We can see that in ANSI mode, when we insert data that does not meet the column length requirements, the data is also inserted successfully, but the fields that exceed the column length are truncated, the system also reports a warning.

1.3 STRICT_TRANS_TABLES Mode

Mysql> set @ SQL _mode = STRICT_TRANS_TABLES;
Query OK, 0 rows affected (0.00 sec)

Mysql> create table test (name varchar (4), pass varchar (4 ));
Query OK, 0 rows affected (0.02 sec)

Mysql> insert into test values ('aaaaa', 'aaaaa'), ('bbbbbb', 'bbbbb ');
ERROR 1406 (22001): Data too long for column 'name' at row 1

Mysql> show errors;
+ ------- + ------ + -------------------------------------------- +
| Level | Code | Message |
+ ------- + ------ + -------------------------------------------- +
| Error | 1406 | Data too long for column 'name' at row 1 |
+ ------- + ------ + -------------------------------------------- +
1 row in set (0.00 sec)

Mysql> select * from test;
Empty set (0.00 sec)

We can see that in STRICT_TRANS_TABLES mode, mysql strictly performs data validation when we insert data. When the inserted column value does not meet the requirements, an error is reported directly, the error data cannot be inserted into the database.

1.3 TRADITIONAL mode

Mysql> set @ SQL _mode = TRADITIONAL;
Query OK, 0 rows affected (0.00 sec)

Mysql> create table test (name varchar (4), pass varchar (4 ));
Query OK, 0 rows affected (0.02 sec)

Mysql> insert into test values ('aaaaa', 'aaaaa'), ('bbbbbb', 'bbbbb ');
ERROR 1406 (22001): Data too long for column 'name' at row 1

Mysql> show errors;
+ ------- + ------ + -------------------------------------------- +
| Level | Code | Message |
+ ------- + ------ + -------------------------------------------- +
| Error | 1406 | Data too long for column 'name' at row 1 |
+ ------- + ------ + -------------------------------------------- +
1 row in set (0.00 sec)

Mysql> select * from test;
Empty set (0.00 sec)

The result of TRADITIONAL mode and STRICT_TRANS_TABLES mode is consistent in this case.

Mysql> select @ SQL _mode \ G;
* *************************** 1. row ***************************
@ SQL _mode: STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, E
RROR_FOR_DIVISION_BY_ZERO, TRADITIONAL, NO_AUTO_CREATE_USER
1 row in set (0.00 sec)

Looking at the TRADITIONAL mode, we found that in the TRADITIONAL mode, all transaction storage engines and non-transaction storage engines are checked. the month and day of the date type cannot contain 0, there cannot be a date such as 0 (0000-00-00), data cannot be divided into 0, and grant is prohibited from automatically creating new users and Other verifications.

Note: The SQL _mode we set here is at the session level. In addition, you can directly modify the my. ini file, find SQL _mode, and set a new mode!

For example:

Vi/etc/my. cnf

Add the following content under [mysqld:

SQL _mode = NO_ENGINE_SUBSTITUTION, STRICT_TRANS_TABLES

This article permanently updates the link address: