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

admin

方法1：
2 u" w" |. M7 q) R" ~8 a5 u5 h1、创建一个临时表，选取需要的数据。
2、清空原表。
4 ?3 @; W7 M) j1 G: v3、临时表数据导入到原表。
, m3 |) F2 M: ]/ g# d% R4、删除临时表。
8 i7 J. B8 }& I3 smysql> select * from student;
9 {3 o4 K5 @6 a+ |" _+----+------+
| ID | NAME |
+----+------+
2 \& d# K) q, ^1 ~| 11 | aa |
/ m. [& x! d; ]& ], S& c4 L/ {| 12 | aa |
+ T7 k$ b1 i( F+ V| 13 | bb |
; H. v1 V5 J) ~; e| 14 | bb |
| 15 | bb |
| 16 | cc |
( r) {9 R. I7 G2 Q9 k" e: c+----+------+
9 u- m6 J2 A" C  ]$ C/ b6 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;
. ?, h7 _& j" x9 TQuery OK, 3 rows affected
" p& _! m' p  z! R; v$ F5 `Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
/ g; a, e6 ]& P7 W) ^* R* V3 A! T+----+------+
2 k! H5 j; Z: A! m0 N  _0 P4 t0 k  L, n| ID | NAME |
' a! ?: W1 j* C" K$ g* _+----+------+
| 11 | aa |
| 13 | bb |
0 x9 d- I8 B7 Y4 H| 16 | cc |
1 J+ M! q: X" n. m5 l/ H1 K+----+------+
! Q+ D% C% |$ l7 T3 rows in set

mysql> drop temporary table temp;
Query 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;
( X$ N2 s7 b9 u/ D+ L& u% a/ v$ KQuery OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> delete from student where id not in (select minid from temp);
% C& O9 r" D; }4 h$ Q* m, y" iQuery OK, 3 rows affected

mysql> select * from student;
+----+------+
8 I- D5 Q+ q$ B) c  u% x* ?9 ~| ID | NAME |
4 j5 h" y/ X  _4 \+ J+----+------+
| 11 | aa |
| 13 | bb |
| 16 | cc |
+----+------+
5 [# A( a, y) s4 N3 j3 rows in set

---

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

mysql> delete from student where id not in (select min(id) from student group by name);
! ^% b/ C# @4 l执行报错：1093 - You can't specify target table 'student' for update in FROM clause
/ m/ a: [3 A  D6 C/ H原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
8 m$ I; v; g* D怎么规避这个问题？
+ ~  ^. C/ n0 e$ D; m5 O% R' V再加一层封装，如下：
2 E$ d6 g: S7 J) x% umysql> delete from student where id not in (select minid from (select min(id) as minid from student group by name) b);
5 i9 Z5 W) r. M/ C* d1 DQuery OK, 3 rows affected

mysql> select * from student;
* \4 Q( ]; {6 ?( |* A+----+------+
| ID | NAME |
7 {% T- Y) `# Z, a+----+------+
6 b! e+ T7 {# z" R/ C, v| 11 | aa |
& z" M$ U5 G8 Q9 Z" @0 i| 13 | bb |
| 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);
6 E) u- q5 J7 a9 `: m
$ H2 f  L5 A- t1 \$ S1 @
) t: B' Y) H- k) L& V' H