mysql group by rowid_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(*)的操作。