There are a large number of MyISAM log tables in the data center that need to be cleaned regularly. This is set to be done every weekend, about 10 Gb, 5-10 minutes.
The event will not be written here, And the stored procedure will be directly stored ....
[SQL] view plaincopyprint?
Drop procedure if exists proc_optable;
Tudou @ gyyx
2012-02-22
Create procedure proc_optable ()
BEGIN
DECLARE dono int default 0;
DECLARE o VARCHAR (2000 );
DECLARE optable CURSOR
FOR
Select concat ('optimize table'', TABLE_SCHEMA ,''. '', TABLE_NAME,''; ') from information_schema. 'tables' WHERE TABLE_TYPE = 'base table' and engine in ('myisam', 'innodb', 'bdb ');
Declare continue handler for not found set dono = 1;
SET dono = 0;
OPEN optable;
REPEAT
FETCH optable INTO o;
Set @ o = o;
Prepare msql from @ o;
Execute msql;
UNTIL dono end repeat;
CLOSE optable;
END
From the column of ylqmf