前言
在一次使用group by查询数据库时,遇到了问题。下面先搭建环境,然后让问题复现,最后分析问题。
一 问题复现
mysql版本
建表插入数据
表的结构
现在问题来了:我想查询上面表中每个部门年龄最大的人。
sql语句:select NAME,dept,MAX(age) from mytable group by dept;
此时会发现:查询结果中,dept 和 max(age) 是正确的,但是name却是错误的!!!因为34岁的101部门应该指向 li4 ,36岁的102部门应该是 tian7。
二 分析原因
对于上面的问题主要要注意两个方面:
1.为什么很多网上的帖子会出现这个sql语句查询报错的情况?
2.为什么这种sql语句能执行但是查询结果不正确?
下面针对这两个方面进行展开说明:
1.
有很多人可能使用上面的sql语句查询会报错,抛出这样的错误
翻译过来就是:select list的表达式1不在group by子句中,并且包含非聚合列“test.mytable.name”,该列在功能上不依赖于group by子句中的列;这与sql _mode=only_full _group _by不兼容
既然这里提到了sql_mode,那我就来查查sql_mode,如果你抛出了上面的错误,那么你的sql_mode中一定包含 ONLY_FULL_GROUP_BY
show variables like 'sql_mode';
什么是sql_mode?
官方解释:
The MySQL server can operate in different SQL modes, and can apply these modes differently for different clients, depending on the value of the sql_mode system variable. DBAs can set the global SQL mode to match site server operating requirements, and each application can set its session SQL mode to its own requirements
中文翻译:
mysql服务器可以在不同的SQL模式下运行,并且可以根据sql_模式系统变量的值,为不同的客户机应用这些模式。DBA可以设置全局SQL模式以匹配站点服务器操作需求,并且每个应用程序都可以将其会话SQL模式设置为自己的需求。
同时:
sql_mode是个很容易被忽视的变量,默认值是空值,在这种设置下是可以允许一些非法操作的,比如允许一些非法数据的插入。在生产环境必须将这个值设置为严格模式,所以开发、测试环境的数据库也必须要设置,这样在开发测试阶段就可以发现问题。
在我们使用group by进行查询语句时,如果查询字段不在聚合列中(也就是group by后面的列),实际上这种sql语句是错误的,也是没有意义的,mysql是可以不支持这种查询方式的。在我们生产境中,最好将sql_mode设置为严格模式,这样在开发阶段就能发现问题,排除错误、无效的sql语句。
ONLY_FULL_GROUP_BY就是规定了你不能使用错误、无效的group by去查询数据,所以,如果你的sql_mode是ONLY_FULL_GROUP_BY,那么执行上面那条sql语句就会抛出错误了。
2.
那为什么我使用上面的sql语句查询时,没有抛出错误,自然也就得到了解释,因为我当时的sql_mode 是这样的:
并不包含ONLY_FULL_GROUP_BY,同时,我们看到的查询结果是错误的,这也从反面说明了 mysql规定的一些sql_mode的必要性,就是因为某些sql语句会出现问题,但是又不能及时被发现。
所以在开发环境中,使用合适的严格的sql_mode 至关重要。
三 有哪些sql_mode?
官方说明先放在这: https://dev.mysql.com/doc/refman/5.6/en/sql-mode.html
简单介绍如下:
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后,不能用双引号来引用字符串,因为它被解释为识别符
四 如何设置sql_mode
sql_mode的设置分为局部(session)设置和全局(global)设置 下面是我从mysql官网直接拿过来的:
session就是指 你设置的sql_mode只会在你当前会话有效,当你注销用户,重新登录mysql后,仍然是你默认的sql_mode
global就是指全局的。
设置GLOBAL变量需要该SUPER权限,并影响从该时间开始连接的所有客户端的操作。设置SESSION变量仅影响当前客户端。每个客户端都可以随时更改其会话sql_mode值。
另外的一种设置方式:
直接修改mysql配置文件,打开mysql配置文件可以看到:(没有sql_mode就自己加)
这个sql_mode也就是我默认的,也可以在此处修改。
最后多说一点,虽然mysql规定了这么多sql_mode用来约束sql语句规范,但是并不是每一个sql_mode都要配上,我们要在合适的环境选择合适的配置。
另,原创,转载注明出处!