用一个临时表 (内存表tmp_aaa 自增长id) 取最关键的字段 select coun(1) as total,id,a ,b from aaa group by id,a,b having count(*)>1 把这些插入到这个临时表里。之后用游标遍历整个临时表 delete aaa from aaa, tmp_aaa a where aaa.id=a.id and aaa.a=a.a and aaa.b=a.b limit total-1 用prepare smt来实现这个sql的执行。之后把所有这些做成自动执行的linxu脚本就ok了
部分参考如下 CREATE ` PROCEDURE `sp_say_hello`(p1 int) COMMENT 'This procedure generates string "Hello,world!"' BEGINPREPARE stmt1 FROM 'SELECT * from a limit ?'; SET @a = p1; EXECUTE stmt1 USING @a; DEALLOCATE PREPARE stmt1; END; 存储过程可以完全实现 PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse'; mysql> SET @a = 3; mysql> SET @b = 4; mysql> EXECUTE stmt1 USING @a, @b; +------------+ | hypotenuse | +------------+ | 5 | +------------+ mysql> DEALLOCATE PREPARE stmt1; PREPARE stmt1 FROM 'SELECT * from a limit ?'; Query OK, 0 rows affected (0.00 sec) Statement preparedmysql> SET @a = 3; Query OK, 0 rows affected (0.00 sec)mysql> EXECUTE stmt1 USING @a; +------+ | a | +------+ | 1 | | 2 | | 2 | +------+ 3 rows in set (0.00 sec)mysql> DEALLOCATE PREPARE stmt1
--1使用表替换来删除重复项 create table test_1(id int,value int); insert test_1 select 1,2 union all select 1,2 union all select 2,3; --建立一个和源表结构一样的空的临时表 create table tmp like test_1; --向临时表插入不重复的记录 insert tmp select distinct * from test_1; --删除原表 drop table test_1; --更改临时表名为目标表 rename table tmp to test_1; --显示 mysql> select * from test_1;+------+-------+| id | value |+------+-------+| 1 | 2 || 2 | 3 |+------+-------+ --2.添加auto_increment属性列(这个方法只能用于MyISAM或者BDB引擎的表) create table test_1(id int,value int) engine=MyISAM; insert test_1 select 1,2 union all select 1,2 union all select 2,3; alter table test_1 add id2 int not null auto_increment, add primary key(id,value,id2); select * from test_1;+----+-------+-----+| id | value | id2 |+----+-------+-----+| 1 | 2 | 1 || 1 | 2 | 2 || 2 | 3 | 1 |+----+-------+-----+ delete from test_1 where id2<>1; alter table test_1 drop id2; select * from test_1; +----+-------+| id | value |+----+-------+| 1 | 2 || 2 | 3 |+----+-------+本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/feixianxxx/archive/2010/08/13/5809334.aspx
这个方法有很大问题 这个表 现在4千万 select count(1) from 表 会发现等很久 create table tmp like test_1;-----------必须为内存bi表 select distinct * from test_1; 这个操作估计半天不会出来 这个表数量庞大啦
CREATE PROCEDURE `sp_Session`() beginDECLARE no_more_gbmes int;declare aid int;declare auserLogId int; declare aTime datetime; declare aminid int; declare num int;declare gbmes_csr CURSOR FOR SELECT id FROM P; DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_gbmes=1;SET no_more_gbmes=0; OPEN gbmes_csr; REPEAT fetch gbmes_csr INTO aid; IF NOT no_more_gbmes THENPREPARE stmt1 FROM 'delete from ahistory where id=? and time=? limit ?'; SET @a= aid; set @b= atime; set @c= acnt-1; EXECUTE stmt1 USING @a,@b,@c; end if until no_more_gbmes END REPEAT; 花了1个小时调试 4千万的大表 搞点东西不容易啦
取最关键的字段 select coun(1) as total,id,a ,b from aaa group by id,a,b having count(*)>1
把这些插入到这个临时表里。之后用游标遍历整个临时表
delete aaa from aaa, tmp_aaa a where aaa.id=a.id and aaa.a=a.a and aaa.b=a.b limit total-1
用prepare smt来实现这个sql的执行。之后把所有这些做成自动执行的linxu脚本就ok了
CREATE ` PROCEDURE `sp_say_hello`(p1 int)
COMMENT 'This procedure generates string "Hello,world!"'
BEGINPREPARE stmt1 FROM 'SELECT * from a limit ?';
SET @a = p1;
EXECUTE stmt1 USING @a;
DEALLOCATE PREPARE stmt1;
END;
存储过程可以完全实现
PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
mysql> SET @a = 3;
mysql> SET @b = 4;
mysql> EXECUTE stmt1 USING @a, @b;
+------------+
| hypotenuse |
+------------+
| 5 |
+------------+
mysql> DEALLOCATE PREPARE stmt1;
PREPARE stmt1 FROM 'SELECT * from a limit ?';
Query OK, 0 rows affected (0.00 sec)
Statement preparedmysql> SET @a = 3;
Query OK, 0 rows affected (0.00 sec)mysql> EXECUTE stmt1 USING @a;
+------+
| a |
+------+
| 1 |
| 2 |
| 2 |
+------+
3 rows in set (0.00 sec)mysql> DEALLOCATE PREPARE stmt1
--2.添加auto_increment属性列(这个方法只能用于MyISAM或者BDB引擎的表) create table test_1(id int,value int) engine=MyISAM; insert test_1 select 1,2 union all select 1,2 union all select 2,3; alter table test_1 add id2 int not null auto_increment, add primary key(id,value,id2); select * from test_1;+----+-------+-----+| id | value | id2 |+----+-------+-----+| 1 | 2 | 1 || 1 | 2 | 2 || 2 | 3 | 1 |+----+-------+-----+ delete from test_1 where id2<>1; alter table test_1 drop id2; select * from test_1; +----+-------+| id | value |+----+-------+| 1 | 2 || 2 | 3 |+----+-------+本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/feixianxxx/archive/2010/08/13/5809334.aspx
select count(1) from 表 会发现等很久 create table tmp like test_1;-----------必须为内存bi表
select distinct * from test_1;
这个操作估计半天不会出来 这个表数量庞大啦
CREATE PROCEDURE `sp_Session`()
beginDECLARE no_more_gbmes int;declare aid int;declare auserLogId int; declare aTime datetime; declare aminid int; declare num int;declare gbmes_csr CURSOR FOR SELECT id FROM P;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_gbmes=1;SET no_more_gbmes=0; OPEN gbmes_csr; REPEAT fetch gbmes_csr INTO aid; IF NOT no_more_gbmes THENPREPARE stmt1 FROM 'delete from ahistory where id=? and time=? limit ?';
SET @a= aid;
set @b= atime;
set @c= acnt-1;
EXECUTE stmt1 USING @a,@b,@c;
end if
until no_more_gbmes END REPEAT;
花了1个小时调试 4千万的大表 搞点东西不容易啦