MySQL : GROUP BY 报错及 sql_mode 相关思考

技术笔记 - 数据库 - MySQL

发布时间:2018-11-21 23:00:05

admin 于  2018-11-21 23:00:05 编辑

sql_mode是个很容易被忽视的变量,默认值是空值,在这种设置下是可以允许一些非法操作的,比如允许一些非法数据的插入。

在生产环境必须将这个值设置为严格模式,所以开发、测试环境的数据库也必须要设置,这样在开发测试阶段就可以发现问题。

问题显示

ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'database_tl.emp.id' which is not functionally dependent on columns in GROUP BY clause; 
this is incompatible with sql_mode=only_full_group_by

错误原因

GROUP BY

SELECT '选取分组中的列+聚合函数' FROM '表名称' GROUP BY '分组的列'

从语法格式来看,先有分组,再确定检索的列,检索的列只能在参加分组的列中选。

MySQL 5.6版以前对其语法采取了包容的态度,使不正确的语法可以正常运行下去而不提示错误,5.7版后开始,对语法控制开始严格起来,于是这个包容语法直接变成错误提示。

Oracle则早就采用了开启 only_full_group_by 模式后的规则。

only_full_group_by :对于 GROUP BY 聚合操作,如果在 SELECT 中的列,没有在 GROUP BY 中出现,那么这个SQL是不合法的,因为列不在 GROUP BY 从句中,也就是说查出来的列必须在 GROUP BY 后面出现否则就会报错,或者这个字段出现在聚合函数里面。

sql_mode

这里有一个变量参数 sql_mode,他直接参与MySQL的安全行为管理;

sql_mode 是个很容易被忽视的变量,默认值是空值,在这种设置下是可以允许一些非法操作的,比如允许一些非法数据的插入;

而在生产环境中必须为这个值设置严格模式,在开发与测试环境中数据库也需要做同样的设置,这样在开发阶段就可以提前针对一些的危险行为做防范。

解决方案

方案一(推荐)

只选择出现在 GROUP BY 后面的列,或者给列增加聚合函数。

方案二(不推荐)

sql_mode命令中,去掉 ONLY_FULL_GROUP_BY 来关闭此错误验证(需要重启MySQL)。

学习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 在严格模式下,不允许日期和月份为零
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.

my.ini中sql_mode推荐设置

[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

感谢:

  1. http://blog.csdn.net/tomcat_2014/article/details/53381529
  2. http://www.thinkphp.cn/topic/35116.html

转载声明:本站文章无特别说明,皆为原创,版权所有,转载请注明:Dy大叔的日常

转载自 MySQL : GROUP BY 报错及 sql_mode 相关思考 | XDY.ME@Dy大叔的日常

XDY.ME@Dy大叔的日常