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

admin

方法1：
1、创建一个临时表，选取需要的数据。
9 r# g- B# |# P5 C4 s/ [2、清空原表。
3、临时表数据导入到原表。
4、删除临时表。
mysql> select * from student;
2 u- ]: Q' l8 y+ I* N+----+------+
| ID | NAME |
. H2 h: O' d$ L) S+----+------+
| 11 | aa |
| 12 | aa |
| 13 | bb |
7 X; m2 a" \, m$ W  J4 A& m3 V5 C! n| 14 | bb |
8 q3 i  N! S! d- g| 15 | bb |
| 16 | cc |
+----+------+
' h  ]8 D6 }1 q6 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;
Query OK, 3 rows affected
1 I; K' j( b5 Y+ Q. ~& M3 a  wRecords: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
+----+------+
  z' c% A, s4 O7 Q0 || ID | NAME |
+----+------+
3 G' \6 N' p! ~1 i8 z4 R/ M| 11 | aa |
# B5 }% ?& U+ l+ U| 13 | bb |
2 M" _$ M$ v: }7 R! c! y8 D| 16 | cc |
+----+------+
3 rows in set

mysql> drop temporary table temp;
# B, x- E: U: p" H1 l$ wQuery OK, 0 rows affected
5 X' `/ N# N: J这个方法，显然存在效率问题。

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
mysql> create temporary table temp as select min(id) as MINID from student group by name;
* K" |/ V8 {* ]1 ?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;
+----+------+
3 y; h9 P9 ~. h| ID | NAME |
+----+------+
9 n3 a7 _: ?# g3 v, B| 11 | aa |
| 13 | bb |
| 16 | cc |
, G. ?4 p. ]/ D$ Z- I6 F% L9 m+----+------+
. P$ i( a3 h/ _! I4 e3 {( V4 e* T3 rows in set

---

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

mysql> delete from student where id not in (select min(id) from student group by name);
/ S" g5 G$ F% @执行报错：1093 - You can't specify target table 'student' for update in FROM clause
原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
& R, t( u6 ^2 H怎么规避这个问题？
/ K% O& ]7 K: P$ ^, K- x% B再加一层封装，如下：
mysql> 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 |
+----+------+
! F5 T. Q8 h* \: J, @0 S* V7 R| 11 | aa |
" s. H! i# f2 R! s! \% D| 13 | bb |
% K2 M+ y$ P0 C* V6 h| 16 | cc |
+----+------+
3 rows in set

! W* I2 b1 `* n  ~5 ^, L6 \方法三例如：delete from bugdata where id not in (select minid from (select min(id) as minid from bugdata group by qxbh) b);


0 k" C. w5 |, M/ q3 j" \$ i2 i: t5 F