모든 테이블 복구 하기. (all tables should be repaired in mysql)

모든 테이블을 리페어 하는 Stored procedure 를 작성할 필요가 있어서, 적어 봅니다.


alll tables should be repaired using open cursor in mysql.


DELIMITER $$
USE `getty`$$
DROP PROCEDURE IF EXISTS `test_repair_all`$$
CREATE DEFINER=`database name`@`%` PROCEDURE `test_repair_all`()
BEGIN
DECLARE pTableName VARCHAR(64);
DECLARE noMoreRows BOOLEAN DEFAULT FALSE;

DECLARE curTableList CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE 1=1
AND TABLE_SCHEMA = 'database name'
LIMIT 1;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET noMoreRows = TRUE;

OPEN curTableList; curTableListLoop: LOOP
IF ( noMoreRows ) THEN
CLOSE curTableList;
LEAVE curTableListLoop;
END IF;

FETCH curTableList INTO pTableName;

SET @s = CONCAT('repair table database name.', pTableName);
PREPARE stmt FROM @s;
EXECUTE stmt;

END LOOP curTableListLoop;

END$$

DELIMITER ;