开始因为执行alter table bb add COLUMN expireTime datetime null;时间特别慢,网上有说是去掉default null,但是去掉之后还是慢(数据有41万条数据,后来经查时那台机器问题),没办法,只能先做备份,再导数据,以下方法:
select count(*) from bb;
create table bb_bak as select * from bb;
select count(*) from bb_bak;
TRUNCATE table bb;
alter table bb add COLUMN expireTime datetime null;
insert into bb(id,version,repeatCount,channel_id,FLEXICACHE_ID) select b.id,b.version,b.repeatCount,b.channel_id,b.FLEXICACHE_ID from bb_bak b limit 0,100000;
insert into bb(id,version,repeatCount,channel_id,FLEXICACHE_ID) select b.id,b.version,b.repeatCount,b.channel_id,b.FLEXICACHE_ID from bb_bak b limit 100000,200000;
insert IGNORE into bb(id,version,repeatCount,channel_id,FLEXICACHE_ID) select b.id,b.version,b.repeatCount,b.channel_id,b.FLEXICACHE_ID from bb_bak b limit 200000,250000;
select count(*) from bb;
TRUNCATE table bb_bak;
DROP table bb_bak;
注意上的limit是从0游标开始取100000个,不是到100000,ignore可以忽略重复key的异常,第三个insert会有重复值,这里用了ignore,则不会有问题了,select为了确保数据准确,truncate快。
select count(*) from bb;
create table bb_bak as select * from bb;
select count(*) from bb_bak;
TRUNCATE table bb;
alter table bb add COLUMN expireTime datetime null;
insert into bb(id,version,repeatCount,channel_id,FLEXICACHE_ID) select b.id,b.version,b.repeatCount,b.channel_id,b.FLEXICACHE_ID from bb_bak b limit 0,100000;
insert into bb(id,version,repeatCount,channel_id,FLEXICACHE_ID) select b.id,b.version,b.repeatCount,b.channel_id,b.FLEXICACHE_ID from bb_bak b limit 100000,200000;
insert IGNORE into bb(id,version,repeatCount,channel_id,FLEXICACHE_ID) select b.id,b.version,b.repeatCount,b.channel_id,b.FLEXICACHE_ID from bb_bak b limit 200000,250000;
select count(*) from bb;
TRUNCATE table bb_bak;
DROP table bb_bak;
注意上的limit是从0游标开始取100000个,不是到100000,ignore可以忽略重复key的异常,第三个insert会有重复值,这里用了ignore,则不会有问题了,select为了确保数据准确,truncate快。