if exist drop index and create index in MYSQL

'if exist drop index' dose not exist in MYSQL.

So we using user variables.

tableName is table name.
indexName is index name.

columnName is column name.

 

set @exist := (select count(*) from information_schema.statistics where table_name = 'tableName' and index_name = 'indexName');
set @sqlstmt := if( @exist > 0, 

 'drop index indexName on tableName',

 'create unique index indexName on tableName ( columnName )'

);
PREPARE stmt FROM @sqlstmt;
EXECUTE stmt;
set @sqlstmt := if( @sqlstmt = 'drop index indexName on tableName', 

 'create unique index catalogItemId on CatalogItemCatalogItemImageModelMeta ( catalogItemId )', 

 'select success'

);
PREPARE stmt FROM @sqlstmt;
EXECUTE stmt;