Java培训MySQL之批量删除某个表上的所有索引

批量删除某个表上的所有索引1 删除索引的存储过程

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$$

2 执行存储过程

调用：CALL proc_drop_index(“dbname”mysql删除表,”tablename”);

（编辑：威海站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!