如果表中不存在索引，则添加索引

Question

我想使用ALTER语法为表添加索引，但首先要检查它是否已经存在于表中，如果不存在才添加索引。

 ALTER TABLE tableName ADD INDEX IX_Table_XYZ (column1);

有没有办法做到这一点？

- Manish Kumar

Answer 1

尝试像这样：

set @x := (select count(*) from information_schema.statistics where table_name = 'table' and index_name = 'IX_Table_XYZ' and table_schema = database());
set @sql := if( @x > 0, 'select ''Index exists.''', 'Alter Table TableName ADD Index IX_Table_XYZ (column1);');
PREPARE stmt FROM @sql;
EXECUTE stmt;

- Rahul Tripathi

---

这是最佳答案！ - Mad Dog Tannen

@RahulTripathi 需要 DEALLOCATE stmt 吗？这是什么情况下适用？ - Mad Dog Tannen

@KayNelson：不需要使用DEALLOCATE。如果我们使用像游标这样的东西，它才会被使用。您可以参考此链接：http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-prepared-statements.html，其中写道：“准备好的语句是特定于创建它的会话的。如果您在没有取消分配先前准备好的语句的情况下终止会话，则服务器将取消分配该语句。” - Rahul Tripathi

很高兴也能帮到你，@KayNelson：- - Rahul Tripathi

这相当于 IF NOT EXISTS，但这是一个非常糟糕的想法。最终你可能会在生产环境中得到一个旧的/过时的索引定义。 - The Impaler

Answer 2

您可以使用以下语法（按索引名称）检查索引是否存在。

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

然后您可以在存储过程中运行它，例如：

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;

- Mad Dog Tannen

---

谢谢，但是能否不使用存储过程来完成呢？ - Manish Kumar

是的，只需在 BEGIN ... END 语句中执行它。我总是更喜欢使用存储过程，因为这样您可以在生产环境中更改逻辑而无需关闭软件。 - Mad Dog Tannen

@KayNelson 我该如何使用字段来完成这个任务？ - Smith

Answer 3

根据@KayNelson的回答，在MySQL 5.7.16中，正确的方法是在IF条件中使用IS NULL而不是!=1。

因此，一段用于有条件地向表添加INDEX的代码片段如下：

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

    ALTER TABLE `yourtable` ADD INDEX `yourindex` (`column1` ASC);

END IF;

- martoncsukas

Answer 4

另一种可能性是检查是否有任何行返回SHOW INDEX FROM yourTableName WHERE Key_name = 'yourTableIndexName'。如果结果集不为空，则索引已经存在。

- nhcodes