#1055 - Expression of SELECT list is not in GROUP BY clause and contains nonaggregated column this i

這個sql語句違背了sql_mode=only_full_group_by 。

先看看這個sql_mode=only_full_group_by是個什麼意思:

(摘自官網文檔)

1 2 3 4 5

ONLY_FULL_GROUP_BY Reject queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BYclause nor are functionally dependent on (uniquely determined by) GROUP BY columns. As of MySQL 5.7.5, the default SQL mode includes ONLY_FULL_GROUP_BY. (Before 5.7.5, MySQL does not detect functional dependency andONLY_FULL_GROUP_BY is not enabled by default. For a description of pre-5.7.5 behavior, see the MySQL 5.6 Reference Manual.) A MySQL extension to standard SQL permits references in the HAVING clause to aliased expressions in the select list. Before MySQL 5.7.5, enablingONLY_FULL_GROUP_BY disables this extension, thus requiring the HAVING clause to be written using unaliased expressions. As of MySQL 5.7.5, this restriction is lifted so that the HAVING clause can refer to aliases regardless of whether ONLY_FULL_GROUP_BY is enabled. For additional discussion and examples, see Section 13.20.3, “MySQL Handling of GROUP BY”.

來源連結:

http://dev.MySQL.com/doc/refman/5.7/en/sql-mode.html#sqlmode_only_full_group_by

mysql Handling of GROUP BY

解決辦法:

1	set @@sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

去掉ONLY_FULL_GROUP_BY即可正常執行sql.

sudo vim /etc/mysql/conf.d/mysql.cnf

Scroll to the bottom of file Copy and paste

[mysqld]sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

to the bottom of the file save and exit input mode sudo service mysql restart to restart MySQL.

另外一篇： mysql的sql_mode合理設定

author：skate
time:2013/04/11

 

MySQL的sql_mode合理設定

sql_mode是個很容易被忽視的變數，預設值是空值，在這種設定下是可以允許一些非法操作的，比如允許一些非法資料的插入。在生產環境必須將這個值設定為strict 模式，所以開發、測試環境的資料庫也必須要設定，這樣在開發測試階段就可以發現問題 
  
sql_mode常用值如下： 
ONLY_FULL_GROUP_BY：

對於GROUP BY彙總操作，如果在SELECT中的列，沒有在GROUP BY中出現，那麼這個SQL是不合法的，因為列不在GROUP BY從句中

NO_AUTO_VALUE_ON_ZERO：

該值影響自增長列的插入。預設設定下，插入0或NULL代表產生下一個自增長值。如果使用者 希望插入的值為0，而該列又是自增長的，那麼這個選項就有用了。

STRICT_TRANS_TABLES：

在該模式下，如果一個值不能插入到一個事務表中，則中斷當前的操作，對非事務表不做限制

NO_ZERO_IN_DATE：

在strict 模式下，不允許日期和月份為零

NO_ZERO_DATE：

設定該值，mysql資料庫不允許插入零日期，插入零日期會拋出錯誤而不是警告。

ERROR_FOR_DIVISION_BY_ZERO：

在INSERT或UPDATE過程中，如果資料被零除，則產生錯誤而非警告。如 果未給出該模式，那麼資料被零除時MySQL返回NULL

NO_AUTO_CREATE_USER：

禁止GRANT建立密碼為空白的使用者

NO_ENGINE_SUBSTITUTION：

如果需要的儲存引擎被禁用或未編譯，那麼拋出錯誤。不設定此值時，用預設的儲存引擎替代，並拋出一個異常

PIPES_AS_CONCAT：

將"||"視為字串的串連操作符而非或運算子，這和Oracle資料庫是一樣的，也和字串的拼接函數Concat相類似

ANSI_QUOTES：

啟用ANSI_QUOTES後，不能用雙引號來引用字串，因為它被解釋為識別符

oracle的sql_mode設定等同：PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER.

 

如果使用mysql，為了繼續保留大家使用oracle的習慣，可以對mysql的sql_mode設定如下：

 

在my.cnf添加如下配置
[mysqld]
sql_mode='ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,

ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,PIPES_AS_CONCAT,ANSI_QUOTES'

 

 

參考：http://dev.mysql.com/doc/refman/5.5/en/server-sql-mode.html

 

------end------