mysql only_full_group_by 问题
来源:互联网 发布:练字 知乎 编辑:程序博客网 时间:2024/05/18 08:47
sql_mode=only_full_group_by研读
MySQL 5.7.5后only_full_group_by成为sql_mode的默认选项之一,这可能导致一些sql语句失效。
比如下表game:
执行sql: select name, group_id from game group by group_id
(记为sql_make_group)
返回:
- 1
解决方法
把group by字段
group_id
设成primary key 或者 unique NOT NULL。这个方法在实际操作中没什么意义。使用函数
any_value
把报错的字段name
包含起来。如,select any_value(name), group_id from game group by group_id
。在配置文件my.cnf中关闭sql_mode=ONLY_FULL_GROUP_BY.。msqyl的默认配置是
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
去掉,也可以去掉所有选项设置成sql_mode=
,如果你确信其他选项不会造成影响的话。
mysql官方文档有详细解译。
执行成功后,返回结果应该是
为什么默认设置ONLY_FULL_GROUP_BY限制?
对于上述的报错信息,我的理解是select
字段里包含了没有被group by
条件唯一确定的字段name。
因为执行sql_make_group语句实际上把两行纪录小明
和小花
合并成一行,搜索引擎不知道该返回哪一条,所以认为这样的sql是武断的(arbitrary).
解决办法2和3都是禁止检查返回结果的唯一性。
进阶讨论
上述办法可以解决报错的问题,但也说明sql语句本身有逻辑问题。name
字段不应该出现在返回结果,因为它是不确定的。
考虑这样的需求:按group_id
分组后,找出每组得分最少的人。
执行sql: select any_value(name) as name, group_id, min(score) as score from game group by group_id order by min(score)
返回结果
B组的name
是小明
(因为小明
的id更小),而期望结果应该是小花
。
所以单纯使用group by
无法实现这样的需求。可以使用临时表的方法:
select id, name,t.group_id, t.score from (select group_id, min(score) as score from game group by group_id order by min(score)) t inner join game on t.group_id=game.group_id and t.score=game.score
如果要实现更为复杂的需求,可以考虑使用group_concat
,请参考这里。
Laravel的相关配置
把my.cnf中的ONLY_FULL_GROUP_BY
去掉后,laravel的运行结果仍然报错。翻过代码后发现,laravel在每一次请求都会设定sql_mode。实现代码在Illuminate\Database\Connectors\MySqlConnector
,不同版本稍有不同。
5.2和5.3可以在config/databse.php修改配置。
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
当strict=true
(默认)即采用mysql的默认配置,当然也包含ONLY_FULL_GROUP_BY
。
当strict=false
即设置sql_mode=NO_ENGINE_SUBSTITUTION
。
也可以自定义,在modes
下添加相应的配置项。modes
的优先级比strict
高。
转载链接http://blog.csdn.net/allen_tsang/article/details/54892046
- mysql only_full_group_by 问题
- mysql only_full_group_by
- MySQL 5.7版本sql_mode=only_full_group_by问题
- MySql版本问题sql_mode=only_full_group_by解决方案
- MySQL 5.7.9版本sql_mode=only_full_group_by问题
- 解压版MYSQL出现only_full_group_by问题,更改配置文件没效果
- 解决macOS下mysql的sql_mode=only_full_group_by问题
- MySQL 5.7.9版本sql_mode=only_full_group_by问题
- MySQL 5.7.9版本sql_mode=only_full_group_by问题
- mysql 5.7 this is incompatible with sql_mode=only_full_group_by 问题
- MySQL 5.7.9版本sql_mode=only_full_group_by问题
- MySQL 5.7.9版本sql_mode=only_full_group_by问题
- MySQL 报错:5.7版本sql_mode=only_full_group_by问题
- MySQL 5.7.9版本sql_mode=only_full_group_by问题
- 去掉mysql ONLY_FULL_GROUP_BY!
- mysql的only_full_group_by
- MySQL新模式only_full_group_by
- mysql:sql_mode=“only_full_group_by”
- Android Oreo 常见问题 2.0 | Android 开发者 FAQ Vol.9
- 以用户登录、注册、添加删除修改为例,解析jdbcTemplate使用方式以及spring注解实现CRUD
- rsync+inotify逐一文件监听并实时同步-附完美案例
- Week01_day03 分支 选择判断 循环语句
- 分布式唯一ID极简教程
- mysql only_full_group_by 问题
- Android 通过一个app 启动另一个app
- 【C++】关于变量在for循环内外定义的思考
- 企服三会·PPT | 中国软件网曹开彬:洞见2018中国CRM趋势洞察报告
- 企服三会·PPT | 中国软件网曹开彬:洞见2018中国HR服务趋势洞察报告
- 进程管理API之find_task_by_vpid
- Jenkins 学习部署过程
- 为什么OpenStack用户名单越来越豪华?
- 百度地图李东旻:中国地图行业最终只会剩下一两个玩家/阿里云CDN再降价25%