MySQL에서 인덱스를 생성하거나 삭제하기 위해 "존재하는 경우"를 어떻게 사용할 수 있습니까?

MySQL에서 인덱스를 생성하거나 삭제하기 위해 "존재하는 경우"를 어떻게 사용할 수 있습니까?

---

인덱스를 생성하거나 MySQL에서 삭제하기 전에 인덱스가 있는지 확인하는 방법이 있는지 궁금합니다. 몇 년 전부터이 기능에 대한 요청이 있었던 것으로 보이지만 솔루션에 대한 문서를 찾을 수 없습니다. 이 작업은 MDB2를 사용하는 PHP 앱에서 수행해야합니다.

---

다음은 내 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;

---

IF EXISTS수정을 위해 만들어진되지 DROP INDEX또는 CREATE INDEX아직. 그러나 인덱스를 생성 / 삭제하기 전에 수동으로 존재 여부를 확인할 수 있습니다.

이 문장을 사용하여 색인이 이미 존재하는지 확인하십시오.

SHOW INDEX FROM table_name WHERE KEY_NAME = 'index_name'

• 쿼리가 0을 반환하면 인덱스가 존재하지 않는 것이므로 만들 수 있습니다.
• 쿼리가 양수를 반환하면 인덱스가 존재하는 것이므로 삭제할 수 있습니다.

---

다음은 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 테이블 인덱스가 존재하는지 확인

---

여기에서 찾은 답변과 인덱스 삭제 및 생성을 위해 다음 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 ;

---

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 () 문을 사용할 때의 장점은 어떤 프로시 저도 작성할 필요가 없다는 것입니다.

---

기존 색인을 삭제하는 데 도움이 될 것 같습니다.

        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;

---

여기에 제시된 솔루션 중 일부에 문제가있었습니다. 이것이 내가 생각 해낸 것입니다.

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');

이것은 MySQL 5.5.24가 설치된 MAC OS X 10.8.2 및 MySQL 5.5.21이 설치된 Windows 7에서 테스트되었습니다.

---

MySQL Workbench 버전 6.3 (MySql fork MariaDb)

DROP INDEX IF EXISTS FK_customer__client_school__school_id ON dbname.tablename;

---

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

ReferenceURL : https://stackoverflow.com/questions/2480148/how-can-i-employ-if-exists-for-creating-or-dropping-an-index-in-mysql