MySQL存储过程之删除某表全部索引

# 如果存储过程存在则删除
DROP PROCEDURE IF EXISTS `drop_all_indices`;

DELIMITER $$
# 创建存储过程
CREATE DEFINER=`root`@`%` PROCEDURE `drop_all_indices`(IN t_name VARCHAR(255))
BEGIN
	# 声明结束标识
	DECLARE end_flag BOOLEAN DEFAULT 0;
	DECLARE idx_name VARCHAR(255);
	# 声明游标 idx_cursor 
	DECLARE idx_cursor CURSOR FOR SELECT INDEX_NAME FROM INFORMATION_SCHEMA.STATISTICS i WHERE TABLE_SCHEMA = 'xmirror_oss' AND TABLE_NAME = t_name AND INDEX_NAME <> 'PRIMARY' GROUP BY INDEX_NAME;
	# 设置终止标志
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET end_flag=1;
	# 打开游标
	OPEN idx_cursor;
	# 遍历游标
	REPEAT
		FETCH idx_cursor INTO idx_name;
		IF NOT end_flag THEN
			SET @STMT :=CONCAT("ALTER TABLE ",t_name," DROP index " ,idx_name,";");
			#SELECT @STMT;
			PREPARE STMT FROM @STMT;
			EXECUTE STMT;
		END IF;
	# 根据 end_flag 判断是否结束
	UNTIL end_flag END REPEAT;
	# 关闭游标
	CLOSE idx_cursor;
END$$
DELIMITER ;


# 调用存储过程
CALL drop_all_indices('table_name');