Мне было интересно, есть ли способ проверить, существует ли индекс перед его созданием или уничтожить его в MySQL. Похоже, что несколько лет назад был запрос функции, но я не могу найти документацию для решения. Это нужно сделать в приложении PHP с использованием MDB2.
Как я могу использовать "if exists" для создания или удаления индекса в MySQL?
Ответ 1
Вот мой 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;
Ответ 2
IF EXISTS
модификатор еще не создан для DROP INDEX
или CREATE INDEX
. Но вы можете проверить вручную наличие, прежде чем создавать/отбрасывать индекс.
Используйте это предложение, чтобы проверить, существует ли этот индекс.
SHOW INDEX FROM table_name WHERE KEY_NAME = 'index_name'
- Если запрос возвращает ноль (0), то индекс не существует, то вы можете его создать.
- Если запрос возвращает положительное число, тогда индекс существует, и вы можете его удалить.
Ответ 3
Вот процедура 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 до создания
Ответ 4
Я проверил ответы, найденные здесь, и где можно найти следующие sprocs для удаления и создания индексов. Обратите внимание, что 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 ;
Ответ 5
Он не встроен в
Однако
http://old.nabble.com/how-to-%22drop-index-if-exists%22-td14024229.html
объясняет, как написать хранимую процедуру для реализации DROP INDEX IF EXISTS, и довольно легко изменить этот код для работы с CREATE INDEX IF EXISTS
Ответ 6
У меня есть что-то подобное с использованием инструкции SELECT IF() в MySQL.
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() заключается в том, что он не нуждается в каких-либо процедурах, которые нужно записать.
Ответ 7
MySQL Workbench версия 6.3 (MySql fork MariaDb)
drop index if exists FK_customer__client_school__school_id on dbname.tablename;
Ответ 8
У меня возникли проблемы с некоторыми из представленных здесь решений. Вот что я придумал:
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
Ответ 9
Я думаю, что это поможет вам удалить существующий индекс.
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;