MySQL5.7版本sql_mode=only_full_group_by问题解决办法

1055 – Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘csc_risk.a.DefaultDate’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by, Time: 0.035000s 

于是把部分数据迁移到本地进行测试,  发现sql语句可以执行

查看本地数据库版本

SELECT VERSION()

 查询线上数据库版本发现确实是5.7版本

5.7.24

原因分析:MySQL5.7版本默认设置了 mysql sql_mode = only_full_group_by 属性,导致报错。

知道原因就好办, 查询怎么解决 

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

一旦开启 only_full_group_by ,感觉,group by 将变成和 distinct 一样,只能获取受到其影响的字段信息,无法和其他未受其影响的字段共存,这样,group by 的功能将变得十分狭窄了

only_full_group_by 模式开启比较好。因为在 mysql 中有一个函数: any_value(field) 允许,非分组字段的出现(和关闭 only_full_group_by 模式有相同效果)。

1、查看sql_mode

SELECT @@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

2、去掉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';

3、上面是改变了全局sql_mode 对于新建的数据库有效, 对于已存在的数据库,则需要在对应的数据下执行:

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

以上方式临时解决没有问题的,数据库重启就需要重新设置

修改MySQL配置文件

Linux下my.cnf或Windows下My.ini

注意在[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

发布者:小站,转转请注明出处:http://blog.gzcity.top/4651.html

(0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
上一篇 2022年6月28日 09:38
下一篇 2022年7月5日 10:45

相关推荐

  • MySql 计算两个日期的时间差函数

    MySql计算两个日期的时间差函数 MySql计算两个日期的时间差函数TIMESTAMPDIFF用法:语法:TIMESTAMPDIFF(interval,datetime_expr1,datetime_expr2) 说明:返回日期或日期时间表达式datetime_expr1 和datetime_expr2the 之间的整数差。其结果的单位由interval …

    2022年12月15日
    15.6K39680
  • Pycharm激活码,Pycharm稳定专属激活码

    2018.2~2023版本用这个,下面激活码直接复制粘贴到activation code即可激活软件 FDXL1Y2811-eyJsaWNlbnNlSWQiOiJGRFhMMVkyODExIiwibGljZW5zZWVOYW1lIjoiQmVub2l0IE1lbmVuZGV6IiwiYXNzaWduZWVOYW1lIjoiIiwiYXNzaWduZWVFbW…

    学习笔记 2023年4月13日
    1.9K870
  • Java操作SFTP工具类,文件上传下载删除,获取列表目录

    Java操作SFTP工具类,文件上传下载删除,获取列表目录 需要依赖的Maven包 <!– SFTP –> <dependency> <groupId>com.jcraft</groupId> <artifactId>jsch</artifactId> <version>…

    2022年6月1日
    864800
  • Java性能调优:优化正则表达式的匹配效率

    在我们的日常业务开发中经常会涉及到使用正则表达式对数据进行处理,比如String的Split()方法,它根据方法中传入的正则表达式对字符串做分割处理。 但是我们是否真的了解正则表达式,它是如何匹配的?不同的匹配方式会带来怎样的效率差别?怎样才能做到效率最优? 本篇就对“如何优化正则表达式的匹配效率?”做深入探讨。   一、匹配的三种方式 看下面这个例子,我们…

    2022年7月6日
    31320
  • 反编译获取微信小程序源码(包含错误解决办法)

    本文章仅用于分享自己反编译的过程以及解决办法,切勿小程序反编译成功后做一些违法事情! 一. 前言 微信小程序的反编译听起来很屌,其实非常简单,就是纯粹的傻瓜式的操作。GitHub有写好的node.js脚本!要想拿到微信小程序源码,找到源文件在手机存放的位置就行,源文件拿到,用反编译脚本跑一下,微信小程序代码包里的所有文件、资源就出来了。 二. 微信小程序的小…

    学习笔记 2022年11月15日
    937610

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注

评论列表(10条)

  • binance
    binance 2024年8月17日 12:22

    Thank you for your sharing. I am worried that I lack creative ideas. It is your article that makes me full of hope. Thank you. But, I have a question, can you help me?

  • أنابيب النحاس
    أنابيب النحاس 2024年9月25日 17:14

    Galvanized Steel Pipes in Iraq ElitePipe Factory is renowned as one of the best and most reliable manufacturers of galvanized steel pipes in Iraq. Our galvanized steel pipes are produced with precision and high-quality standards, ensuring durability and resistance to corrosion. These pipes are ideal for a variety of applications, including water supply, construction, and industrial uses. With our state-of-the-art facilities and commitment to excellence, ElitePipe Factory has established a reputation for delivering top-notch products that meet the needs of our clients effectively. Discover more about our galvanized steel pipes by visiting our website at ElitePipe Iraq.

  • sex porn mom
    sex porn mom 2024年10月2日 11:19

    He reaches for and cups the wiggling low hangers of his teenage son. [url=https://arturzasada.pl/]polskie porno[/url] “I don’t wanna jack-off, dad.” He says flabbergasted to his dad as he turns off the tap to the hot and chilly water in the shower.

  • gay sex porn
    gay sex porn 2024年10月3日 05:41

    “Yes! Yes! I know you understand.” His father proudly exclaims. “You guys jerk off together, these days?” His dad asks, “Back when I was in high school me and several of my friends would jerk-off in our trucks in the parking lot. We were so horny we could barely sit in our seats. You ever do that Garrett?”

  • porno dla dzieci
    porno dla dzieci 2024年10月5日 19:40

    Exactly a week after, daddy texted me with a different tone saying, “ get your ass ready tonight cause daddy is coming to your place tonight.” He was usually really sweet and nice, I was shot when I got the message. But automatically I said, “ yes daddy!” I guess I am a slutty whore for him right at the beginning as I knew my place where is always going to be inferior. He reaches for and cups the wiggling low hangers of his teenage son.

  • gay sex porn
    gay sex porn 2024年10月7日 05:41

    “What are ya now, by the way?” I used my teeth to strip off daddy’s black sheer socks. I placed them in my mouth and washed them with my saliva. I swallowed every single sweat and stink from these socks. It takes so well. I rolled them into a ball and stuck them into my jockstrap where my small dick and balls were.

  • free porn sex
    free porn sex 2024年10月11日 09:27

    His father strokes his own cock in the shower, the dew from his cock mixes with the drops condensing on the glass.

  • dzieciece porno
    dzieciece porno 2024年10月13日 21:10

    Exactly a week after, daddy texted me with a different tone saying, “ get your ass ready tonight cause daddy is coming to your place tonight.” He was usually really sweet and nice, I was shot when I got the message. But automatically I said, “ yes daddy!” I guess I am a slutty whore for him right at the beginning as I knew my place where is always going to be inferior.

  • "oppna binance-konto
    "oppna binance-konto 2024年12月4日 00:47

    Thank you for your sharing. I am worried that I lack creative ideas. It is your article that makes me full of hope. Thank you. But, I have a question, can you help me?

  • 100 USDT
    100 USDT 2025年2月5日 02:20

    Can you be more specific about the content of your article? After reading it, I still have some doubts. Hope you can help me.