php 如何使用“如果存在”在 MySQL 中创建或删除索引？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2480148/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I employ "if exists" for creating or dropping an index in MySQL?
提问by Paul
I was wondering if there's a way to check if an index exists before creating it or destroying it on MySQL. It appears that there was a feature request for this a few years back, but I can't find any documentation for a solution. This needs to be done in a PHP app using MDB2.
我想知道是否有办法在 MySQL 上创建或销毁索引之前检查索引是否存在。几年前似乎有一个功能请求，但我找不到任何解决方案的文档。这需要在使用 MDB2 的 PHP 应用程序中完成。
回答by Nikhil S
Here is my 4 liner:
这是我的 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;
回答by Pablo Venturino
IF EXISTSmodifier is not built for DROP INDEXor CREATE INDEXyet. But you can check manually for the existence before creating/dropping an index.
IF EXISTS修改器不是为DROP INDEX或CREATE INDEX尚未构建的。但是您可以在创建/删除索引之前手动检查是否存在。
Use this sentence to check whether the index already exists.
用这句话检查索引是否已经存在。
SHOW INDEX FROM table_name WHERE KEY_NAME = 'index_name'
- If the query returns zero (0) then the index does not exists, then you can create it.
- If the query returns a positive number, then the index exists, then you can drop it.
- 如果查询返回零 (0) 则索引不存在，那么您可以创建它。
- 如果查询返回一个正数，那么索引存在，那么你可以删除它。
回答by Datageek
Here is a DROP INDEX IF EXISTS procedure:
这是一个 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 ;
This code was created based on the procedure from here: Determining if MySQL table index exists before creating
此代码是根据此处的过程创建的：在创建之前确定 MySQL 表索引是否存在
回答by Thomas Paine
I tweaked answers found here and else where to come up with the following sprocs for dropping & creating indexes. Note that the AddTableIndex sproc can drop the index if need be. They also accept a schema name which was critical for my uses.
我调整了在此处找到的答案，以及在何处提出以下用于删除和创建索引的 sproc。请注意，如果需要，AddTableIndex sproc 可以删除索引。他们还接受对我的使用至关重要的模式名称。
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 ;
回答by Mithun B
I have something similar with using SELECT IF() statement in MySQL.
我在 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;
The advantage of using if() statement is that, it doesn't need a stored procedures.
使用 if() 语句的优点是，它不需要存储过程。
回答by jeewiya
I think this will helpful to you drop your existing index.
我认为这将有助于您删除现有索引。
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;
回答by SushiGuy
MySQL Workbench version 6.3 (MySql fork MariaDb)
MySQL Workbench 6.3 版（MySql fork MariaDb）
DROP INDEX IF EXISTS FK_customer__client_school__school_id ON dbname.tablename;
回答by Wim Deblauwe
I was having problems with some of the solutions presented here. This is what I came up with:
我遇到了这里介绍的一些解决方案的问题。这就是我想出的：
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 ;
Use it as follows:
使用方法如下：
call myschema.create_index_if_not_exists('MyTable','end_time_index','end_time');
This was tested on MAC OS X 10.8.2 with MySQL 5.5.24 and on Windows 7 with MySQL 5.5.21
这是在 MAC OS X 10.8.2 和 MySQL 5.5.24 和 Windows 7 和 MySQL 5.5.21 上测试的
回答by automatix
Here is a workaround for the DROP INDEX IF EXISTS, that is missing in MySQL and MariaDB versions before v10.1.4. You can also use it for every other statement you want, that should be depend on the existence of an INDEX(e.g. for SELECT "info: index exists."like in the example below).
这是DROP INDEX IF EXISTS之前的 MySQL 和 MariaDB 版本中缺少的v10.1.4. 您也可以将它用于您想要的所有其他语句，这应该取决于 an 的存在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;
