MySQL的sql_mode 模式修改 my.cnf

MySQL的sql_mode 模式修改 my.cnf

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

 mysql> select @@sql_mode; 
+----------------------------------------------------------------+ 
| @@sql_mode                                                     | 
+----------------------------------------------------------------+ 
| STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | 
+----------------------------------------------------------------+ 
或者通過查看系統變數方式:
mysql> show variables like 'sql_mode%'\G; 
*************************** 1. row *************************** 
Variable_name: sql_mode 
        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模式 

 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'),('bbbb','bbbb'); 
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 | aaaa | 
| bbbb | bbbb | 
+------+------+ 
2 rows in set (0.00 sec)

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

1.3 STRICT_TRANS_TABLES模式

 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'),('bbbb','bbbb'); 
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)

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

1.3 TRADITIONAL模式

 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'),('bbbb','bbbb'); 
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)

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

 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)

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

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

例如： 

    vi /etc/my.cnf

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

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

本文永久更新連結地址：