mysql中过滤重复记录之distinct用法

本文章主要是讲述了关于利用mysql中distinct来过滤一些重启的记录,有需要的朋友可参考一下.

下面我们就通过几个简单的 Query 示例来展示一下 DISTINCT 的实现.

1.首先看看通过松散索引扫描完成 DISTINCT 的操作,代码如下:

1. sky@localhost : example 11:03:41> EXPLAIN SELECT DISTINCT group_id  
2.     -> FROM group_messageG 
3. *************************** 1. row *************************** 
4.            id: 1  --phpfensi.com 
5.   SELECT_type: SIMPLE 
6.         table: group_message 
7.          type: range 
8. possible_keys: NULL 
9.           key: idx_gid_uid_gc 
10.       key_len: 4 
11.           ref: NULL 
12.          rows: 10 
13.         Extra: Using index for group-by 
14. 1 row in set (0.00 sec) 

我们可以很清晰的看到,执行计划中的 Extra 信息为“Using index for group-by”,这代表什么意思？为什么我没有进行 GROUP BY 操作的时候，执行计划中会告诉我这里通过索引进行了 GROUP BY 呢？其实这就是于 DISTINCT 的实现原理相关的,在实现 DISTINCT的过程中，同样也是需要分组的，然后再从每组数据中取出一条返回给客户端。而这里的 Extra 信息就告诉我们，MySQL 利用松散索引扫描就完成了整个操作。当然，如果 MySQL Query Optimizer 要是能够做的再人性化一点将这里的信息换成“Using index for distinct”那就更好更容易让人理解了，呵呵。

2.我们再来看看通过紧凑索引扫描的示例,代码如下:

1. sky@localhost : example 11:03:53>  EXPLAIN SELECT DISTINCT user_id  
2.     -> FROM group_message 
3.     -> WHERE group_id = 2G 
4. *************************** 1. row *************************** 
5.            id: 1 
6.   SELECT_type: SIMPLE 
7.         table: group_message 
8.          type: ref 
9. possible_keys: idx_gid_uid_gc 
10.           key: idx_gid_uid_gc 
11.       key_len: 4 
12.           ref: const 
13.          rows: 4 
14.         Extra: Using WHERE; Using index 
15. 1 row in set (0.00 sec) 

这里的显示和通过紧凑索引扫描实现 GROUP BY 也完全一样,实际上,这个 Query 的实现过程中,MySQL 会让存储引擎扫描 group_id = 2 的所有索引键,得出所有的 user_id,然后利用索引的已排序特性,每更换一个 user_id 的索引键值的时候保留一条信息,即可在扫描完所有 gruop_id = 2 的索引键的时候完成整个 DISTINCT 操作.

3.下面我们在看看无法单独使用索引即可完成 DISTINCT 的时候会是怎样,代码如下:

1. sky@localhost : example 11:04:40> EXPLAIN SELECT DISTINCT user_id  
2.     -> FROM group_message 
3.     -> WHERE group_id > 1 AND group_id < 10G 
4. *************************** 1. row *************************** 
5.            id: 1 
6.   SELECT_type: SIMPLE 
7.         table: group_message 
8.          type: range 
9. possible_keys: idx_gid_uid_gc 
10.           key: idx_gid_uid_gc 
11.       key_len: 4 
12.           ref: NULL 
13.          rows: 32 
14.         Extra: Using WHERE; Using index; Using temporary 
15. 1 row in set (0.00 sec) 

当 MySQL 无法仅仅依赖索引即可完成 DISTINCT 操作的时候，就不得不使用临时表来进行相应的操作了。但是我们可以看到，在 MySQL 利用临时表来完成 DISTINCT 的时候，和处理 GROUP BY 有一点区别，就是少了 filesort。实际上，在 MySQL 的分组算法中，并不一定非要排序才能完成分组操作的，这一点在上面的 GROUP BY 优化小技巧中我已经提到过了。实际上这里 MySQL 正是在没有排序的情况下实现分组最后完成 DISTINCT 操作的，所以少了 filesort 这个排序操作。

4.最后再和 GROUP BY 结合试试看,代码如下:

1. sky@localhost : example 11:05:06> EXPLAIN SELECT DISTINCT max(user_id)  
2.     -> FROM group_message 
3.     -> WHERE group_id > 1 AND group_id < 10 
4.     -> GROUP BY group_idG 
5. *************************** 1. row *************************** 
6.            id: 1 
7.   SELECT_type: SIMPLE 
8.         table: group_message 
9.          type: range 
10. possible_keys: idx_gid_uid_gc 
11.           key: idx_gid_uid_gc 
12.       key_len: 4 
13.           ref: NULL 
14.          rows: 32 
15.         Extra: Using WHERE; Using index; Using temporary; Using filesort 
16. 1 row in set (0.00 sec) 

最后我们再看一下这个和 GROUP BY 一起使用带有聚合函数的示例，和上面第三个示例相比，可以看到已经多了 filesort 排序操作了，正是因为我们使用了 MAX 函数的缘故。要取得分组后的 MAX 值，又无法使用索引完成操作，只能通过排序才行了。

在使用mysql时，有时需要查询出某个字段不重复的记录，虽然mysql提供有distinct这个关键字来过滤掉多余的重复记录只保留一条，但往往只用它来返回不重复记录的条数，而不是用它来返回不重记录的所有值。其原因是distinct只能返回它的目标字段，而无法返回其它字段，这个问题让我困扰了很久

下面先来看看例子,代码如下:

1. table  
2. id name  
3. 1 a  
4. 2 b  
5. 3 c  
6. 4 c  
7. 5 b 

库结构大概这样,这只是一个简单的例子,实际情况会复杂得多,比如我想用一条语句查询得到name不重复的所有数据,那就必须使用distinct去掉多余的重复记录,代码如下:

select distinct name from table

得到的结果是:

1. name  
2. a  
3. b  
4. c 

好像达到效果了,可是,我想要得到的是id值呢？改一下查询语句吧,代码如下:

select distinct name, id from table

结果会是如下代码:

1. id name  
2. 1 a  
3. 2 b  
4. 3 c  
5. 4 c  
6. 5 b 

distinct怎么没起作用？作用是起了的,不过他同时作用了两个字段,也就是必须得id与name都相同的才会被排除,我们再改改查询语句,代码如下:

select id, distinct name from table

很遗憾,除了错误信息你什么也得不到,distinct必须放在开头,难到不能把distinct放到where条件里？能,照样报错.

试了半天，也不行，最后在mysql手册里找到一个用法，用group_concat(distinct name)配合group by name实现了我所需要的功能，兴奋，天佑我也，赶快试试

报错,郁闷,连mysql手册也跟我过不去,先给了我希望,然后又把我推向失望,好狠那.

再仔细一查，group_concat函数是4.1支持，晕，我4.0的。没办法，升级，升完级一试，成功。。。。。。

终于搞定了，不过这样一来，又必须要求客户也升级了,突然灵机一闪，既然可以使用group_concat函数，那其它函数能行吗？赶紧用count函数一试，成功，我。。。。。。。想哭啊，费了这么多工夫。。。。。。。。原来就这么简单。。。。。。

现在将完整语句放出,代码如下:

select id,name, count(distinct name) from table group by name

结果,代码如下:

1. id name count(distinct name)  
2. 1 a 1  
3. 2 b 1  
4. 3 c 1 

最后一项是多余的,不用管就行了,目的达到,哦,对,再顺便说一句，group by 必须放在 order by 和 limit之前，不然会报错,差不多了,我继续忙碌。。。。。。

原文:这篇文章是我从别人那里转来的，在自己的项目中也遇到了这样的问题，我的sql语句是向下面这样写的:

1. SELECT attention_join.memberID,nickName,headpic,attention_join.time 
2.  FROM attention_join 
3.      JOIN member ON attention_join.memberID = member.memberID 
4.      JOIN member_meta ON member.memberID = member_meta.memberID 
5.  GROUP BY attention_join.memberID 
6.  ORDER BY attention_join.time DESC 

意思是 '按 加入/关注 小组的时间降序，查出小组内的会员' ，但是语句里并没有用到向上文说的count()关键字，这个也让我很不解，mysql没有详细的学习过，它的 group by 关键字的用法好像和 sqlserver 的有很大不同，这个等有时间了，在查查看吧，现在没有时间了

哦,对了,我的mysql版本是:服务器版本: 5.1.54-1 ubuntu4,协议版本: 10