Mysql delete tens of millions of Data Operations

Mysql deletes tens of millions of data in mysql. When the delete operation drops tens of millions of data records, the table will be locked and even put a lot of pressure on the mysql server. 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; $