mysql 查询sql mode_MySQL5.7.x的sql_mode参数ONLY_FULL_GROUP_BY引发的查询异常

最近把服务器上的MySQL5.6.x升级到MySQL5.7.x，使用navicat查询SQL时，虽然可以查询到正确的结果集，可是在正确结果集下方出现以下一段异常信息：html

[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROFILING.SEQ' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by mysql

这是为何呢？能够确定SQL语句没有问题，在MySQL5.6.x上执行相同的SQL语句没有任何异常，难道升级后MySQL5.7.x有细节更新？sql

Linux版本的MySQL5.x中sql_mode默认为空，例如日期字段能够插入'0000-00-00 00:00:00'这样的值，还有若是要插入的字段长度超过列定义的长度，那么MySQL不会终止操做，而是会自动截断后面的字符继续插入操做，以下例：

mysql> create table t5 (c1 char(3));

mysql> insert into t5 values('abcd');

mysql> select * from t5;

+------+

| c1 |

+------+

| abc |

+------+

1 row in set (0.00 sec)

咱们发现插入的字符被自动截断了，可是若是咱们本意但愿若是长度超过限制就报错，那么咱们能够设置sql_mode为STRICT_TRANS_TABLES，以下：

mysql> set session sql_mode='STRICT_TRANS_TABLES'

这样咱们再执行一样的操做，mysql就会告诉咱们插入的值太长，操做被终止，以下：

mysql> insert into t5 values('abcd');

ERROR 1406 (22001): Data too long for column 'c1' at row 1

经过上面的例子，对sql_mode的简单设置，便能解决这个问题，即能控制sql规范，也可以识别"这个字符。数据库

Windows版本的MySQL5.x的配置文件中是默认取消ONLY_FULL_GROUP_BY检查。

参数以下：

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"服务器

对于强迫症人来讲眼里是不容沙子的，这是咱们决不容许的状况，那么咱们怎么清除这段异常信息呢？session

分析ide

根据异常信息分析发现，这问题主要是因为sql_mode引发，因此先查看sql_mode，再除ONLY_FULL_GROUP_BY参数。函数

解决方案 1this

SELECT @@sql_mode; 或 SELECT @@global.sql_mode; //全局配置

-- 修改SQL_MODE

SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

-- 或者只清除ONLY_FULL_GROUP_BY

SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'));

或

SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

注意：这种方法修改是会话级别的，重启MySQL服务后，再执行SQL语句仍然会出现异常信息。code

解决方案 2

修改MySQL配置文件

Windows：my.ini

Linux：my.cnf

sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

修改成：

sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

sql_mode的做用是什么呢？

模式定义MySQL会支持哪些SQL语法。以及应执行哪一种数据验证检查。

最终达到的目标：MySQL服务器可以工做在不一样的SQL模式下，并能针对不一样的客户端以不一样的方式应用这些模式。这样，应用程序就能对服务器操做进行量身定制以知足本身的需求。这类模式定义了MySQL应支持的SQL语法，以及应该在数据上执行何种确认检查。从而可以在众多不一样的环境下，与其余数据库服务器一块儿更容易地使用MySQL。从MySQL 4.1 开始，也能在启动以后，使用SET[SESSION|GLOBAL]sql_mode='mode1,mode2…'语句，经过设置sql_mode变量更改模式。

那么ONLY_FULL_GROUP_BY是起什么做用的？

对于GROUP BY聚合操做，若是在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，由于列不在GROUP BY从句中，因此对于设置了这个mode的数据库，在使用group by 的时候，就要用MAX(),SUM(),ANT_VALUE()这种聚合函数，才能完成GROUP BY 的聚合操做。

sql_mode经常使用值

sql_mode值

描述

TANS

宽松模式，对插入数据进行校验，若是不符合定义类型或长度，对数据类型调整或截断保存，报warning警告

STRICT_TRANS_TABLES

严格模式，若是不能将给定的值插入到事务表中，则放弃该语句。对于非事务表，若是值出如今单行语句或多行语句的第1行，则放弃该语句

TRADITIONAL

严格模式，Make MySQL的行为象“传统”SQL数据库系统。该模式的简单描述是当在列中插入不正确的值时“给出错误而不是警告”。注释：一旦发现错误当即放弃INSERT/UPDATE。若是你使用非事务存储引擎，这种方式不是你想要的，由于出现错误前进行的数据更改不会“滚动”，结果是更新“只进行了一部分”。

说明：若是把sql_mode的值设置成后面的两个值(也就是咱们说的严格模式)，那么当在列中插入或更新不正确的值时，mysql将会给出错误，而且放弃insert/update操做。在咱们的通常应用中建议使用这两种模式，而不是使用默认的空或ANSI模式。可是须要注意的问题是，若是数据库运行在严格模式下，而且你的存储引擎不支持事务，那么有数据不一致的风险存在，好比一组sql中有两个dml语句，若是后面的一个出现了问题，可是前面的已经操做成功，那么mysql并不能回滚前面的操做。所以说设置sql_mode须要应用人员权衡各类得失，从而获得一个合适的选择

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后，不能用双引号来引用字符串，由于它被解释为识别符