目录
查询sql_mode设置，发现有ONLY_FULL_GROUP_BY
1. 异常现象
数据库在迁移后执行SQL出现了问题, 报错信息如下
SELECT list is not in GROUP BY clause and contains nonaggregated column 'cloud.exam_train_user.user_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
可以看出是因为sql_mode中设置了only_full_group_by模式引起的
在这个模式下，我们使用分组查询时，出现在
select字段后面的只能是group by后面的分组字段，或使用聚合函数包裹着的字段。
Oracled等数据库都不支持select target list中出现语义不明确的列，这样的语句在这些数据库中是会被报错的，所以从MySQL 5.7版本开始修正了这个语义，就是所说的ONLY_FULL_GROUP_BY语义。
MySQL 官方解释
MySQL 5.7.5 及更高版本默认开启了
ONLY_FULL_GROUP_BY的 SQL 模式
解决方法
方法1: 修改sql_mode
可以通过select @@sql_mode查出sql_mode以后去掉ONLY_FULL_GROUP_BY后复制过来
查询sql_mode设置，发现有ONLY_FULL_GROUP_BY
mysql> select version(),@@sql_mode; +------------+----------------------------------------------------------------+ | version() | @@sql_mode | +------------+----------------------------------------------------------------+ | 5.7.29-log | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +------------+----------------------------------------------------------------+ 1 row in set (0.00 sec)
修改sql_mode设置
临时修改
set sql_mode=' ' //改变已经存在的数据库sql_mode
set @@global.sql_mode=' ' //改变全局配置sql_mode
以上配置在重启服务以后失效
更改配置文件（推荐使用）
- linux系统更改/etc/my.cnf文件
- windows下配置文件是安装目录下的my.ini文件
如果有sql_mode=...的注释就把注释打开，如果没有就加上sql_mode=...
sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
方法2: select非group by的字段
直接修改修改sql_mode设置, 很多时候对于开发人员时没有权限的, DBA也需要很慎重地修改,因此对于开发人员, 我们可以在SQL层面进行优化
通过上文，我们知道，
如果sql_mode中设置了only_full_group_by模式
在这个模式下，我们使用分组查询时，出现在
select字段后面的只能是group by后面的分组字段，或使用聚合函数包裹着的字段。
因此，此时在含有Group by子句的查询语句中，对select关键字后的目标列，存在以下规律
- 使用group by 时，select 涉及的列要么是参与分组的列，要么列包含在聚合函数中
- where将对分组前的所有数据进行筛选。having将对分组后的一组数据搞事情。
例如：
select a,b,avg(c),sum(d) from 表 group by a,b
select非group by的字段
- 可以把group by的结果集当作一个表，然后从这里表里取数就可以了
- 例如：查询，订单表中订单号重复的订单信息，分组字段是orderno，select的信息不仅有orderno，还有其他信息
SELECT p.id, p.orgcode, p.idserial, p.username, p.orderno, p.createtime, p.payflag, p.businessorderno, p.paytime FROM pay_order_trade p, ( SELECT orderno, count(*) 重复次数 FROM pay_order_trade GROUP BY orderno HAVING count(1) > 1 ) s WHERE s.orderno = p.orderno ORDER BY p.orderno DESC;
MySQL 5.7: sql_mode
模式会影响 MySQL 支持的 SQL 语法以及它执行的数据验证检查。
这使得在不同环境中使用 MySQL 以及与其他数据库服务器一起使用 MySQL 变得更容易。
设置 SQL 模式
在MySQL 5.7的默认SQL模式包括以下模式：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。
这些模式已添加到 MySQL 5.7 中的默认 SQL 模式： ONLY_FULL_GROUP_BY和 STRICT_TRANS_TABLES模式已添加到 MySQL 5.7.5 中。该 NO_AUTO_CREATE_USER模式是在 MySQL 5.7.7 中添加的。的 ERROR_FOR_DIVISION_BY_ZERO， NO_ZERO_DATE以及 NO_ZERO_IN_DATE在MySQL 5.7.8添加模式。有关对默认 SQL 模式值的这些更改的其他讨论，请参阅 MySQL 5.7 中的 SQL 模式更改。
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后，不能用双引号来引用字符串，因为它被解释为识别符 |
更多信息参考: https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html
参考连接
https://dev.mysql.com/doc/refman/5.7/en/dynindex-sqlmode.html
https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html
https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html
