因?yàn)樵贛ySQL中使用group by 是總是出現(xiàn)1055的錯(cuò)誤,這就導(dǎo)致了必須去查看是什么原因了,查詢了相關(guān)的資料,現(xiàn)在將筆記記錄下來,以便后面可以參考使用:
sql_mode:簡而言之就是:它定義了你MySQL應(yīng)該支持的sql語法,對(duì)數(shù)據(jù)的校驗(yàn)等等
select @@sql_mode:使用該命令我們可以查看我們當(dāng)前數(shù)據(jù)庫的sql_mode
mysql> select @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
下面我們來看看sql_mode的各個(gè)值的含義:
ONLY_FULL_GROUP_BY:
對(duì)于GROUP BY聚合操作,如果在SELECT中的列,沒有在GROUP BY中出現(xiàn),那么將認(rèn)為這個(gè)SQL是不合法的,因?yàn)榱胁辉贕ROUP BY從句中
因?yàn)橛衞nly_full_group_by,所以我們要在MySQL中正確的使用group by語句的話,只能是select column1 from tb1 group by column1(即只能展示group by的字段,其他均都要報(bào)1055的錯(cuò))
實(shí)例:
mysql> select * from tt1;
+----+-------+--------+
| id | name | gender |
+----+-------+--------+
| 1 | xiong | 0 |
| 2 | ying | 0 |
| 3 | cai | 0 |
| 4 | zhang | 0 |
| 5 | li | 1 |
| 6 | wang | 1 |
+----+-------+--------+
6 rows in set (0.00 sec)
mysql> select id,name from tt1 group by name;
ERROR 1055 (42000):
mysql> select * from tt1;
+----+-------+--------+
| id | name | gender |
+----+-------+--------+
| 1 | xiong | 0 |
| 2 | ying | 0 |
| 3 | cai | 0 |
| 4 | zhang | 0 |
| 5 | li | 1 |
| 6 | wang | 1 |
+----+-------+--------+
6 rows in set (0.00 sec)
mysql> select name from tt1 group by name;
+-------+
| name |
+-------+
| cai |
| li |
| wang |
| xiong |
| ying |
| zhang |
+-------+
6 rows in set (0.00 sec)
所以我們要使用能正確的使用group by 的話就必須刪除掉only_full_group_by
set sql_mode=(select replace(@@sql_mode,'ONLY_FULL_GROUP_BY','')); 可以使用該語句來將空格替換掉only_full_group_by,這樣我們就可以使用
mysql> SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
Query OK, 0 rows affected (0.00 sec)
mysql> select id,name from tt1 group by name;
+----+-------+
| id | name |
+----+-------+
| 3 | cai |
| 5 | li |
| 6 | wang |
| 1 | xiong |
| 2 | ying |
| 4 | zhang |
+----+-------+
6 rows in set (0.00 sec)
但是這種方法只是做了暫時(shí)的修改,我們可以更改配置文件my.ini
sql_mode= STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
STRICT_TRANS_TABLES:
在該模式下,如果一個(gè)值不能插入到一個(gè)事務(wù)表中,則中斷當(dāng)前的操作,對(duì)非事務(wù)表不做任何限制
NO_ZERO_IN_DATE:
在嚴(yán)格模式,不接受月或日部分為0的日期。如果使用IGNORE選項(xiàng),我們?yōu)轭愃频娜掌诓迦?0000-00-00'。在非嚴(yán)格模式,可以接受該日期,但會(huì)生成警告。
NO_ZERO_DATE:
在嚴(yán)格模式,不要將 '0000-00-00'做為合法日期。你仍然可以用IGNORE選項(xiàng)插入零日期。在非嚴(yán)格模式,可以接受該日期,但會(huì)生成警告
ERROR_FOR_DIVISION_BY_ZERO:
在嚴(yán)格模式,在INSERT或UPDATE過程中,如果被零除(或MOD(X,0)),則產(chǎn)生錯(cuò)誤(否則為警告)。如果未給出該模式,被零除時(shí)MySQL返回NULL。如果用到INSERT IGNORE或UPDATE IGNORE中,MySQL生成被零除警告,但操作結(jié)果為NULL
防止GRANT自動(dòng)創(chuàng)建新用戶,除非還指定了密碼。
如果需要的存儲(chǔ)引擎被禁用或未編譯,那么拋出錯(cuò)誤。不設(shè)置此值時(shí),用默認(rèn)的存儲(chǔ)引擎替代,并拋出一個(gè)異常
三、據(jù)說是MySQL5.0以上版本支持三種sql_mode模式:ANSI、TRADITIONAL和STRICT_TRANS_TABLES。
1、ANSI模式:寬松模式,更改語法和行為,使其更符合標(biāo)準(zhǔn)SQL。對(duì)插入數(shù)據(jù)進(jìn)行校驗(yàn),如果不符合定義類型或長度,對(duì)數(shù)據(jù)類型調(diào)整或截?cái)啾4?,?bào)warning警告。對(duì)于本文開頭中提到的錯(cuò)誤,可以先把sql_mode設(shè)置為ANSI模式,這樣便可以插入數(shù)據(jù),而對(duì)于除數(shù)為0的結(jié)果的字段值,數(shù)據(jù)庫將會(huì)用NULL值代替。將當(dāng)前數(shù)據(jù)庫模式設(shè)置為ANSI模式:
mysql> set @@sql_mode=ANSI;
2、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)行事物的回滾。 注釋:一旦發(fā)現(xiàn)錯(cuò)誤立即放棄INSERT/UPDATE。如果你使用非事務(wù)存儲(chǔ)引擎,這種方式不是你想要的,因?yàn)槌霈F(xiàn)錯(cuò)誤前進(jìn)行的數(shù)據(jù)更改不會(huì)“滾動(dòng)”,結(jié)果是更新“只進(jìn)行了一部分”。
將當(dāng)前數(shù)據(jù)庫模式設(shè)置為TRADITIONAL模式:
mysql> set @@sql_mode=TRADITIONAL;
3、STRICT_TRANS_TABLES模式:嚴(yán)格模式,進(jìn)行數(shù)據(jù)的嚴(yán)格校驗(yàn),錯(cuò)誤數(shù)據(jù)不能插入,報(bào)error錯(cuò)誤。如果不能將給定的值插入到事務(wù)表中,則放棄該語句。對(duì)于非事務(wù)表,如果值出現(xiàn)在單行語句或多行語句的第1行,則放棄該語句。將當(dāng)前數(shù)據(jù)庫模式設(shè)置為STRICT_TRANS_TABLES模式:
mysql> set @@sql_mode=STRICT_TRANS_TABLES;
沒有最好與最壞的模式,只有最合適的模式。需要根據(jù)自己的實(shí)際情況去選擇那個(gè)最適合的模式?。?!
另外說一點(diǎn),這里的更改數(shù)據(jù)庫模式都是session級(jí)別的,一次性,關(guān)了再開就不算數(shù)了?。?!
也可以通過配置文件設(shè)置:vim /etc/my.cnf
在my.cnf(my.ini)添加如下配置:
[mysqld]
sql_mode='你想要的模式'
總結(jié)
以上所述是小編給大家介紹MySQL中使用group by 是總是出現(xiàn)1055的錯(cuò)誤,希望對(duì)大家有所幫助!
您可能感興趣的文章:- MySQL優(yōu)化GROUP BY(松散索引掃描與緊湊索引掃描)
- MySQL優(yōu)化GROUP BY方案
- mysql group by 對(duì)多個(gè)字段進(jìn)行分組操作
- 基于mysql實(shí)現(xiàn)group by取各分組最新一條數(shù)據(jù)
- MySQL去重該使用distinct還是group by?
- Mysql5.7及以上版本 ONLY_FULL_GROUP_BY報(bào)錯(cuò)的解決方法
- mysql case when group by 實(shí)例詳解
- Mysql升級(jí)到5.7后遇到的group by查詢問題解決
- MySQL group by語句如何優(yōu)化