DROP PROCEDURE IF EXISTS BIGDATA_ADD_INDEX; DELIMITER $$ CREATE PROCEDURE BIGDATA_ADD_INDEX() BEGIN
DECLARE command VARCHAR(200);
DECLARE founded INT DEFAULT 1;
DECLARE cur_sleest CURSOR FOR SELECT CONCAT("ALTER TABLE ", TABLE_NAME, " ADD INDEX idx_name(cloumn);") AS SQL_COMMAND FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA="schema_name" AND TABLE_TYPE="BASE TABLE";
DECLARE CONTINUE HANDLER FOR NOT FOUND SET founded = 0;
OPEN cur_sleest;
sleet_loop : LOOP FETCH cur_sleest INTO command; IF founded = 0 THEN LEAVE sleet_loop; END IF; SET @sql := command; PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; END LOOP sleet_loop;
CLOSE cur_sleest; END$$ DELIMITER ;
命令行mysql 库名 --skip-column_names -e "SELECT CONCAT('ALTER TABLE ', TABLE_NAME, ' ADD INDEX idx_name(cloumn);') AS SQL_COMMAND FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_TYPE='BASE TABLE'" | mysql 库名
DELIMITER $$
CREATE PROCEDURE BIGDATA_ADD_INDEX()
BEGIN
DECLARE command VARCHAR(200);
DECLARE founded INT DEFAULT 1;
DECLARE cur_sleest CURSOR FOR
SELECT CONCAT("ALTER TABLE ", TABLE_NAME, " ADD INDEX idx_name(cloumn);") AS SQL_COMMAND
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA="schema_name"
AND TABLE_TYPE="BASE TABLE";
DECLARE CONTINUE HANDLER FOR NOT FOUND SET founded = 0;
OPEN cur_sleest;
sleet_loop : LOOP
FETCH cur_sleest INTO command;
IF founded = 0 THEN LEAVE sleet_loop; END IF;
SET @sql := command;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END LOOP sleet_loop;
CLOSE cur_sleest;
END$$
DELIMITER ;