mysql删除重复记录，保存Id最小的一条

admin

方法1：
1、创建一个临时表，选取需要的数据。
/ v9 j8 p5 V/ H& l' _5 r  ?2、清空原表。
3、临时表数据导入到原表。
9 f! g, W" T% E0 u2 b. E* ~, ]4、删除临时表。
, ]8 C4 C$ h# U- r/ k! R8 Tmysql> select * from student;
+----+------+
% R& I! }9 C# p9 o| ID | NAME |
' ?& |/ X" O" j/ l" q+----+------+
| 11 | aa |
3 F! X; D4 G. P: U% D! r| 12 | aa |
| 13 | bb |
+ ?  z+ d- v* @  j  j! I| 14 | bb |
; p4 S+ o4 E: o+ Z- B6 _: [0 s| 15 | bb |
| 16 | cc |
+----+------+
6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
& }# G8 k9 o$ \' ]' P+ o5 k3 f/ bQuery OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> truncate table student;
Query OK, 0 rows affected

mysql> insert into student select * from temp;
: _' h3 b- u2 hQuery OK, 3 rows affected
# |  P+ y2 C; O2 mRecords: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
( c: t, A4 D# r4 M) J( ]3 Z$ F$ T9 ^. x+----+------+
8 ?% s9 _  Q$ w3 H& }1 z4 C| ID | NAME |
+----+------+
| 11 | aa |
5 D0 e. o7 E9 ]+ V; o| 13 | bb |
* e$ H8 V# J7 G6 l' K  T| 16 | cc |
+----+------+
3 rows in set

mysql> drop temporary table temp;
! X4 `. d- ]+ r% z, A5 tQuery OK, 0 rows affected
这个方法，显然存在效率问题。

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
! @% B& U1 A8 h3 Z/ z+ g7 B  {; f# p! Mmysql> create temporary table temp as select min(id) as MINID from student group by name;
Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> delete from student where id not in (select minid from temp);
4 ]* ]# A% Y" M, g& q4 V6 OQuery OK, 3 rows affected

mysql> select * from student;
! ~7 d0 q9 e- d( N# o' u+----+------+
& G5 H! x% v' M| ID | NAME |
+----+------+
| 11 | aa |
) O+ \0 v9 A4 F, f) M| 13 | bb |
. O) O$ j" D& y  j& j# H| 16 | cc |
- i1 \# }+ Y& y$ H+----+------+
3 rows in set

---

方法3：直接在原表上操作，容易想到的sql语句如下：

mysql> delete from student where id not in (select min(id) from student group by name);
执行报错：1093 - You can't specify target table 'student' for update in FROM clause
' \8 t* \1 O, K7 {8 `原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
4 Q3 }, G; o1 V( S4 s3 |5 s/ j3 @怎么规避这个问题？
" [# ?& g, _; x1 Z0 v9 ?/ v/ k, F再加一层封装，如下：
' u8 p; O& c. P/ Hmysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);
Query OK, 3 rows affected

mysql> select * from student;
+ q* \5 g& ~! z5 K, g* G' X+----+------+
/ t* o% ^/ X! Y. ?* _' Y7 [) o| ID | NAME |
+----+------+
| 11 | aa |
| 13 | bb |
! U. Z' j5 }. c5 `| 16 | cc |
+----+------+
3 rows in set

方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);


# x& ~: U0 Q, f: C) K9 G4 d) J