由group by引发的sql_mode的学习
前言
在一次使用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都要配上，我们要在合适的环境选择合适的配置。
另，原创，转载注明出处！
