Ik vroeg me af of er een manier is om te controleren of een index bestaat voordat deze wordt gemaakt of vernietigd op MySQL. Het lijkt erop dat hier een paar jaar geleden een functieverzoek voor was, maar ik kan geen documentatie voor een oplossing vinden. Dit moet worden gedaan in een PHP-app die MDB2 gebruikt.
Antwoord 1, autoriteit 100%
Hier is mijn 4-voering:
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;
Antwoord 2, autoriteit 48%
IF EXISTSmodifier is nog niet gebouwd voor DROP INDEXof CREATE INDEX. Maar u kunt handmatig controleren op het bestaan voordat u een index maakt/verwijdert.
Gebruik deze zin om te controleren of de index al bestaat.
SHOW INDEX FROM table_name WHERE KEY_NAME = 'index_name'
- Als de zoekopdracht nul (0) retourneert, bestaat de index niet, dan kunt u deze maken.
- Als de zoekopdracht een positief getal retourneert, bestaat de index en kunt u deze verwijderen.
Antwoord 3, autoriteit 28%
Hier is een DROP INDEX IF BESTAAT procedure:
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 ;
Deze code is gemaakt op basis van de procedure vanaf hier: Bepalen of MySQL-tabelindex bestaat vóór het maken
Antwoord 4, autoriteit 5%
Ik heb de antwoorden die ik hier vond aangepast en anders waar ik de volgende sprocs kon bedenken voor het laten vallen van & indexen maken. Merk op dat de AddTableIndex sproc de index kan laten vallen indien nodig. Ze accepteren ook een schemanaam die cruciaal was voor mijn gebruik.
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 ;
Antwoord 5, autoriteit 3%
Ik heb iets soortgelijks met het gebruik van de SELECT IF()-instructie in 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;
Het voordeel van het gebruik van de instructie if() is dat er geen opgeslagen procedures voor nodig zijn.
Antwoord 6, autoriteit 2%
Ik denk dat dit je zal helpen om je bestaande index te verwijderen.
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;
Antwoord 7, autoriteit 2%
MySQL Workbench versie 6.3 (MySql fork MariaDb)
DROP INDEX IF EXISTS FK_customer__client_school__school_id ON dbname.tablename;
Antwoord 8
Ik had problemen met sommige van de hier gepresenteerde oplossingen. Dit is wat ik bedacht:
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 ;
Gebruik het als volgt:
call myschema.create_index_if_not_exists('MyTable','end_time_index','end_time');
Dit is getest op MAC OS X 10.8.2 met MySQL 5.5.24 en op Windows 7 met MySQL 5.5.21