处理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_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_by
在没有分组依据的聚合查询中，选择列表的表达式#1包含未聚合的列“ygdb.mmb.id”；这与sqlu mode=onlyu fullu groupu 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_mysql 42000

完事！！！

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

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

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

sql-mode_mysql 42000

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

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请发送邮件至举报，一经查实，本站将立刻删除。
转载请注明出处: https://daima100.com/5684.html

sql-mode_mysql 42000

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

相关推荐

发表回复