MySQL按条件复制数据_按条件迁移数据[MySQL]

# 1、friend 和 friend_profile 表新增字段flag=0

# 2、分批准备好所有要清理的记录的id_list

SELECT a.`uid` INTO OUTFILE '/tmp/id_list.txt'

LINES TERMINATED BY ','

FROM `friend` as a, `friend_profile` as b

WHERE a.`uid`=b.`uid`

AND a.`login_num`<1

AND b.`reg_ip`='0.0.0.0'

AND a.`uid`>0

AND a.`uid`<300

# 3、逐步置 friend 和 friend_profile 表中垃圾记录flag=1

UPDATE `friend` SET `flag`=1 WHERE `uid` IN (【一批id_list】)

UPDATE `friend_profile` SET `flag`=1 WHERE `uid` IN (【一批id_list】)

# 4、逐步把 friend 和 friend_profile 表垃圾数据写入 friend_rubbish 和 friend_profile_rubbish 表

INSERT INTO `friend_rubbish`

(

SELECT * FROM `friend`

WHERE `flag`=1 AND `uid`>0 AND `uid`<300

)

INSERT INTO `friend_profile_rubbish`

(

SELECT * FROM `friend_profile`

WHERE `flag`=1 AND `uid`>0 AND `uid`<300

)

# 5、逐步删除 friend 和 friend_profile 表中flag=1的垃圾记录

DELETE FROM `friend` WHERE `flag`=1 AND `uid`>0 AND `uid`<300

DELETE FROM `friend_profile` WHERE `flag`=1 AND `uid`>0 AND `uid`<300

# 6、删除friend 和 friend_profile 表中flag字段