代码之家 › 专栏 › 技术社区 › NeilInglis

在创建之前确定MySQL表索引是否存在

indexing stored-procedures mysql

NeilInglis · 技术社区 · 16 年前

我需要创建这些表和索引的能力,如果它们还不存在的话。在创建索引时,使用IF NOT EXISTS来处理表,但是不存在这样的语法。

我试着编写一个存储过程,如下所示,但可能失败了,因为您无法从show语句中进行选择。

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 $$

有没有另一种方法来检索表的索引,以便在创建索引之前检查索引是否已经存在,或者有人能提出更好的方法来管理这个索引?

编辑:请注意,这是一个自动程序,没有人为干预。

7 回复 | 直到 16 年前

Mo. 13 年前

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

NeilInglis 16 年前

我又把头撞到墙上,使劲地在谷歌上搜索了几下,终于找到了答案 information_schema.statistics 桌子。它包含表的索引名称。

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 $$

一切正常。

谢谢你的建议。

Piskvor left the building Rohit Kumar 16 年前

SHOW INDEX FROM mytable FROM mydb; 并检查索引是否存在—每个返回的行表示索引的一部分;您可能最感兴趣的列是 Key_name Documentation here .

Mchl 16 年前

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

Hirogen 8 年前

因为文本和blob需要一个大小,所以我将其添加到存储过程中。

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 ;

Mikko Wilkman 16 年前

您只需创建另一个具有正确索引的表,从旧表中复制所有内容,然后将其删除,并将新表重命名为旧表的名称即可。有点黑客和可能有点沉重的大表,但仍然相当直接。

j0k gauthamp 13 年前

这不是一个新版本,而是一个更完整的解决方案,包括一个创建2个索引的调用。

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 ;