Determining if MySQL table index exists before creating

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

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.

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?

EDIT: Please note that this is an automated procedure, n开发者_如何学Pythono 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

After some more banging my head off the wall and intense googling I found the information_schema.statistics table. This contains the index_name for a table.

My stored procedure is now

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(*) AS index_exists FROM information_schema.statistics WHERE TABLE_SCHEMA = DATABASE() and table_name =
theTable AND index_name = theIndexName)  = 0) THEN
   SET @s = CONCAT('CREATE INDEX ' , theIndexName , ' ON ' , theTable, '(', theIndexColumns, ')');
   PREPARE stmt FROM @s;
   EXECUTE stmt;
 END IF;
END $$

and works as expected.

Thanks for the suggestions.

Use SHOW INDEX FROM mytable FROM mydb; and check if the index is present - each of the returned rows represents one part of an index; the column that would probably interest you most is Key_name, as it contains the name of the index. Documentation here.

You can query infomration_schema database for this and many more useful information

http://dev.mysql.com/doc/refman/5.0/en/information-schema.html

Since Text and Blobs need a size, I added it to the stored procedure.

DROP PROCEDURE IF EXISTS createIndex;

DELIMITER $$
-- Create a temporary stored procedure for checking if Indexes exist before attempting to re-create them. => can be called
$$
CREATE PROCEDURE `createIndex`(
    IN `tableName` VARCHAR(128),
    IN `indexName` VARCHAR(128),
    IN `indexColumns` VARCHAR(128),
    IN `indexSize` VARCHAR(128)
)
BEGIN
  IF((SELECT COUNT(*) AS index_exists FROM information_schema.statistics WHERE TABLE_SCHEMA = DATABASE() AND table_name = tableName AND index_name = indexName)  = 0) THEN
        IF(indexSize > 0) THEN
            SET @sqlCommand = CONCAT('CREATE INDEX ' , indexName , ' ON ' , tableName, '(', indexColumns, '(' , indexSize, '))');
        ELSE
            SET @sqlCommand = CONCAT('CREATE INDEX ' , indexName , ' ON ' , tableName, '(', indexColumns, ')');
        END IF;
    PREPARE _preparedStatement FROM @sqlCommand;
    EXECUTE _preparedStatement;
  END IF;
END $$
DELIMITER ;

You could just create another table with the correct indices, copy everything from the old table and then drop it and rename the new table back to what the old one used to be. A bit hackish and might be a bit heavy for big tables but still fairly straightforward.

Not a new version but a more complete solution that includes a call to create 2 indexes.

USE MyDatabaseName;
DELIMITER $$
-- Create a temporary stored procedure for checking if Indexes exist before attempting to re-create them.
DROP PROCEDURE IF EXISTS `MyDatabaseName`.`spCreateIndex` $$
CREATE PROCEDURE `MyDatabaseName`.`spCreateIndex` (tableName VARCHAR(128), in indexName VARCHAR(128), in indexColumns VARCHAR(128))
BEGIN
  IF((SELECT COUNT(*) AS index_exists FROM information_schema.statistics WHERE TABLE_SCHEMA = DATABASE() AND table_name = tableName AND index_name = indexName)  = 0) THEN
    SET @sqlCommand = CONCAT('CREATE INDEX ' , indexName , ' ON ' , tableName, '(', indexColumns, ')');
    PREPARE _preparedStatement FROM @sqlCommand;
    EXECUTE _preparedStatement;
  END IF;
END $$
DELIMITER ;

-- Create the Indexes if they do not exist already.
CALL spCreateIndex('MyCustomers', 'idxCustNum', 'CustomerNumber');
CALL spCreateIndex('MyProducts', 'idxProductName', 'ProductName');

DELIMITER $$
-- Drop the temporary stored procedure.
DROP PROCEDURE IF EXISTS `MyDatabaseName`.`spCreateIndex` $$
DELIMITER ;