mysql数据库重复索引_查看mysql数据库表上的重复索引列

BEGIN

DECLARE tableNameXX VARCHAR(100);

DECLARE indexNameXX VARCHAR(100);

DECLARE sqll VARCHAR(1000);

DECLARE countIndexXX INT;

DECLARE done int;

DECLARE needUpdateC CURSOR FOR SELECT table_name,INDEX_NAME,COUNT(1) ct

FROM information_schema.`STATISTICS`

WHERE seq_in_index=1

AND table_schema='test' AND INDEX_NAME!='PRIMARY'

GROUP BY table_name,column_name

HAVING ct >=2;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

OPEN needUpdateC;

cursor_loop:LOOP

FETCH needUpdateC into tableNameXX,indexNameXX,countIndexXX;

SET sqll=CONCAT_ws(' ','drop index ',indexNameXX ,' on ',tableNameXX);

IF done=1 THEN

LEAVE cursor_loop;

END IF;

SET @sql=sqll;

PREPARE s1 FROM @sql;

EXECUTE s1;

DEALLOCATE PREPARE s1;

END LOOP cursor_loop;

CLOSE needUpdateC;

END

分享到：

2014-04-18 09:56

浏览 937

评论