2021
11/23
17:39
你的位置:首页 > 数据库 > sql_mode详解,bug修复!!!!!

sql_mode详解,bug修复!!!!!

发布时间:2021-11-23 17:39:54

原标题:sql_mode详解,bug修复!!!!!

处理MySQL数据库工作中遇到关于sql_mode的情况,特学习记录一下

废话不多说直接上错误提示,哈哈哈 bug开始!!!!

INSERT INTO mmb_user (openid,mobile,wxmobile,password,age,realnm,nicknm,avatar,height,weight,sex,country,province,city,invdcd,unionid,create_date,valid) VALUES (?,?, ?,?,    ?, ?,?,?,?,?,?,?, ?,?, ?, ?, now(), '1' ) on duplicate key update nicknm = ?,   openid = ?,avatar = ?, update_date = now(),valid = '1'### Cause: java.sql.SQLSyntaxErrorException: In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'ygdb.mmb.id'; this is incompatible with sql_mode=only_full_group_by; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'ygdb.mmb.id'; this is incompatible with sql_mode=only_full_group_by

In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'ygdb.mmb.id'; this is incompatible with sql_mode=only_full_group_byIn aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'ygdb.mmb.id'; this is incompatible with sql_mode=only_full_group_by在没有分组依据的聚合查询中,选择列表的表达式#1包含未聚合的列“ygdb.mmb.id”;这与sql\u mode=only\u full\u group\u by不兼容

意思就我的mysql语法校验规则不合规

那我就在mysql中执行,查看一下,确实有ONLY_FULL_GROUP_BY

①命令:select @@GLOBAL.sql_mode或者select @@SESSION.sql_mode 

②命令:SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','')); 

③命令:select version(), @@sql_mode;

 完事!!!

下面还有惊喜哦,哈哈哈哈哈哈

在表里是没有问题了,但是在存储过程里面是对sql_mode无效的哦!!!需要在过程里面在加入这么一句就支持存储过程了,太棒了。bug修复完成!!!!

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

对你有帮助给个赞,哈哈哈哈哈哈哈,扫红包在送你个吃饭的券券




原标题:sql_mode详解,bug修复!!!!!

关键词:SQL,mysql,Insert

转载请保留本文网址: http://www.www.d5897.com/a/1270427.html
*特别声明:以上内容来自于网络收集,著作权属原作者所有,如有侵权,请联系我们: admin#www.d5897.com (#换成@)。
Baidu