'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;

'database' 카테고리의 다른 글

if exist drop index and create index in MYSQL  (0) 2019.05.18

+ Recent posts