MySQL里面的group by问题浅析
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(*)的操作。