Shulou(Shulou.com)06/01 Report--

This article mainly introduces the method of batch deletion of mysql data and defragmentation of table. The content of the article is carefully selected and edited by the author. It has a certain pertinence and is of great significance to everyone's reference. Let's learn about the method of batch deletion of mysql data and defragmentation of table with the author.

DROP PROCEDURE IF EXISTS prc_del_loop

CREATE PROCEDURE prc_del_loop ()

BEGIN

DECLARE v_count INT

DECLARE v_data_free INT

My_del_loop:LOOP

Delete from t_data where adress = 'Suzhou Log Analysis' limit 10000

Select count (1) into v_count from (

Select * from t_data where adress = 'Suzhou log analysis' limit 1) d

IF v_count=0 THEN

LEAVE my_del_loop

END IF

END LOOP my_del_loop

SELECT "TABLE DATA HAD DELETED!!"

# defragment, data_free greater than or equal to 100m for defragmentation

Select round (sum (data_free/1024/1024), 0) into v_data_free

From information_schema.tables where TABLE_SCHEMA= 'data' and table_name =' t'

IF v_data_free > = 100 THEN

SELECT "TABLE SHRINK IS runing games!"

Alter table t ENGINE=INNODB

SELECT "TABLE SHRINK IS FINISHEDROWTH!"

ELSE

SELECT "TABLE SHRINK IS NOT NEED shrimp diagnosis!"

END IF

END

Use cursors to delete:

Drop PROCEDURE if EXISTS prc_del_loop_cur

CREATE PROCEDURE prc_del_loop_cur ()

BEGIN

DECLARE v_name VARCHAR (2000)

DECLARE v_id BIGINT

DECLARE done INT DEFAULT 0

DECLARE cur1 CURSOR FOR SELECT id,name from test where adress = 'Suzhou log analysis'

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1

Open cur1

My_del_loop:LOOP

Fetch cur1 into v_id,v_name

IF done=1 THEN

LEAVE my_del_loop

END IF

Delete from test where id = v_id

END LOOP my_del_loop

CLOSE cur1

SELECT "delete over!!"

END

Call prc_del_loop_cur

After reading the above about mysql data batch deletion and table defragmentation methods, many readers must have some understanding, if you need to get more industry knowledge and information, you can continue to follow our industry information column.

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.