MySQL里面的group by问题浅析

今天做一个备份看板的时候,无意中写了如下的SQL,当时看到之后就有点疑惑了。

mysql> select backup_date ,count(*) piece_no from redis_backup_result;

+-------------+----------+

| backup_date | piece_no |

+-------------+----------+

| 2018-08-14 | 40906 |

+-------------+----------+

1 row in set (0.03 sec)

一天之内肯定没有这么多的记录,明显不对,到底是哪里出了问题呢。

自己仔细看了下SQL,发现是没有加group by

我们随机查出10条数据。

mysql> select backup_date from redis_backup_result limit 10;

+-------------+

| backup_date |

+-------------+

| 2018-08-14 |

| 2018-08-14 |

| 2018-08-14 |

| 2018-08-15 |

| 2018-08-15 |

| 2018-08-15 |

| 2018-08-15 |

| 2018-08-15 |

| 2018-08-15 |

| 2018-08-15 |

+-------------+

10 rows in set (0.00 sec)

这个梗很多同学都知道,是在早期的版本中sql_mode默认为null,不会校验这个部分,从语法角度来说,是允许的,但是到了高版本,比如5.7之后是不支持的。

mysql> show variables like 'sql_mode%';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| sql_mode | |

+---------------+-------+

1 row in set (0.01 sec)

添加group by之后,结果就符合预期了。

mysql> select backup_date ,count(*) piece_no from redis_backup_result group by backup_date;

+-------------+----------+

| backup_date | piece_no |

+-------------+----------+

| 2018-08-14 | 3 |

| 2018-08-15 | 121 |

| 2018-08-16 | 184 |

| 2018-08-17 | 3284 |

| 2018-08-18 | 7272 |

| 2018-08-19 | 7272 |

| 2018-08-20 | 7272 |

| 2018-08-21 | 7272 |

| 2018-08-22 | 8226 |

+-------------+----------+

9 rows in set (0.06 sec)

但是问题到了这里,我比较奇怪上面的逻辑,到底是怎么解析的,看起来是SQL解析了第一行,然后输出了count(*)的操作。

显然这个是从执行计划中无法得到的信息。

mysql> explain extended select backup_date ,count(*) piece_no from redis_backup_result;

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

| 1 | SIMPLE | redis_backup_result | NULL | ALL | NULL | NULL | NULL | NULL | 38351 | 100.00 | NULL |

+----+-------------+---------------------+------------+------+---------------+------+---------+------+-------+----------+-------+

1 row in set, 2 warnings (0.00 sec)

mysql> show warnings;

| Level | Code | Message

| Warning | 1681 | 'EXTENDED' is deprecated and will be removed in a future release. |

| Note | 1003 | /* select#1 */ select `devopsdb`.`redis_backup_result`.`backup_date` AS `backup_date`,count(0) AS `piece_no` from `devopsdb`.`redis_backup_result` |

2 rows in set (0.00 sec)

我们换个思路。添加sql_mode的约束。

mysql> set session 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';

Query OK, 0 rows affected, 1 warning (0.00 sec)

可以看到这个表有4万多的记录。

mysql> select count(*)from redis_backup_result;

+----------+

| count(*) |

+----------+

| 40944 |

+----------+

1 row in set (0.01 sec)

为了验证,我们可以使用_rowid的方式来做初步的验证。

mysql> select _rowid from redis_backup_result limit 5;

+--------+

| _rowid |

+--------+

| 117 |

| 118 |

| 119 |

| 120 |

| 121 |

+--------+

5 rows in set (0.00 sec)

然后可以实现一个初步的思路。

mysql> select _rowid,count(*)from redis_backup_result;

+--------+----------+

| _rowid | count(*) |

+--------+----------+

| 117 | 41036 |

+--------+----------+

1 row in set (0.03 sec)

然后借助rownum来实现。

mysql> SELECT @rowno:=@rowno+1 as rowno,r._rowid from redis_backup_result r ,(select @rowno:=0) t limit 20;

+-------+--------+

| rowno | _rowid |

+-------+--------+

| 1 | 117 |

| 2 | 118 |

| 3 | 119 |

| 4 | 120 |

| 5 | 121 |

| 6 | 122 |

| 7 | 123 |

| 8 | 124 |

| 9 | 125 |

| 10 | 126 |

| 11 | 127 |

| 12 | 128 |

| 13 | 129 |

| 14 | 130 |

| 15 | 131 |

| 16 | 132 |

| 17 | 133 |

| 18 | 134 |

| 19 | 135 |

| 20 | 136 |

+-------+--------+

20 rows in set (0.00 sec)

写一个完整的语句。

mysql> SELECT @rowno:=@rowno+1 as rowno,r._rowid ,backup_date,count(*) from redis_backup_result r ,(select @rowno:=0) t ;

+-------+--------+-------------+----------+

| rowno | _rowid | backup_date | count(*) |

+-------+--------+-------------+----------+

| 1 | 117 | 2018-08-14 | 41061 |

+-------+--------+-------------+----------+

1 row in set (0.02 sec)

很明显是第1行的记录,然后做了count(*)的操作。

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2018-08-22

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏FreeBuf

如何对已损坏的SQLite数据库取证分析?

SQLite是当今最流行的数据库之一,许多移动应用台式计算机以及便携式笔记本上都用它来存储数据(例如桌面工具、浏览器以及社交媒体软件等),因此SQLite在电子...

46490
来自专栏杨建荣的学习笔记

MySQL中的derived table(r12笔记第47天)

初始MySQL中的derived table还是在一个偶然的问题场景中。 下面的语句在执行的时候抛出了错误。 UPDATE payment_data rr ...

40550
来自专栏Laoqi's Linux运维专列

Mysql 通过全量备份和binlog恢复整体数据

1.3K70
来自专栏IT大咖说

2018年,让你的数据库变更快的十个建议

摘要 大多数网站的内容都存在数据库里,用户通过请求来访问内容。数据库非常的快,有许多技巧能让你优化数据库的速度,使你不浪费服务器的资源。在这篇文章中,我收录了十...

43370
来自专栏杨建荣的学习笔记

MySQL Online DDL(二)(r11笔记第88天)

对于Online DDL,之前简单分析了一些场景MySQL中的Online DDL(第一篇)(r11笔记第3天),其实有一个很关键的点没提到,那就是online...

37290
来自专栏PHP在线

Mysql存储引擎中InnoDB与Myisam的区别

1. 事务处理innodb 支持事务功能,myisam 不支持。 Myisam 的执行速度更快,性能更好。 2. select ,update ,insert...

32150
来自专栏乐沙弥的世界

临时表空间的管理与受损恢复

      Oracle 临时表空间是Oracle数据库的重要组成部分,尽管该部分并没有cont体系结构上得以展现,但其重要地位也是不容忽视的。尤其是 对于大...

12130
来自专栏跟着阿笨一起玩NET

SQL语句使用总结(一)

1>. FROM 2>. WHERE 3>. GROUP BY 4>. HAVING 5>. SELECT 6>. ORDER BY

6810
来自专栏杨建荣的学习笔记

MySQL中使用pt-osc的一些小结

Percona的pt-osc工具算是DBA的一个福利工具。想想一个数据量有些大的表,在上面做DDL操作真是一种煎熬,我们也基本理解了这是一种空间换时间的...

14310
来自专栏互联网技术栈

MySQL 锁机制——必知必会

MyISAM表的读和写是串行的,但这是就总体而言的。在一定条件下,MyISAM表也支持查询和插入操作的并发进行。

15660

扫码关注云+社区

领取腾讯云代金券