mysql如何建立索引workbench_我如何使用“如果存在”在MySQL中创建或删除索引？...

我如何使用“如果存在”在MySQL中创建或删除索引？

我想知道是否有办法在创建索引或在MySQL上销毁索引之前检查索引是否存在。 几年前似乎有功能请求，但是我找不到解决方案的任何文档。 这需要在使用MDB2的PHP应用程序中完成。

Paul asked 2020-01-06T00:38:43Z

9个解决方案

46 votes

这是我的4班轮：

set @exist := (select count(*) from information_schema.statistics where table_name = 'table' and index_name = 'index' and table_schema = database());

set @sqlstmt := if( @exist > 0, 'select ''INFO: Index already exists.''', 'create index i_index on tablename ( columnname )');

PREPARE stmt FROM @sqlstmt;

EXECUTE stmt;

Nikhil S answered 2020-01-06T00:38:53Z

25 votes

IF EXISTS或CREATE INDEX尚未构建。 但是您可以在创建/删除索引之前手动检查是否存在。

使用此语句检查索引是否已存在。

SHOW INDEX FROM table_name WHERE KEY_NAME = 'index_name'

如果查询返回零(0)，则索引不存在，则可以创建它。

如果查询返回正数，则该索引存在，然后可以将其删除。

Pablo Venturino answered 2020-01-06T00:39:26Z

13 votes

这是一个DROP INDEX IF EXISTS过程：

DELIMITER $$

DROP PROCEDURE IF EXISTS drop_index_if_exists $$

CREATE PROCEDURE drop_index_if_exists(in theTable varchar(128), in theIndexName 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('DROP INDEX ' , theIndexName , ' ON ' , theTable);

PREPARE stmt FROM @s;

EXECUTE stmt;

END IF;

END $$

DELIMITER ;

这段代码是根据以下过程创建的：确定创建前是否存在MySQL表索引

Datageek answered 2020-01-06T00:39:51Z

3 votes

我调整了在这里找到的答案，以及在哪里提出了以下用于删除和创建索引的步骤。 请注意，如果需要，AddTableIndex存储过程可以删除索引。 他们还接受对我的使用至关重要的架构名称。

DELIMITER //

DROP PROCEDURE IF EXISTS migrate.DropTableIndex //

CREATE PROCEDURE migrate.DropTableIndex

(

in schemaName varchar(128) -- If null use name of current schema;

, in tableName varchar(128) -- If null an exception will be thrown.

, in indexName varchar(128) -- If null an exception will be thrown.

)

BEGIN

SET schemaName = coalesce(schemaName, schema());

IF((SELECT COUNT(*) AS index_exists FROM information_schema.statistics WHERE TABLE_SCHEMA = schemaName and table_name = tableName AND index_name = indexName) > 0) THEN

SET @s = CONCAT('DROP INDEX `' , indexName , '` ON `' , schemaName, '`.`', tableName, '`');

PREPARE stmt FROM @s;

EXECUTE stmt;

END IF;

END //

DROP PROCEDURE IF EXISTS migrate.AddTableIndex//

CREATE PROCEDURE migrate.AddTableIndex

(

IN schemaName varchar(128) -- If null use name of current schema;

, IN tableName varchar(128) -- If null an exception will be thrown.

, IN indexName varchar(128) -- If null an exception will be thrown.

, IN indexDefinition varchar(1024) -- E.g. '(expireTS_ ASC)'

, IN ifPresent ENUM('leaveUnchanged', 'dropAndReplace') -- null=leaveUnchanged.

, OUT outcome tinyint(1) -- 0=unchanged, 1=replaced, 4=added.

)

BEGIN

DECLARE doDrop tinyint(1) DEFAULT NULL;

DECLARE doAdd tinyint(1) DEFAULT NULL;

DECLARE tmpSql varchar(4096) DEFAULT '';

SET schemaName = coalesce(schemaName, schema());

SET ifPresent = coalesce(ifPresent, 'leaveUnchanged');

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE table_schema = schemaName AND table_name = tableName AND index_name = indexName) THEN

IF (ifPresent = 'leaveUnchanged') THEN

SET doDrop = 0;

SET doAdd = 0;

SET outcome = 0;

ELSEIF (ifPresent = 'dropAndReplace')

THEN

SET doDrop = 1;

SET doAdd = 1;

SET outcome = 1;

END IF;

ELSE

SET doDrop = 0;

SET doAdd = 1;

SET outcome = 4;

END IF;

IF (doDrop = 1) THEN

SET tmpSql = concat( 'alter table `', schemaName, '`.`', tableName, '` drop index `', indexName, '` ');

SET @sql = tmpSql;

PREPARE tmp_stmt FROM @sql;

EXECUTE tmp_stmt;

DEALLOCATE PREPARE tmp_stmt;

END IF;

IF (doAdd = 1) THEN

SET tmpSql = concat( 'alter table `', schemaName, '`.`', tableName, '` add index `', indexName, '` (', indexDefinition, ')');

SET @sql = tmpSql;

PREPARE tmp_stmt FROM @sql;

EXECUTE tmp_stmt;

DEALLOCATE PREPARE tmp_stmt;

END IF;

END;

//

DELIMITER ;

Thomas Paine answered 2020-01-06T00:40:11Z

1 votes

我在MySQL中使用SELECT IF()语句有一些相似之处。

select if (

exists(

select distinct index_name from information_schema.statistics

where table_schema = 'schema_db_name'

and table_name = 'tab_name' and index_name like 'index_1'

)

,'select ''index index_1 exists'' _______;'

,'create index index_1 on tab_name(column_name_names)') into @a;

PREPARE stmt1 FROM @a;

EXECUTE stmt1;

DEALLOCATE PREPARE stmt1;

使用if()语句的优点是不需要编写任何过程。

Mithun B answered 2020-01-06T00:40:35Z

1 votes

我认为这将有助于您删除现有索引。

DELIMITER //

CREATE PROCEDURE dropIndexing

()

BEGIN

IF EXISTS(

SELECT * FROM information_schema.statistics

WHERE TABLE_SCHEMA = DATABASE()

AND `table_name`='mytable'

AND `index_name` = 'myindex'

)

THEN

ALTER TABLE `mytable` DROP INDEX `myindex`;

END IF;

END //

DELIMITER ;

CALL dropIndexing();

DROP PROCEDURE dropIndexing;

jeewiya answered 2020-01-06T00:40:55Z

0 votes

我对此处介绍的某些解决方案有疑问。 这是我想出的：

DELIMITER $$

DROP PROCEDURE IF EXISTS myschema.create_index_if_not_exists $$

CREATE PROCEDURE myschema.create_index_if_not_exists(in p_tableName VARCHAR(128), in p_indexName VARCHAR(128), in p_columnName VARCHAR(128) )

BEGIN

PREPARE stmt FROM 'SELECT @indexCount := COUNT(1) from information_schema.statistics WHERE `table_name` = ? AND `index_name` = ?';

SET @table_name = p_tableName;

SET @index_name = p_indexName;

EXECUTE stmt USING @table_name, @index_name;

DEALLOCATE PREPARE stmt;

-- select @indexCount;

IF( @indexCount = 0 ) THEN

SELECT 'Creating index';

SET @createIndexStmt = CONCAT('CREATE INDEX ', p_indexName, ' ON ', p_tableName, ' ( ', p_columnName ,')');

PREPARE stmt FROM @createIndexStmt;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

END IF;

END $$

DELIMITER ;

如下使用它：

call myschema.create_index_if_not_exists('MyTable','end_time_index','end_time');

这已在MAC OS X 10.8.2和MySQL 5.5.24以及Windows 7和MySQL 5.5.21上进行了测试。

Wim Deblauwe answered 2020-01-06T00:41:24Z

0 votes

MySQL Workbench版本6.3(MySql fork MariaDb)

DROP INDEX IF EXISTS FK_customer__client_school__school_id ON dbname.tablename;

SushiGuy answered 2020-01-06T00:41:44Z

-1 votes

这是DROP INDEX IF EXISTS的变通办法，在v10.1.4之前的MySQL和MariaDB版本中缺少该变通办法。您还可以将其用于所有其他所需的语句，这取决于INDEX(例如SELECT "info: index exists.")的存在，如以下示例所示 )。

-- DROP INDEX IF EXISTS

SELECT

COUNT(*)

INTO

@INDEX_my_index_ON_TABLE_my_table_EXISTS

FROM

`information_schema`.`statistics`

WHERE

`table_schema` = 'my_database'

AND `index_name` = 'my_index'

AND `table_name` = 'my_table'

;

SET @statement := IF(

@INDEX_my_index_ON_TABLE_my_table_EXISTS > 0,

-- 'SELECT "info: index exists."',

'DROP INDEX `my_index` ON `my_table`',

'SELECT "info: index does not exist."'

);

PREPARE statement FROM @statement;

EXECUTE statement;

automatix answered 2020-01-06T00:42:04Z