按條件遷移資料[MySQL]

功能需求：

1、把 friend 和 friend_profile 表中的垃圾（符合一定條件）資料轉移到 friend_rubbish 和 friend_profile_rubbish 表中

2、條件有可能較為複雜

 

非功能需求：

1、線上操作

2、千萬級資料量

 

聲明：俺不是DBA，只會寫點簡單的SQL……-_-! 高手、大牛、神人、耶穌、釋迦摩尼……能指點就指點指點偶吧~~~~~~

 

寫在前面的話：

1、“逐步”和“分批”都是“按uid分批”的意思（為啥要分批？因為非功能需求-.-），SQL中uid的範圍是測試隨便寫的

2、“a.`login_num`<1 AND`reg_ip`='0.0.0.0'”也是隨便寫的條件（成為垃圾記錄的具體條件）

3、friend_rubbish 和 friend_profile_rubbish 是根據 friend 和 friend_profile 的表結構建立來的 

4、中間產生id_list.txt而沒有純SQL，是為了效能考慮，代價是需要人來手工參與（或寫成工具指令碼也可）

 

【方案一】

 

# 1、按條件逐步把 friend 表資料寫入 friend_rubbish 表

INSERT INTO `friend_rubbish` 

(

    SELECT a.* 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_profile 表資料寫入 friend_profile_rubbish 表（直接參照 friend_rubbish 表中記錄）

INSERT INTO `friend_profile_rubbish` 

(

    SELECT * FROM `friend_profile` 

    WHERE `uid` IN

    (

        SELECT `uid` FROM `friend_rubbish` 

        WHERE `uid`>0 AND `uid`<300 

    )

)

 

 

# 4、分批準備好所有要刪除記錄的id_list

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

LINES TERMINATED BY ',' 

FROM `friend_rubbish` 

WHERE `uid`>0 AND `uid`<300

 

# 5、逐步清理 friend 和 friend_profile 表 

DELETE FROM `friend` WHERE `uid` IN (【一批id_list】)

DELETE FROM `friend_profile` WHERE `uid` IN (【一批id_list】)

 

 

貌似有些複雜。。。又想了個方案二。。。

【方案二】

 

# 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欄位

 

等等！貌似可以最佳化，flag欄位好像很多餘的說。。。

【方案三】

 

# 1、分批準備好所有要清理的記錄的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 

 

# 2、分批把 friend 和 friend_profile 表垃圾資料寫入 friend_rubbish 和 friend_profile_rubbish 表

INSERT INTO `friend_rubbish` 

(

    SELECT * FROM `friend` WHERE `uid` IN (【一批id_list】) 

)

 

INSERT INTO `friend_profile_rubbish` 

(

    SELECT * FROM `friend_profile` WHERE `uid` IN (【一批id_list】) 

)

 

# 3、分批刪除 friend 和 friend_profile 表中的垃圾記錄

DELETE FROM `friend` WHERE `uid` IN (【一批id_list】) 

DELETE FROM `friend_profile` WHERE `uid` IN (【一批id_list】) 

 

厄……還是這個簡單明了~