mysql中的sql_mode模式實例詳解

更新時間：2020年01月03日 11:48:12 作者：dawn-liu

這篇文章主要介紹了mysql中的sql_mode模式,結(jié)合實例形式詳細(xì)分析了mysql sql_mode模式相關(guān)原理、用法及操作注意事項,需要的朋友可以參考下

本文實例講述了mysql中的sql_mode模式。分享給大家供大家參考，具體如下：

mysql數(shù)據(jù)庫的中有一個環(huán)境變量sql_mode,定義了mysql應(yīng)該支持的sql語法，數(shù)據(jù)校驗等！我們可以通過以下方式查看當(dāng)前數(shù)據(jù)庫使用的sql_mode：

mysql> select @@sql_mode;
+----------------------------------------------------------------+
| @@sql_mode                           |
+----------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------+

mysql5.0以上版本支持三種sql_mode模式

ANSI模式

寬松模式，對插入數(shù)據(jù)進(jìn)行校驗，如果不符合定義類型或長度，對數(shù)據(jù)類型調(diào)整或截斷保存，報warning警告。

TRADITIONAL模式

嚴(yán)格模式，當(dāng)向mysql數(shù)據(jù)庫插入數(shù)據(jù)時，進(jìn)行數(shù)據(jù)的嚴(yán)格校驗，保證錯誤數(shù)據(jù)不能插入，報error錯誤。用于事物時，會進(jìn)行事物的回滾。

STRICT_TRANS_TABLES模式

嚴(yán)格模式，進(jìn)行數(shù)據(jù)的嚴(yán)格校驗，錯誤數(shù)據(jù)不能插入，報error錯誤。

1 ANSI模式

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

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)

2 STRICT_TRANS_TABLES模式

在STRICT_TRANS_TABLES模式下，當(dāng)我們插入數(shù)據(jù)時，mysql會嚴(yán)格的進(jìn)行數(shù)據(jù)的校驗，當(dāng)發(fā)現(xiàn)插入列值未滿足要求，直接報告error錯誤，保證了錯誤數(shù)據(jù)無法插入到數(shù)據(jù)庫中。

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)

3 TRADITIONAL模式，初看結(jié)果是不是一樣

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)

但是，可以看看設(shè)置后的情況

mysql> set @@sql_mode=TRADITIONAL;
Query OK, 0 rows affected (0.00 sec)
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_SUBSTITUTION
1 row in set (0.00 sec)

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

最后：

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

vi /etc/my.cnf

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

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

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

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

改模式之前的操作

mysql> select * from employee 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)

修改一下，退出再進(jìn)入才會生效

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.00 sec)
mysql> select @@sql_mode;
+----------------------------------------------------------------+
| @@sql_mode                           |
+----------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> exit
Bye

再次進(jìn)入

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)

下面查看修改后的查看結(jié)果

mysql> select * from employee group by post;  //只能查看post
ERROR 1055 (42000): 't1.employee.id' isn't in GROUP BY
mysql> select post from employee group by post;
+-----------------------------------------+
| post                  |
+-----------------------------------------+
| operation                |
| sale                  |
| teacher                 |
| 老男孩駐沙河辦事處外交大使       |
+-----------------------------------------+
4 rows in set (0.00 sec)
mysql> select id,post from employee group by post;
ERROR 1055 (42000): 't1.employee.id' isn't in GROUP BY
mysql> select name,post from employee group by post,name; //根據(jù)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)

更多關(guān)于MySQL相關(guān)內(nèi)容感興趣的讀者可查看本站專題：《MySQL查詢技巧大全》、《MySQL常用函數(shù)大匯總》、《MySQL日志操作技巧大全》、《MySQL事務(wù)操作技巧匯總》、《MySQL存儲過程技巧大全》及《MySQL數(shù)據(jù)庫鎖相關(guān)技巧匯總》

希望本文所述對大家MySQL數(shù)據(jù)庫計有所幫助。

您可能感興趣的文章: