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

admin

方法1：
4 }+ H$ h' P$ ~! G1、创建一个临时表，选取需要的数据。
0 q3 K+ w! J+ k; }0 }2、清空原表。
3、临时表数据导入到原表。
$ u; R+ S  G3 r( s$ b% I4、删除临时表。
  |" K2 l% Q3 ^1 t6 Amysql> select * from student;
  G8 K0 n4 g) z: A+----+------+
| ID | NAME |
+----+------+
) P3 A) o+ u/ G, }: N. a% W$ u) J) h| 11 | aa |
5 R2 `8 q/ U, [" K7 [8 d/ L2 f| 12 | aa |
| 13 | bb |
; o* b9 n. f/ J; w, }2 \9 q: w| 14 | bb |
' k& h% e$ w; ?. t| 15 | bb |
# x- T0 ^. G7 V& x, d9 ]0 a) I; w| 16 | cc |
+----+------+
6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
Query 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;
) a: |5 O0 K, U8 [( gQuery OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
+----+------+
! z7 ]5 c' a( u' i8 T* B9 t: q| ID | NAME |
/ m/ H/ m. P6 z2 |9 [& U  ~+ ~+----+------+
| 11 | aa |
) }1 l1 A! n' z( ?8 ^' R8 u| 13 | bb |
+ {8 Q  ~& J7 |$ W$ D8 ~, [| 16 | cc |
+----+------+
5 l( `3 P4 H2 w9 B: d  N4 d3 rows in set

mysql> drop temporary table temp;
1 `2 {* ?' s0 d( M/ @0 ?# {Query OK, 0 rows affected
; h0 B8 r2 H( D6 T这个方法，显然存在效率问题。

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
mysql> 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);
Query OK, 3 rows affected

mysql> select * from student;
+----+------+
| ID | NAME |
7 t6 C" r( J& j" O& F, X2 ?+----+------+
+ A* o7 y7 n% j, N) T; y* p+ ?) h& `| 11 | aa |
1 m9 J# i& r% w, e' U, y& o6 N| 13 | bb |
3 a) y0 Q0 v0 S| 16 | cc |
7 z/ E4 \0 }% B+ T+----+------+
. g% Q+ d' @. S% Z3 rows in set

---

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

mysql> delete from student where id not in (select min(id) from student group by name);
; f( C7 P( ~8 d6 B" h) v  S执行报错：1093 - You can't specify target table 'student' for update in FROM clause
& C6 @  D7 ~9 v原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
怎么规避这个问题？
再加一层封装，如下：
+ p4 w3 J8 V9 m' Fmysql> 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;
+----+------+
| ID | NAME |
6 y$ g1 ]- x! X; W+----+------+
( I3 @3 e3 P& l: Z" f+ ?$ y| 11 | aa |
| 13 | bb |
| 16 | cc |
+----+------+
$ i  \' G- @( }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);

6 [1 _6 l1 N" Z; @
; s" Y1 w4 R, Z( B  k% q