MySQL中使用group by 是總是出現(xiàn)1055的錯誤(推薦)

因為在MySQL中使用group by 是總是出現(xiàn)1055的錯誤，這就導(dǎo)致了必須去查看是什么原因了，查詢了相關(guān)的資料，現(xiàn)在將筆記記錄下來，以便后面可以參考使用：

sql_mode:簡而言之就是：它定義了你MySQL應(yīng)該支持的sql語法，對數(shù)據(jù)的校驗等等

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的各個值的含義：

ONLY_FULL_GROUP_BY：

對于GROUP BY聚合操作，如果在SELECT中的列，沒有在GROUP BY中出現(xiàn)，那么將認(rèn)為這個SQL是不合法的，因為列不在GROUP BY從句中

因為有only_full_group_by，所以我們要在MySQL中正確的使用group by語句的話，只能是select column1 from tb1 group by column1(即只能展示group by的字段，其他均都要報1055的錯)

實例：

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)

但是這種方法只是做了暫時的修改，我們可以更改配置文件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：

在該模式下，如果一個值不能插入到一個事務(wù)表中，則中斷當(dāng)前的操作，對非事務(wù)表不做任何限制

NO_ZERO_IN_DATE：

在嚴(yán)格模式，不接受月或日部分為0的日期。如果使用IGNORE選項，我們?yōu)轭愃频娜掌诓迦?0000-00-00'。在非嚴(yán)格模式，可以接受該日期，但會生成警告。

NO_ZERO_DATE：

在嚴(yán)格模式，不要將 '0000-00-00'做為合法日期。你仍然可以用IGNORE選項插入零日期。在非嚴(yán)格模式，可以接受該日期，但會生成警告

ERROR_FOR_DIVISION_BY_ZERO：

在嚴(yán)格模式，在INSERT或UPDATE過程中，如果被零除(或MOD(X，0))，則產(chǎn)生錯誤(否則為警告)。如果未給出該模式，被零除時MySQL返回NULL。如果用到INSERT IGNORE或UPDATE IGNORE中，MySQL生成被零除警告，但操作結(jié)果為NULL

NO_AUTO_CREATE_USER：

防止GRANT自動創(chuàng)建新用戶，除非還指定了密碼。

NO_ENGINE_SUBSTITUTION：

如果需要的存儲引擎被禁用或未編譯，那么拋出錯誤。不設(shè)置此值時，用默認(rèn)的存儲引擎替代，并拋出一個異常

三、據(jù)說是MySQL5.0以上版本支持三種sql_mode模式：ANSI、TRADITIONAL和STRICT_TRANS_TABLES。

1、ANSI模式：寬松模式，更改語法和行為，使其更符合標(biāo)準(zhǔn)SQL。對插入數(shù)據(jù)進(jìn)行校驗，如果不符合定義類型或長度，對數(shù)據(jù)類型調(diào)整或截斷保存，報warning警告。對于本文開頭中提到的錯誤，可以先把sql_mode設(shè)置為ANSI模式，這樣便可以插入數(shù)據(jù)，而對于除數(shù)為0的結(jié)果的字段值，數(shù)據(jù)庫將會用NULL值代替。將當(dāng)前數(shù)據(jù)庫模式設(shè)置為ANSI模式：

 mysql> set @@sql_mode=ANSI; 

2、TRADITIONAL模式：嚴(yán)格模式，當(dāng)向mysql數(shù)據(jù)庫插入數(shù)據(jù)時，進(jìn)行數(shù)據(jù)的嚴(yán)格校驗，保證錯誤數(shù)據(jù)不能插入，報

error錯誤，而不僅僅是警告。用于事物時，會進(jìn)行事物的回滾。 注釋：一旦發(fā)現(xiàn)錯誤立即放棄INSERT/UPDATE。如果你使用非事務(wù)存儲引擎，這種方式不是你想要的，因為出現(xiàn)錯誤前進(jìn)行的數(shù)據(jù)更改不會“滾動”，結(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)格校驗，錯誤數(shù)據(jù)不能插入，報error錯誤。如果不能將給定的值插入到事務(wù)表中，則放棄該語句。對于非事務(wù)表，如果值出現(xiàn)在單行語句或多行語句的第1行，則放棄該語句。將當(dāng)前數(shù)據(jù)庫模式設(shè)置為STRICT_TRANS_TABLES模式：

mysql> set @@sql_mode=STRICT_TRANS_TABLES;

沒有最好與最壞的模式，只有最合適的模式。需要根據(jù)自己的實際情況去選擇那個最適合的模式！??！

另外說一點，這里的更改數(shù)據(jù)庫模式都是session級別的，一次性，關(guān)了再開就不算數(shù)了?。?！

也可以通過配置文件設(shè)置:vim /etc/my.cnf

在my.cnf（my.ini）添加如下配置:

[mysqld]
sql_mode='你想要的模式'

總結(jié)

以上所述是小編給大家介紹MySQL中使用group by 是總是出現(xiàn)1055的錯誤，希望對大家有所幫助！