mysql删除重复数据_MySQL

mysql删除重复数据

删除表内重复数据的一种解决方案：

Create Table: CREATE TABLE `tt` (

`id` int(11) DEFAULT NULL,

`name` varchar(30) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

200万的数据

100万 id从1到100w name是zz

100条 id从1到100 name是zz

100万 id从1到100w name是mike

100条 id从1到100 name是mike

存储过程：

新增sizes+1条数据，name为params

CREATE PROCEDURE pro_insert2(in sizes int,in params varchar(30))

begin

declare i int;

start transaction;

set i = 0;

while i<=sizes do

insert into tt values(i,params);

set i=i+1;

end while;

commit;

end

mysql命令行操作：插入测试数据

call pro_insert2(1000000,'zz');

call pro_insert2(100,'zz');

call pro_insert2(1000000,'mike');

call pro_insert2(100,'mike');

mysql> select count(*) from tt;

+----------+

| count(*) |

+----------+

| 2000204 |

+----------+

1 row in set (0.00 sec)

Create Table: CREATE TABLE `tt2` (

`id` int(11) DEFAULT NULL,

`name` varchar(30) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

mysql> insert into tt2 (select distinct * from tt);

Query OK, 2000002 rows affected (51.55 sec)

Records: 2000002 Duplicates: 0 Warnings: 0

mysql> drop table tt;

Query OK, 0 rows affected (0.10 sec)

mysql> alter table tt2 rename tt;

Query OK, 0 rows affected (0.09 sec)

mysql> select count(*) from tt;

+----------+

| count(*) |

+----------+

| 2000002 |

+----------+

1 row in set (2.54 sec)

到此OK，这种方法效率不是很高，期待更有效率的方法.