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

admin

方法1：
: b9 }! O: P7 v8 D1、创建一个临时表，选取需要的数据。
2、清空原表。
3、临时表数据导入到原表。
4、删除临时表。
$ u, A! M! t4 c9 W: Q* \mysql> select * from student;
+----+------+
| ID | NAME |
+----+------+
| 11 | aa |
, a6 `) U& e* N5 y* i| 12 | aa |
| 13 | bb |
| 14 | bb |
| 15 | bb |
| 16 | cc |
& ^$ e) T7 d, n6 D6 i4 x1 Q+----+------+
, T+ `& Q6 y9 e8 e2 N- R9 x* u6 rows in set

mysql> create temporary table temp as select min(id),name from student group by name;
  K+ }7 r; N( x6 Z" l* v# n! k6 dQuery OK, 3 rows affected
: W  }3 J9 u/ `; cRecords: 3 Duplicates: 0 Warnings: 0

mysql> truncate table student;
Query OK, 0 rows affected

mysql> insert into student select * from temp;
) F. u: h" X/ @' W. d0 xQuery OK, 3 rows affected
) e: c( a1 E# b: ~Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from student;
0 D$ J: L! J  e/ _! P1 i+----+------+
| ID | NAME |
+----+------+
+ d* s, a" C9 y6 T' l( R' e0 R- @| 11 | aa |
# \  q8 n9 _- \| 13 | bb |
| 16 | cc |
/ U  U# n2 j4 n+----+------+
3 rows in set

mysql> drop temporary table temp;
& }; q* D/ A; b; v: gQuery OK, 0 rows affected
这个方法，显然存在效率问题。

---

方法2：按name分组，把最小的id保存到临时表，删除id不在最小id集合的记录，如下：
$ u3 i- o' T1 K+ hmysql> create temporary table temp as select min(id) as MINID from student group by name;
Query OK, 3 rows affected
9 G2 f  t# W& t" v: r  b- SRecords: 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;
* @7 b3 D; _' e1 W! ?) o( T. W; ~+----+------+
8 D0 {; h7 i- Q| ID | NAME |
/ M3 ]( J% J: r+----+------+
| 11 | aa |
| 13 | bb |
| 16 | cc |
+----+------+
# B% M% N0 P  p; ^( f3 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
, X1 ]; D9 R; z0 o: e原因是：更新数据时使用了查询，而查询的数据又做了更新的条件，mysql不支持这种方式。
6 w, B0 I3 f) U怎么规避这个问题？
再加一层封装，如下：
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 |
+----+------+
3 `* |6 b$ w' y& Q| 11 | aa |
| 13 | bb |
3 {- O2 n0 K/ Z/ b| 16 | cc |
3 \/ x* R/ D+ v* y5 J+----+------+
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);


- S/ O# ~$ _) y1 |- j