处理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"；这与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;
完事！！！
下面还有更多惊喜哈。
[En]
There are more surprises down there, ha.
在表里是没有问题了，但是在存储过程里面是对sql_mode无效的哦！！！需要在过程里面在加入这么一句就支持存储过程了，太棒了。bug修复完成！！！！
SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
如果对你有帮助，给我一个赞美，哈，给你扫红包，给你一张晚餐代金券。
[En]
If it’s helpful to you, give me a compliment, ha, sweep red packets and give you a voucher for dinner.
Original: https://www.cnblogs.com/diaoyulin/p/15597288.html
Author: yl_diao
Title: sql_mode详解，bug修复！！！！！
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/508004/
转载文章受原作者版权保护。转载请注明原作者出处！