批量删除某个表上的索引(自定义函数)
它不是自带的,而是一个自定义函数,所以需要自己设置一下批量删除索引
DELIMITER $$
CREATE PROCEDURE `proc_drop_index`(dbname VARCHAR(200),tablename VARCHAR(200))
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE ct INT DEFAULT 0;
DECLARE _index VARCHAR(200) DEFAULT '';
DECLARE _cur CURSOR FOR SELECT index_name FROM information_schema.STATISTICS WHERE table_schema=dbname AND table_name=tablename AND seq_in_index=1 AND index_name <>'PRIMARY' ;
DECLARE CONTINUE HANDLER FOR NOT FOUND set done=2 ;
OPEN _cur;
FETCH _cur INTO _index;
WHILE _index<>'' DO
SET @str = CONCAT("drop index ",_index," on ",tablename );
PREPARE sql_str FROM @str ;
EXECUTE sql_str;
DEALLOCATE PREPARE sql_str;
SET _index='';
FETCH _cur INTO _index;
END WHILE;
CLOSE _cur;
END$$
自定义函数函数命令:
call proc_drop_index('数据库名','表名')
其他命令:
创建索引
ALTER TABLE `表名` ADD INDEX 索引名 ( `列`);
显示所有索引
SHOW INDEX FROM 表名;
删除主键索引
ALTER TABLE 表名 drop PRIMARY KEY;
删除单值、唯一、复合索引
DROP INDEX indexName on mytable;