mysql rowid踢重_rowid去重（删除表的重复记录）

-- 构造测试环境

SQL> create table andy(id int,name varchar2(10));

Table created.

SQL>

insert into andy values(1,'a');

insert into andy values(2,'b');

insert into andy values(3,'c');

insert into andy values(4,'d');

SQL> select * from andy;

ID NAME

---------- ----------

1 a

2 b

3 c

4 d

4 rows selected.

SQL>

insert into andy values(4,'f');

insert into andy values(4,'d');

SQL> select * from andy;

ID NAME

---------- ----------

1 a

2 b

3 c

4 d

4 f

4 d

6 rows selected.

-- 依次group by 表所有字段，通过min(rowid)查看所有唯一记录(去重记录，也就是相同多行数据只显示一行)

SQL> select id,name,min(rowid)

from andy

group by id,name;

ID NAME MIN(ROWID)

---------- ---------- ------------------

3 c AAAfKTAAEAAACr/AAC

4 d AAAfKTAAEAAACr/AAD

4 f AAAfKTAAEAAACr/AAJ

1 a AAAfKTAAEAAACr/AAA

2 b AAAfKTAAEAAACr/AAB

-- delete 重复数据时,group by 表的个别字段，发现误删除

SQL> delete from andy

where rowid not in (

select min(rowid)

from andy

group by id);

2 rows deleted.

说明：记录 4 f 被误删。

SQL> select * from andy;

ID NAME

---------- ----------

1 a

2 b

3 c

4 d

-- 构造与上面测试相同环境，即插入刚删除的数据

SQL>

insert into andy values(4,'f');

insert into andy values(4,'d');

SQL> select * from andy;

ID NAME

---------- ----------

1 a

2 b

3 c

4 d

4 f

4 d

6 rows selected.

-- 依次group by 表所有字段，通过min(rowid)查看所有唯一记录(去重记录，也就是相同多行数据只显示一行)

SQL> select id,name,min(rowid)

from andy

group by id,name;

ID NAME MIN(ROWID)

---------- ---------- ------------------

3 c AAAfKTAAEAAACr/AAC

4 d AAAfKTAAEAAACr/AAD

4 f AAAfKTAAEAAACr/AAL

1 a AAAfKTAAEAAACr/AAA

2 b AAAfKTAAEAAACr/AAB

-- delete 重复数据时，group by 表的所有字段，发现没有误删。

SQL> delete from andy

where rowid not in (

select min(rowid)

from andy

group by id,name);

1 row deleted.

-- 检查去重后的数据，发现没有误删

SQL> select * from andy;

ID NAME

---------- ----------

1 a

2 b

3 c

4 d

4 f

说明：

如果想通过rowid去重，那么在 delete 重复数据时，需要group by 表的所有字段。如果只group by 表的个别字段，那么会造成误删除。