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

admin

方法1：
1、创建一个临时表，选取需要的数据。
; d, i. w; _; N' y5 k8 N( b2、清空原表。
3、临时表数据导入到原表。
4、删除临时表。
6 M, w5 ~/ }- k$ Q" q7 L7 R, Hmysql> select * from student;
8 R" |- c" q% c! R8 R8 i  S+----+------+
| ID | NAME |
& c2 G) |2 Y" V7 N+----+------+
| 11 | aa |
& X: q+ ~* _' A; e. n| 12 | aa |
| 13 | bb |
1 A% g. {: F& l" r5 \; r8 O| 14 | bb |
+ m4 `* x( y& x3 k9 h1 r| 15 | bb |
1 X8 d4 Z: M( O. q| 16 | cc |
( E- I6 q4 \6 W$ Y( W7 o( Q+----+------+
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;
5 y) ^4 }  S0 ?3 C1 sQuery OK, 3 rows affected
2 ~- O, k! w; Z& J# }# iRecords: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
+----+------+
| ID | NAME |
+----+------+
| 11 | aa |
7 v' R6 E7 J6 a* ~/ P. b; j% K| 13 | bb |
| 16 | cc |
/ A) D6 }; O& ^0 ]. i+----+------+
3 rows in set

mysql> drop temporary table temp;
0 @/ d9 y' h  z' j) Y( `2 W1 P! KQuery OK, 0 rows affected
这个方法，显然存在效率问题。

---

方法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);
: A0 ?+ k" k* }Query OK, 3 rows affected

mysql> select * from student;
6 F  I4 d4 _( K+----+------+
8 `' _/ o+ V" A: Q| ID | NAME |
+----+------+
# J/ P, ?& }4 L1 w& k| 11 | aa |
. a6 y' I5 N$ L! {+ a| 13 | bb |
| 16 | cc |
+----+------+
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
原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
# X: O" C! }8 q8 f2 H: `9 l- }" A8 H4 M怎么规避这个问题？
8 e4 ]) f0 ~: ?" Z" q再加一层封装，如下：
1 F& Q8 q+ v' |( `$ K7 Jmysql> 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;
+----+------+
' O( b' ?7 f! x| ID | NAME |
+----+------+
| 11 | aa |
1 _: |" T# A' T| 13 | bb |
) O4 U) ^! [0 S! n$ J- W| 16 | cc |
+----+------+
; f' h. B( |5 ]8 _9 R+ I# {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);
8 ^- J5 @( \; ^) F
2 U2 y- `8 I1 f" D