Reclaim the space of a database table

1. Create a stored procedure

2. Get all the table names in the database first

3. Reclaim space using optimize table

4. Execute the stored procedure (call Recycle_interspace)

DELIMITER $$
CREATE PROCEDURE ' Recycle_interspace ' ()
BEGIN
DECLARE row_table_name VARCHAR (90);
DECLARE ergodic INT DEFAULT 1;
DECLARE getcategory CURSOR for SELECT table_name from information_schema.tables WHERE table_schema= ' database name ';
DECLARE CONTINUE HANDLER for not FOUND SET ergodic:=0;
OPEN getcategory;

/**
The space to begin reclaiming the table.
Use optimize table to reclaim space, but this operation will lock the table to affect the business, if the recovery is confirmed, it is recommended to operate during the business low peak period.
*/
REPEAT
FETCH getcategory into Row_table_name;
SET @csql =concat (' OPTIMIZE TABLE ', Row_table_name, '; ');
PREPARE create_stmt from @csql;
EXECUTE create_stmt;
UNTIL ergodic=0 END REPEAT;
CLOSE getcategory;
end$$

DELIMITER;

Reclaim the space of a database table