mysql Batch delete data sql sentence _mysql Batch delete data sql Statement explanation


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
©2020-2025 ioDraw All rights reserved, Privacy Policy