How to make DROP INDEX IF EXISTS for mysql?

Question

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?

similar question : http://stackoverflow.com/questions/2480148/how-can-i-employ-if-exists-for-creating-or-dropping-an-index-in-mysql — 1000111, Oct 04 '16 at 09:42
On recent mysql versions this works. I just tried it myself and dropped the index. — MP-programming, Jan 11 '23 at 12:02

score 11 · Answer 1 · edited Feb 28 '20 at 07:39

11

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

edited Feb 28 '20 at 07:39

Sergey Romanov

2,949
4
23
38

answered Oct 04 '16 at 11:39

Man

2,720
2
13
21

2

you could put your `SET @query...` inside the `IF`. Nitpick, but why would it run otherwise ? :p – Drew Oct 04 '16 at 21:06
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; ... ` – asimov May 15 '23 at 09:26

score 6 · Answer 2 · answered Oct 05 '16 at 08:26

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

score -3 · Answer 3 · answered Mar 25 '23 at 11:35

-3

This is how you can drop index if exists in Laravel:

Schema::table("table_name", function (Blueprint $table) {
    $indexes = DB::connection()->getDoctrineSchemaManager()->listTableIndexes("table_name");
    foreach ($indexes as $key => $val) {
        if ($key == "index_name") {
            $table->dropIndex("index_name");
        }
    }
});

answered Mar 25 '23 at 11:35

Ilyich

4,966
3
39
27

OP has no tag for laravel / php but only MySql. This answer would unlikely help the OP. – killstreet Aug 24 '23 at 08:11

How to make DROP INDEX IF EXISTS for mysql?

3 Answers3