mysql sql mode

Mysql 5.7默认的 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, and NO_ENGINE_SUBSTITUTION。

 

 

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 BY clause nor are functionally dependent on (uniquely determined by) GROUP BY columns.

 

ONLY_FULL_GROUP_BY 模式开启时，select中的列一定要出现在GROUP BY中，以此来避免不确定性查询可能带来的错误。

 

#查询mysql服务器版本
select @@version; 
#查看sql_mode的语法
SELECT @@sql_mode; 
select @@GLOBAL.sql_mode; 
select @@SESSION.sql_mode; 
#修改sql_mode的语法
SET GLOBAL sql_mode = 'modes’;
SET SESSION sql_mode = 'modes’;

 

也可通过修改配置文件 /etc/my.cnf达到目的:

#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
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 模式也可以修改语句实现方式：

可以用DISTINCT替代group by，前提是通过DISTINCT列中获取的值和group by id是相同的。

SELECT name, id, email from user_flows WHERE author_user = "dweiwei"  group by id ORDER BY creat_time DESC LIMIT 0, 25;

改为

SELECT DISTINCT name, id, email from user_flows WHERE author_user = "dweiwei"  ORDER BY creat_time DESC LIMIT 0, 25; 

 

mysql官网引用:

https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

http://mysqlserverteam.com/mysql-5-7-only_full_group_by-improved-recognizing-functional-dependencies-enabled-by-default/