Migrate data by conditions [MySQL]

Functional requirements:

1. Transfer the junk data in the tables friend and friend_profile to the tables friend_rubbish and friend_profile_rubbish.

2. The conditions may be complex.

 

Non-functional requirements:

1. online operations

2. Tens of millions of data records

 

Statement: I am not a DBA. I only write some simple SQL ...... -_-! Master, Daniel, god man, Jesus, sagamuni ...... Just give me some advice ~~~~~~

 

Preface:

1. "Step by Step" and "batch" both mean "batch by uid" (why batch? Because of non-functional requirements-.-), the UID range in SQL is tested and written randomly.

2. "A. 'login _ num' <1 and 'reg _ ip' = '0. 0.0.0 '" is also a random write condition (as a condition for junk Records)

3. friend_rubbish and friend_profile_rubbish are created based on the table structure of friend and friend_profile.

In 4、, id_list.txt is generated in the middle instead of pure SQL. It is for performance consideration, at the cost of manual participation (or writing a tool script)

 

Solution 1]

 

#1. Write Data from the friend table to the friend_rubbish table step by condition

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. Gradually write the data in the friend_profile table to the friend_profile_rubbish table (directly refer to the records in the friend_rubbish table)

Insert into 'friend _ profile_rubbish'

(

Select * From 'friend _ profile'

Where 'uid' in

(

Select 'uid' from 'friend _ rubbish'

Where 'uid'> 0 and 'uid' <300

)

)

 

 

#4. Prepare all id_list records to be deleted in batches

Select 'uid' into outfile'/tmp/id_list.txt'

Lines terminated ','

From 'friend _ rubbish'

Where 'uid'> 0 and 'uid' <300

 

#5. Gradually clear the tables friend and friend_profile

Delete from 'friend' where 'uid' in ([batch id_list ])

Delete from 'friend _ profile 'Where 'uid' in ([batch id_list ])

 

 

It seems complicated... I thought of another solution 2...

Solution 2]

 

#1. The new fields flag = 0 are added to the tables friend and friend_profile.

 

#2. Prepare id_list of all records to be cleared in batches

Select a. 'uid' into outfile'/tmp/id_list.txt'

Lines terminated ','

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. gradually set the spam records in the friend and friend_profile tables to flag = 1

Update 'friend' set 'flag' = 1 where 'uid' in ([batch id_list ])

Update 'friend _ profile 'set 'flag' = 1 where 'uid' in ([batch id_list ])

 

#4. Gradually write the junk data in the friend and friend_profile tables to the friend_rubbish and friend_profile_rubbish tables

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. Gradually delete spam records with flag = 1 in the friend and friend_profile tables

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. Delete the flags in the friend and friend_profile tables.

 

Wait! It seems that the flag can be optimized. The flag field seems like a lot of other words...

Solution 3]

 

#1. Prepare id_list of all records to be cleared in batches

Select a. 'uid' into outfile'/tmp/id_list.txt'

Lines terminated ','

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. Batch write the junk data in the friend and friend_profile tables to the friend_rubbish and friend_profile_rubbish tables.

Insert into 'friend _ rubbish'

(

Select * From 'friend' where 'uid' in ([batch id_list ])

)

 

Insert into 'friend _ profile_rubbish'

(

Select * From 'friend _ profile 'Where 'uid' in ([batch id_list ])

)

 

#3. Batch Delete junk records in the friend and friend_profile tables

Delete from 'friend' where 'uid' in ([batch id_list ])

Delete from 'friend _ profile 'Where 'uid' in ([batch id_list ])

 

Er ...... Or is this simple and clear ~