MySQL Delete tens of millions of Data Operations

In MySQL, when the delete operation drops tens of millions of data records, the table may be locked, and the MySQL server may be under great pressure. This is an unavoidable problem in MySQL. It can be said that it is insufficient in dealing with large data volumes. This Delete requirement cannot be avoided in the business. Therefore, we use the idea of Oracle to write stored procedures, divide and conquer them, and delete them in batches.

Delimiter $

Create procedure sp_del_test ()

Begin

Declare done int default 0;

Declare uid_1 int default 0;

Declare I int default 0;

Declare cur_test cursor for select uid from tmp_test_del;

Declare continue handler for sqlstate '000000' set done = 1;

Open cur_test;

Repeat fetch cur_test into uid_1;

Set autocommit = 0;

Delete from test_fenye where uid = uid_1;

Set I = I + 1;

If Mod (I, 1000) = 0 then

Commit;

End if;

Until done = 1 end repeat;

Close cur_test;

Commit;

End;

$