Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to delete data in batches by MYSQL". In daily operation, I believe many people have doubts about how to delete data in batches by MYSQL. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts about "how to delete data in batches by MYSQL". Next, please follow the editor to study!

-- you can delete data by directly running the script

DELIMITER $$

DROP PROCEDURE IF EXISTS P_DBDATA_DEL $$

CREATE PROCEDURE P_DBDATA_DEL ()

BEGIN

-- declare variables

DECLARE v_sql varchar (1000)

Declare done boolean

-- declare cursor cur1

Declare cur1 cursor for

Select

Concat ('delete from', table_name,';')-Delete data

-- concat ('drop table', table_name,';')-- Delete the table

From information_schema.TABLES

Where table_schema= (SELECT DATABASE ())-- the current database instance

Declare continue handler for NOT FOUND set done = true

Open cur1

Loop1:LOOP

FETCH cur1 INTO v_sql

If done then

Leave loop1

END IF

-- execute dynamic SQL

SET @ sql = v_sql

PREPARE stmt from @ sql

EXECUTE stmt

Deallocate prepare stmt

End loop

CLOSE cur1

END

$

-- call the storage to empty the current database data

CALL p_dbdata_del ()

At this point, the study on "how to delete data in batches by MYSQL" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.