CODEWITHSUNDEEP
mysql
Rayuth You
Rayuth You

Reputation: 181

How to make DROP INDEX IF EXISTS for mysql?

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?

Upvotes: 18

Views: 47997

Answers (2)

Man
Man

Reputation: 2817

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

Upvotes: 12

Prakash S
Prakash S

Reputation: 95

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

Upvotes: 7

Related Questions