ANSI模式

寬松模式，對插入數據進行校驗，如果不符合定義類型或長度，對數據類型調整或截斷保存，報warning警告。

TRADITIONAL模式

嚴格模式，當向mysql數據庫插入數據時，進行數據的嚴格校驗，保證錯誤數據不能插入，報error錯誤。用于事物時，會進行事物的回滾。

STRICT_TRANS_TABLES模式

嚴格模式，進行數據的嚴格校驗，錯誤數據不能插入，報error錯誤。

1?ANSI模式

在ANSI模式下，當我們插入數據時，未滿足列長度要求時，數據同樣會插入成功，但是對超出列長度的字段進行截斷，同時報告warning警告。

mysql> set @@sql_mode=ANSI;

Query OK, 0 rows affected (0.00sec)

mysql> create table test(name varchar(4), pass varchar(4));

Query OK, 0 rows affected (0.03sec)

mysql> insert into test values('aaaaa','aaaaa'),('bbbb','bbbb');

Query OK,2 rows affected, 2 warnings (0.02sec)

Records:2 Duplicates: 0 Warnings: 2mysql>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.00sec)

mysql> select * fromtest;+------+------+

| name | pass |

+------+------+

| aaaa | aaaa |

| bbbb | bbbb |

+------+------+

2 rows in set (0.00 sec)

2?STRICT_TRANS_TABLES模式

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

mysql> set @@sql_mode=STRICT_TRANS_TABLES;

Query OK, 0 rows affected (0.00sec)

mysql> create table test(name varchar(4), pass varchar(4));

Query OK, 0 rows affected (0.02sec)

mysql> insert into test values('aaaaa','aaaaa'),('bbbb','bbbb');

ERROR1406 (22001): Data too long for column 'name' at row 1mysql>show errors;+-------+------+------------------------------------------+

| Level | Code | Message |

+-------+------+------------------------------------------+

| Error | 1406 | Data too long for column 'name' at row 1 |

+-------+------+------------------------------------------+

1 row in set (0.00sec)

mysql> select * fromtest;

Empty set (0.00 sec)

3?TRADITIONAL模式，初看結果是不是一樣

mysql> set @@sql_mode=TRADITIONAL;

Query OK, 0 rows affected (0.00sec)

mysql> create table test(name varchar(4), pass varchar(4));

Query OK, 0 rows affected (0.02sec)

mysql> insert into test values('aaaaa','aaaaa'),('bbbb','bbbb');

ERROR1406 (22001): Data too long for column 'name' at row 1mysql>show errors;+-------+------+------------------------------------------+

| Level | Code | Message |

+-------+------+------------------------------------------+

| Error | 1406 | Data too long for column 'name' at row 1 |

+-------+------+------------------------------------------+

1 row in set (0.00sec)

mysql> select * fromtest;

Empty set (0.00 sec)

但是，可以看看設置后的情況

mysql> set @@sql_mode=TRADITIONAL;

Query OK, 0 rows affected (0.00sec)

mysql>select @@sql_mode\G*************************** 1. row ***************************@@sql_mode: STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION1 row in set (0.00 sec)

在TRADITIONAL模式下，對所有的事務存儲引擎，非事務存儲引擎檢查，日期類型中的月和日部分不能包含0，不能有0這樣的日期(0000-00-00),數據不能除0，禁止grant自動創建新用戶等一些校驗。

最后：

set @@只是在sessions級別設置的，要想所有的都生效，還是要設置配置文件

vi /etc/my.cnf

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

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

#NO_ENGINE_SUBSTITUTION對于不存在的引擎就報錯，不加的話，指定不支持的引擎時指定默認的innodb

另外：sql_mode還有一個配置ONLY_FULL_GROUP_BY，這個表示采用group by帥選數據的時候只能查看新組內信息

改模式之前的操作

mysql> select * fromemployee group by post;+----+--------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+

| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |

+----+--------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+

| 14 | 張野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 |

| 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 |

| 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |

| 1 | egon | male | 18 | 2017-03-01 | 老男孩駐沙河辦事處外交大使 | NULL | 7300.33 | 401 | 1 |

+----+--------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+

4 rows in set (0.00 sec)

此時的sql_mode:

mysql>select @@sql_mode;+----------------------------------------------------------------+

| @@sql_mode |

+----------------------------------------------------------------+

| STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |

+----------------------------------------------------------------+

1 row in set (0.00 sec)

修改一下，退出再進入才會生效

mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY';

Query OK, 0 rows affected (0.00sec)

mysql>select @@sql_mode;+----------------------------------------------------------------+

| @@sql_mode |

+----------------------------------------------------------------+

| STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |

+----------------------------------------------------------------+

1 row in set (0.00sec)

mysql>exit

Bye

再次進入

mysql> select @@sql_mode;

+-----------------------------------------------------------------------------------+

| @@sql_mode |

+-----------------------------------------------------------------------------------+

| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |

+-----------------------------------------------------------------------------------+

1 row in set (0.00 sec)

下面查看修改后的查看結果

mysql> select * fromemployee group by post; //只能查看post

ERROR1055 (42000): 't1.employee.id' isn't in GROUP BY

mysql> select post fromemployee group by post;+-----------------------------------------+

| post |

+-----------------------------------------+

| operation |

| sale |

| teacher |

| 老男孩駐沙河辦事處外交大使 |

+-----------------------------------------+

4 rows in set (0.00sec)

mysql> select id,post fromemployee group by post;

ERROR1055 (42000): 't1.employee.id' isn't in GROUP BY

mysql> select name,post from employee group by post,name; ?//根據group by 后面的選擇查看

+------------+-----------------------------------------+

| name | post |

+------------+-----------------------------------------+

| 張野 | operation |

| 程咬金 | operation |

| 程咬鐵 | operation |

| 程咬銅 | operation |

| 程咬銀 | operation |

| 丁丁 | sale |

| 丫丫 | sale |

| 星星 | sale |

| 格格 | sale |

| 歪歪 | sale |

| alex | teacher |

| jingliyang | teacher |

| jinxin | teacher |

| liwenzhou | teacher |

| wupeiqi | teacher |

| xiaomage | teacher |

| yuanhao | teacher |

| egon | 老男孩駐沙河辦事處外交大使 |

+------------+-----------------------------------------+

18 rows in set (0.00 sec)

總結

以上是生活随笔為你收集整理的mysql mode_mysql中的sql_mode的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。