lecms-恒哥博客

MySQL -- 【Mysql5.7版本sql_mode=only_full_group_by问题】

发布时间:3年前 热度: 573 ℃ 评论数: 0

下载安装的是最新版的mysql5.7.x版本,默认是开启了 only_full_group_by 模式的,但开启这个模式后,原先的 group by 语句就报错;


具体出错提示:

[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


解决方法一(服务重启后失效):

        ①查看sql_mode

select @@global.sql_mode;

查询出来的值为: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  

        ②去掉ONLY_FULL_GROUP_BY,重新设置值。

set @@global.sql_mode =‘STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION‘;


解决方法二(一劳永逸):

         ①windows

         windows环境直接修改mysql.ini中的sql_mode

         ②Linux

         ubuntu环境下默认配置在:/etc/mysql/mysql.conf.d   下的mysqld.cnf,在[mysqld]下增加:

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

         重启数据库

/etc/init.d/mysql restart


      

Mysql5.7、sql_mode

吐槽一下
  • 消灭零回复

手机扫码访问