MySQL: Create index If not exists

滿奇怪的，為何 Mysql 不提供 IF NOT EXISTS。

MySQL does not support the obvious format:

CREATE INDEX IF NOT EXISTS index_name ON table(column)
ERROR 1064 (42000): You have an error in your SQL syntax;...

---

 

解法：

You can check if the index (by name of the index) exists by using this syntax

SELECT 1        
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='yourtable' AND
INDEX_NAME='yourindex';

Then you could run it in a Stored Procedure like

IF (SELECT 1        
    FROM INFORMATION_SCHEMA.STATISTICS
    WHERE TABLE_SCHEMA = 'yourschema' AND TABLE_NAME='yourtable' AND
    INDEX_NAME='yourindex') != 1 THEN

Alter Table TableName ADD Index IX_Table_XYZ (column1);

END IF;

---

 

手動測試，查詢 index:

SELECT 1 IndexIsThere FROM INFORMATION_SCHEMA.STATISTICS WHERE table_schema=DATABASE() AND table_name='EF' AND index_name='EF_status';

 

建立index:

create index EF_status on EF(enrollStatus);

 

刪除 index:

drop index EF_status on EF;

 

挑戰合併成一個句字，手動測試下面語法會出錯：

if not exists(SELECT 1 IndexIsThere FROM INFORMATION_SCHEMA.STATISTICS WHERE table_schema=DATABASE() AND table_name='EF' AND index_name='EF_status') create index EF_status on EF(enrollStatus);