PREPARE s1 FROM @sql;

是执行上面CONCAT("select concat 语句。所以optimizer无法执行。

可以参考下面：

###########

DROP PROCEDURE IF EXISTS `sp_optimize_tables`;

DELIMITER //

CREATE PROCEDURE `sp_optimize_tables`( IN db_name varchar(255))BEGIN
-- To optimize all the tables in exact database.
DECLARE cnt int default 0;
DECLARE i int default 0;
SELECT count(*) as total FROM information_schema.tables WHERE table_schema = db_name into cnt;
WHILE i < cnt
DO
-- Get the table's exact name.
SET @stmt = concat('SELECT table_name FROM information_schema.tables WHERE table_schema = ''',db_name,''' ORDER BY table_name ASC LIMIT ',i,',1 into @tb_name');
prepare s1 from @stmt;
execute s1;
DEALLOCATE prepare s1;
SET @stmt = '';
SET @stmt = concat('OPTIMIZE TABLE ',db_name,'.',@tb_name );
PREPARE s1 FROM @stmt;
EXECUTE s1;
DEALLOCATE prepare s1;
set @stmt = '';
set i = i + 1;
END WHILE ;
END;//
DELIMITER ;


CALL sp_optimize_tables('db8');