1.like And in Batch delete specified records
like grammar
like Mainly used for fuzzy query
sql = "delete from A where field like '%ABC%'" This can be used for characters and numbers
in grammar
sql = "delete from A where field id(1,2,3)" This can only be used with numbers
Association deletion
delete B from B
where exists (select 1 from A where B.accountid=A.accountid);
If the above two methods are deleted 100W The level of database is estimated to be no problem , If it is 1000W Probably not
Suppose you have a table (syslogs) have 1000 10000 records , You need to delete it without stopping the business statusid=1 All records of , Almost 600 Ten thousand , Direct execution
DELETE FROM syslogs WHERE statusid=1
You will find the deletion failed , because lock wait timeout exceed Error for .
Because this statement involves too many records , So we passed LIMIT Parameter batch deletion , For example, every 10000 Delete once , So we can use MySQL Such statements
The code is as follows
DELETE FROM syslogs WHERE status=1 ORDER BY statusid LIMIT 10000;
These records can then be successfully deleted after multiple executions .
Another Delete direction .
The above comparison indicates that the amount to be deleted is not too large , Delete the PID Generate to another temporary table .
The code is as follows
mysql DBname -e "select a.pid from table1 a ,table2 b where
a.pid=b.pid">del_pid.txt;
sed -i '1d' del_pid.txt
awk '{print "delete from table1 where pid=",$1,";"}' del_pid.txt >del_pid.sql
mysql DBname
This way SQL Split into several SQL The execution speed should not be too slow .
Technology