sql_mode and [my.cnf] settings

Keywords: MySQL Database SQL Session

1. sql_mode mode mode
There is an environment variable sql_mode in mysql database, which defines SQL grammar, data validation and so on that mysql should support. We can view the sql_mode used by the current database in the following ways:
  1. mysql> select @@sql_mode;  
  2. +----------------------------------------------------------------+  
  3. | @@sql_mode                                                     |  
  4. +----------------------------------------------------------------+  
  5. | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |  
  6. +----------------------------------------------------------------+  

Or by looking at system variables:
  1. mysql> show variables like 'sql_mode%'\G;  
  2. *************************** 1. row ***************************  
  3. Variable_name: sql_mode  
  4.         Value: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION  

Versions above MySQL 5.0 support three sql_mode modes: ANSI, TRADITIONAL and STICT_TRANS_TABLES.  
ANSI mode: Loose mode, check the inserted data, if it does not conform to the defined type or length, adjust or truncate the data type and save, report warning warning.  
TRADITIONAL mode: Strict mode, when inserting data into mysql database, the strict verification of data is carried out to ensure that the wrong data can not be inserted and error errors are reported. When used in things, things roll back.  
STRICT_TRANS_TABLES mode: Strict mode, strict data verification, error data can not be inserted, error reporting.  

1.2 ANSI Model
  1. mysql> set @@sql_mode=ANSI;  
  2. Query OK, 0 rows affected (0.00 sec)  
  3.   
  4. mysql> create table test(name varchar(4), pass varchar(4));  
  5. Query OK, 0 rows affected (0.03 sec)  
  6.   
  7. mysql> insert into test values('aaaaa','aaaaa'),('bbbb','bbbb');  
  8. Query OK, 2 rows affected, 2 warnings (0.02 sec)  
  9. Records: 2  Duplicates: 0  Warnings: 2  
  10.   
  11. mysql> show warnings;  
  12. +---------+------+-------------------------------------------+  
  13. | Level   | Code | Message                                   |  
  14. +---------+------+-------------------------------------------+  
  15. | Warning | 1265 | Data truncated for column 'name' at row 1 |  
  16. | Warning | 1265 | Data truncated for column 'pass' at row 1 |  
  17. +---------+------+-------------------------------------------+  
  18. 2 rows in set (0.00 sec)  
  19.   
  20. mysql> select * from test;  
  21. +------+------+  
  22. | name | pass |  
  23. +------+------+  
  24. | aaaa | aaaa |  
  25. | bbbb | bbbb |  
  26. +------+------+  
  27. 2 rows in set (0.00 sec)  

We can see that in ANSI mode, when we insert data, the column length requirement is not met, the data will also be inserted successfully, but truncate the field beyond the column length, and report warning warning warning.  

1.3 STRICT_TRANS_TABLES mode
  1. mysql> set @@sql_mode=STRICT_TRANS_TABLES;  
  2. Query OK, 0 rows affected (0.00 sec)  
  3.   
  4. mysql> create table test(name varchar(4), pass varchar(4));  
  5. Query OK, 0 rows affected (0.02 sec)  
  6.   
  7. mysql> insert into test values('aaaaa','aaaaa'),('bbbb','bbbb');  
  8. ERROR 1406 (22001): Data too long for column 'name' at row 1  
  9.   
  10. mysql> show errors;  
  11. +-------+------+------------------------------------------+  
  12. | Level | Code | Message                                  |  
  13. +-------+------+------------------------------------------+  
  14. | Error | 1406 | Data too long for column 'name' at row 1 |  
  15. +-------+------+------------------------------------------+  
  16. 1 row in set (0.00 sec)  
  17.   
  18. mysql> select * from test;  
  19. Empty set (0.00 sec)  

We can see that in STITCT_TRANS_TABLES mode, when we insert data, mysql will strictly verify the data. When we find that the insertion column values do not meet the requirements, we report error errors directly, which ensures that the wrong data can not be inserted into the database.

1.3 TRADITIONAL mode
  1. mysql> set @@sql_mode=TRADITIONAL;  
  2. Query OK, 0 rows affected (0.00 sec)  
  3.   
  4. mysql> create table test(name varchar(4), pass varchar(4));  
  5. Query OK, 0 rows affected (0.02 sec)  
  6.   
  7. mysql> insert into test values('aaaaa','aaaaa'),('bbbb','bbbb');  
  8. ERROR 1406 (22001): Data too long for column 'name' at row 1  
  9.   
  10. mysql> show errors;  
  11. +-------+------+------------------------------------------+  
  12. | Level | Code | Message                                  |  
  13. +-------+------+------------------------------------------+  
  14. | Error | 1406 | Data too long for column 'name' at row 1 |  
  15. +-------+------+------------------------------------------+  
  16. 1 row in set (0.00 sec)  
  17.   
  18. mysql> select * from test;  
  19. Empty set (0.00 sec)  

The results of the execution of TRADITIONAL mode and STRICT_TRANS_TABLES mode are consistent in this case.  
  1. mysql> select @@sql_mode\G;  
  2. *************************** 1. row ***************************  
  3. @@sql_mode: STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,E  
  4. RROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER  
  5. 1 row in set (0.00 sec)  

Looking at the TRADITIONAL mode, we find that in the TRADITIONAL mode, all transaction storage engines, non-transaction storage engines check, the month and day part of the date type can not contain 0, there can not be such a date (0000-00-00), data can not be divided by 0, grant is prohibited to automatically create new users and some other checks.

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

The directory of my.cnf under mac is

/usr/local/etc


For example:

    vim my.cnf

Under [mysqld], add the following:

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

Posted by crazyjeremy on Wed, 15 May 2019 01:51:07 -0700

Hot Keywords

©2024 Programmer Group