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

admin

方法1：
1、创建一个临时表，选取需要的数据。
2、清空原表。
3、临时表数据导入到原表。
4、删除临时表。
mysql> select * from student;
* _" v/ D5 Q- q+----+------+
' P2 _7 {- u0 G% E* ^| ID | NAME |
4 d7 c4 W8 _& B5 b. X+----+------+
$ `# N8 s; {8 b: D0 s! f| 11 | aa |
| 12 | aa |
| 13 | bb |
7 t" n' c$ x* l| 14 | bb |
9 T- n) K' f. @: C* ?* P6 Q| 15 | bb |
| 16 | cc |
$ X- ^) r2 ~$ E2 E. w& K/ m1 Y+----+------+
% Q: u; \  ~% l8 l6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
3 [) T8 Q* w. O( x2 c, 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;
Query OK, 3 rows affected
2 _4 T( _  \' l# V, d  P9 dRecords: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
+----+------+
+ Q% b; H+ T! ]0 K9 @6 {9 l| ID | NAME |
+----+------+
| 11 | aa |
| 13 | bb |
| 16 | cc |
1 C, S7 H3 i. h4 m3 H% t+----+------+
2 p9 s* S& o& u! h- V# t3 rows in set

mysql> drop temporary table temp;
Query OK, 0 rows affected
$ B2 r" y+ T/ B, |) H这个方法，显然存在效率问题。

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
" \. `4 V/ K# e, L& Omysql> create temporary table temp as select min(id) as MINID from student group by name;
Query OK, 3 rows affected
4 }! E1 F$ _/ ^0 m" ERecords: 3 Duplicates: 0 Warnings: 0

mysql> delete from student where id not in (select minid from temp);
; |8 L. P) i3 s  `, Y, P$ zQuery OK, 3 rows affected

mysql> select * from student;
+----+------+
| ID | NAME |
+----+------+
- A! p7 p5 t1 || 11 | aa |
# `  l$ S6 E& F' U, o) {| 13 | bb |
| 16 | cc |
+----+------+
" R5 E9 n+ w# Q3 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
5 b' l3 F6 Q+ |5 m: j; }  e原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
5 j0 V: o) z! C怎么规避这个问题？
再加一层封装，如下：
0 x/ ?. H- {. H6 R1 E9 z- A7 |" qmysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);
) h: a# }! m! S* ]1 h$ R* @* OQuery OK, 3 rows affected

mysql> select * from student;
9 A5 X: g- a) X) a: v$ Z+----+------+
| ID | NAME |
+----+------+
& O/ l- H+ }4 \8 o( || 11 | aa |
| 13 | bb |
| 16 | cc |
+----+------+
) P0 j. ^8 e, W0 f8 T" l3 rows in set

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


  |* \) |+ I# F" `" ^6 E# x2 Y