|
ANSI模式 |
寬松模式，對(duì)插入數(shù)據(jù)進(jìn)行校驗(yàn)，如果不符合定義類型或長(zhǎng)度，對(duì)數(shù)據(jù)類型調(diào)整或截?cái)啾４?，?bào)warning警告。 |
|
TRADITIONAL模式 |
嚴(yán)格模式，當(dāng)向mysql數(shù)據(jù)庫插入數(shù)據(jù)時(shí)，進(jìn)行數(shù)據(jù)的嚴(yán)格校驗(yàn)，保證錯(cuò)誤數(shù)據(jù)不能插入，報(bào)error錯(cuò)誤。用于事物時(shí)，會(huì)進(jìn)行事物的回滾。 |
|
STRICT_TRANS_TABLES模式 |
嚴(yán)格模式，進(jìn)行數(shù)據(jù)的嚴(yán)格校驗(yàn)，錯(cuò)誤數(shù)據(jù)不能插入，報(bào)error錯(cuò)誤。
|
1 ANSI模式
在ANSI模式下，當(dāng)我們插入數(shù)據(jù)時(shí)，未滿足列長(zhǎng)度要求時(shí)，數(shù)據(jù)同樣會(huì)插入成功，但是對(duì)超出列長(zhǎng)度的字段進(jìn)行截?cái)?，同時(shí)報(bào)告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ù)時(shí)，mysql會(huì)嚴(yán)格的進(jìn)行數(shù)據(jù)的校驗(yàn)，當(dāng)發(fā)現(xiàn)插入列值未滿足要求，直接報(bào)告error錯(cuò)誤，保證了錯(cuò)誤數(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模式下，對(duì)所有的事務(wù)存儲(chǔ)引擎，非事務(wù)存儲(chǔ)引擎檢查，日期類型中的月和日部分不能包含0，不能有0這樣的日期(0000-00-00),數(shù)據(jù)不能除0，禁止grant自動(dòng)創(chuàng)建新用戶等一些校驗(yàn)。
最后：
set @@只是在sessions級(jí)別設(shè)置的，要想所有的都生效，還是要設(shè)置配置文件
vi /etc/my.cnf
在[mysqld]下面添加如下列：
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
#NO_ENGINE_SUBSTITUTION對(duì)于不存在的引擎就報(bào)錯(cuò)，不加的話，指定不支持的引擎時(shí)指定默認(rèn)的innodb
另外：sql_mode還有一個(gè)配置ONLY_FULL_GROUP_BY，這個(gè)表示采用group by帥選數(shù)據(jù)的時(shí)候只能查看新組內(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)
此時(shí)的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)入才會(huì)生效
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存儲(chǔ)過程技巧大全》及《MySQL數(shù)據(jù)庫鎖相關(guān)技巧匯總》
希望本文所述對(duì)大家MySQL數(shù)據(jù)庫計(jì)有所幫助。
標(biāo)簽：河北 煙臺(tái) 黃石 陽江 赤峰 來賓 果洛 鞍山
巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《mysql中的sql_mode模式實(shí)例詳解》，本文關(guān)鍵詞 mysql,中的,sql,mode,模式,實(shí)例,；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題，煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們，我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無關(guān)。