MYSQL千百万级数据复制

--修改表名称

RENAME TABLE `ad_pvip_collect` TO `ad_pvip_collect_old` ;

--创建新表，去掉所有的主键和索引

CREATE TABLE `ad_pvip_collect` (

`ad_channel_id` int(11) NOT NULL,

`count_pv` int(11) NOT NULL,

`count_ip` int(11) NOT NULL,

`time_collect` int(11) NOT NULL,

`time_created` int(11) NOT NULL

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--创建插入存储过程，注意MYSQL低版本不支持limit变量传入,测试环境5.5.13支持

create procedure p_ad_pvip_collect()

begin

    declare i int;

    declare b int;

    declare onetime int;

    declare numcount int;

    declare la int;

    set i=0;

    set onetime=1000;

    select count(*) into numcount from ad_pvip_collect_old;

    set la=numcount/onetime;

    while i<la do

        set b=i*onetime;

        set @sql ='insert into ad_pvip_collect select * from ad_pvip_collect_old where time_collect >1298822400';

        set @sql=concat(@sql, " limit ", b, " , ",onetime);

prepare stmt from @sql;

        execute stmt;

        set i=i+1;

    end while;

end;

--执行脚本

call p_ad_pvip_collect;

--修改主键或索引

ALTER TABLE ad_pvip_collect ADD INDEX `ad_channel_id` (`ad_channel_id`);

ALTER TABLE ad_pvip_collect ADD INDEX `time_collect` (`time_collect`);