在创建之前确定MySQL表索引是否存在 [英] Determining if MySQL table index exists before creating

问题描述

我们系统的自动数据库迁移过程涉及运行包含新表定义及其附带索引的.sql脚本。

Our system's automated database migration process involves running .sql scripts containing new table definitions and their accompanying indexes.

我只需要能够创建这些表和索引他们还不存在。通过使用IF NOT EXISTS来处理表，但在创建索引时不存在这样的语法。

I require the ability to create these tables and indexes only if they don't already exist. Tables are taken care of by using IF NOT EXISTS but no such syntax exists when creating indexes.

我试图写一个存储过程，如下所示，但是这个失败了大概是因为你不能从一个show语句中选择。

I've tried to write a stored procedure, shown below, but this fails presumably as you can't select from a show statement.

DELIMITER $$
DROP PROCEDURE IF EXISTS csi_add_index $$
CREATE PROCEDURE csi_add_index(in theTable varchar(128), in theIndexName varchar(128), in theIndexColumns varchar(128)  )
BEGIN
 IF(((SELECT COUNT(*) FROM (SHOW KEYS FROM theTable WHERE key_name = theIndexName)) tableInfo = 0) THEN
   SET @s = CONCAT('CREATE INDEX ' , theIndexName , ' ON ' , theTable, '(', theIndexColumns, ')');
   PREPARE stmt FROM @s;
   EXECUTE stmt;
 END IF;
END $$

我考虑过删除和重新创建，但该过程，因为它存在，假设它不会遇到任何错误，因此我想先检查是否存在。

I've considered dropping and recreating but the process, as it exists, assumes that it'll encounter no errors hence me wanting to check for existence first.

还有另一种方法来检索在创建之前检查索引是否已存在的表的索引，还是有人可以建议更好的方法来管理它？

Is there another way to retrieve the indexes of a table to check if an index already exists before creating or can anyone suggest a better approach to managing this?

编辑：请注意这是一个自动程序，没有人间发明。

Please note that this is an automated procedure, no human intervention.

推荐答案

SELECT INDEX_NAME FROM INFORMATION_SCHEMA.STATISTICS WHERE
`TABLE_CATALOG` = 'def' AND `TABLE_SCHEMA` = DATABASE() AND
`TABLE_NAME` = theTable AND `INDEX_NAME` = theIndexName

这篇关于在创建之前确定MySQL表索引是否存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！