I want to DROP INDEX in mysql with option IF EXISTS but I have found nothing that make it works.
DROP INDEX IF EXISTS index_name ON table_name;
Anyone has any hint?
-
2similar question : stackoverflow.com/questions/2480148/…1000111– 10001112016-10-04 09:42:05 +00:00Commented Oct 4, 2016 at 9:42
-
On recent mysql versions this works. I just tried it myself and dropped the index.MP-programming– MP-programming2023-01-11 12:02:25 +00:00Commented Jan 11, 2023 at 12:02
Add a comment
|
2 Answers
I do not see any straight-forward way to DROP INDEX using IF EXISTS.
As a workaround, I wrote the following Procedure, which works for me.
CREATE PROCEDURE `DropIndexIfExists`(
IN i_table_name VARCHAR(128),
IN i_index_name VARCHAR(128)
)
BEGIN
SET @tableName = i_table_name;
SET @indexName = i_index_name;
SET @indexExists = 0;
SELECT
1
INTO @indexExists FROM
INFORMATION_SCHEMA.STATISTICS
WHERE
TABLE_NAME = @tableName
AND INDEX_NAME = @indexName;
SET @query = CONCAT(
'DROP INDEX ', @indexName, ' ON ', @tableName
);
IF @indexExists THEN
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
END
2 Comments
Drew
you could put your
SET @query... inside the IF. Nitpick, but why would it run otherwise ? :p
asimov
update if multiple databases :
... SELECT 1 INTO @indexExists FROM INFORMATION_SCHEMA.STATISTICS WHERE INDEX_SCHEMA = database() AND TABLE_NAME = @tableName AND INDEX_NAME = @indexName LIMIT 1; ... Try this,
create procedure DeleteIndex()
begin
IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_NAME = 'TableName'
AND INDEX_NAME = 'IndexName' AND INDEX_SCHEMA='DbName') THEN
ALTER TABLE TableName DROP index THead2;
END IF;
END
