MySQL的SQL_MODE模式

標籤：

1. sql_mode模式
mysql資料庫的中有一個環境變數sql_mode,定義了mysql應該支援的sql文法，資料校正等！我們可以通過以下方式查看當前資料庫使用的sql_mode：

1. mysql> select @@sql_mode;  
2. +----------------------------------------------------------------+  
3. | @@sql_mode                                                     |  
4. +----------------------------------------------------------------+  
5. | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |  
6. +----------------------------------------------------------------+  

或者通過查看系統變數方式:

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  

 mysql5.0以上版本支援三種sql_mode模式：　　ANSI　　TRADITIONAL　　STRICT_TRANS_TABLES。
ANSI模式：寬鬆模式，對插入資料進行校正，如果不符合定義類型或長度，對資料類型調整或截斷儲存，報warning警告。
TRADITIONAL模式：strict 模式，當向mysql資料庫插入資料時，進行資料的嚴格校正，保證錯誤資料不能插入，報error錯誤。用於事物時，會進行事物的復原。
STRICT_TRANS_TABLES模式：strict 模式，進行資料的嚴格校正，錯誤資料不能插入，報error錯誤。

 1.2 ANSI模式

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)  

 
我們可以看到，在ANSI模式下，當我們插入資料時，未滿足列長度要求時，資料同樣會插入成功，但是對超出列長度的欄位進行截斷，同時報告warning警告。

 1.3 STRICT_TRANS_TABLES模式

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)  

 
我們可以看到，在STRICT_TRANS_TABLES模式下，當我們插入資料時，mysql會嚴格的進行資料的校正，當發現插入列值未滿足要求，直接報告error錯誤，保證了錯誤資料無法插入到資料庫中。

 1.3 TRADITIONAL模式

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)  

 

TRADITIONAL模式與STRICT_TRANS_TABLES模式執行的結果，在這種情況下一致。

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)  

 
看一下TRADITIONAL模式，我們發現在TRADITIONAL模式下，對所有的事務儲存引擎，非事務儲存引擎檢查，日期類型中的月和日部分不能包含0，不能有0這樣的日期(0000-00-00),資料不能除0，禁止grant自動建立新使用者等一些校正。

 

注意：我們這裡設定的sql_mode都是session層級的。另外，可以直接修改my.ini檔案，找到sql_mode，然後設定新的模式即可！

 

例如： 

MySQL5.0\MySQL Server 5.0\my.ini

在[mysqld]下面添加如下列：

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

 

為支援原創，特寫明轉載地址：

http://blog.csdn.net/wulantian/article/details/8905573

 

MySQL的SQL_MODE模式