Determinare se la tabella di MySQL indice esiste prima della creazione

Del nostro sistema automatizzato il processo di migrazione di database prevede l’esecuzione .script sql che contiene le definizioni di tabella e i relativi indici.

Mi richiedono la capacità di creare queste tabelle e indici solo se non esiste già. I tavoli sono curati solo da tramite, SE NON ESISTE, ma non come sintassi esiste nella creazione di indici.

Ho provato a scrivere una stored procedure, illustrato di seguito, ma questo non riesce, presumibilmente, in quanto non è possibile selezionare una dichiarazione.

DELIMITER $$
DROP PROCEDURE IF EXISTS csi_add_index $$
CREATE PROCEDURE csi_add_index(in theTable varchar(128), in theIndexName varchar(128), in theIndexColumns varchar(128)  )
BEGIN
 IF(((SELECT COUNT(*) FROM (SHOW KEYS FROM theTable WHERE key_name = theIndexName)) tableInfo = 0) THEN
   SET @s = CONCAT('CREATE INDEX ' , theIndexName , ' ON ' , theTable, '(', theIndexColumns, ')');
   PREPARE stmt FROM @s;
   EXECUTE stmt;
 END IF;
END $$

Ho considerato eliminare e ricreare, ma il processo, così come esiste, si presuppone che si incontrano senza errori, quindi me voler controllare l’esistenza di prima.

C’è un altro modo per recuperare gli indici di una tabella per verificare se un indice già esistente, prima di creare o qualcuno può suggerire un approccio migliore per la gestione di questa?

EDIT: si Prega di notare che questa è una procedura automatica, senza intervento umano.

InformationsquelleAutor NeilInglis | 2010-09-02



7 Replies
  1. 14
    SELECT INDEX_NAME FROM INFORMATION_SCHEMA.STATISTICS WHERE
    `TABLE_CATALOG` = 'def' AND `TABLE_SCHEMA` = DATABASE() AND
    `TABLE_NAME` = theTable AND `INDEX_NAME` = theIndexName
    • Il ‘TABLE_SCHEMA’ = Database() po ‘ qui è importante e non sapevo su di esso. Significa che se si dispone di una tabella con lo stesso nome di un altro database non in conflitto. Avrei ottenuto falsi positivi in caso contrario.
    • Il “table_catalog è null” clausola sembra un bug.
    • Non era, al momento della scrittura — il catalogo predefinito è ora denominato “def” in corrente MySQL.
  2. 8

    Dopo alcuni sbattere la mia testa al muro e intenso googling ho trovato il information_schema.statistiche tabella. Questo contiene il index_name per una tabella.

    Mia stored procedure è ora

    DELIMITER $$
    
    DROP PROCEDURE IF EXISTS csi_add_index $$
    CREATE PROCEDURE csi_add_index(in theTable varchar(128), in theIndexName varchar(128), in theIndexColumns 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('CREATE INDEX ' , theIndexName , ' ON ' , theTable, '(', theIndexColumns, ')');
       PREPARE stmt FROM @s;
       EXECUTE stmt;
     END IF;
    END $$

    e funziona come previsto.

    Grazie per i suggerimenti.

    • Ah, qualcuno ha suggerito questo. Darò loro accettato di rispondere. Grazie a tutti.
  3. 1

    Utilizzare SHOW INDEX FROM mytable FROM mydb; e controllare se l’indice di presenza di ciascuna delle righe restituite rappresenta una parte di un indice; la colonna che sarebbe probabilmente più interessanti, è Key_name, in quanto contiene il nome dell’indice. Documentazione qui.

    • Io non riesco a utilizzare una MOSTRA INDICE in SE () istruzione nella stored procedure. Se sapessi quello che associato in termini di SELEZIONARE, quindi ho potuto usare, ma senza di quello non posso verificare i risultati. Io modificare la domanda per chiarire che questa è una procedura automatizzata, come forse non è chiaro.
  4. 1

    Dato che il Testo e Blob bisogno di una taglia, l’ho aggiunto alla stored procedure.

    DROP PROCEDURE IF EXISTS createIndex;
    
    DELIMITER $$
    -- Create a temporary stored procedure for checking if Indexes exist before attempting to re-create them. => can be called
    $$
    CREATE PROCEDURE `createIndex`(
        IN `tableName` VARCHAR(128),
        IN `indexName` VARCHAR(128),
        IN `indexColumns` VARCHAR(128),
        IN `indexSize` VARCHAR(128)
    )
    BEGIN
      IF((SELECT COUNT(*) AS index_exists FROM information_schema.statistics WHERE TABLE_SCHEMA = DATABASE() AND table_name = tableName AND index_name = indexName)  = 0) THEN
            IF(indexSize > 0) THEN
                SET @sqlCommand = CONCAT('CREATE INDEX ' , indexName , ' ON ' , tableName, '(', indexColumns, '(' , indexSize, '))');
            ELSE
                SET @sqlCommand = CONCAT('CREATE INDEX ' , indexName , ' ON ' , tableName, '(', indexColumns, ')');
            END IF;
        PREPARE _preparedStatement FROM @sqlCommand;
        EXECUTE _preparedStatement;
      END IF;
    END $$
    DELIMITER ;
  5. 0

    Si potrebbe creare un’altra tabella con la corretta indici, copiare tutto il vecchio tavolo e poi cadere e rinominare la nuova tabella per ritornare a quello vecchio usato per essere. Un po ‘hackish e potrebbe essere un po’ pesante per i grandi tavoli, ma ancora abbastanza semplice.

  6. 0

    Non nuova versione, ma una soluzione più completa che include una chiamata di creare 2 indici.

    USE MyDatabaseName;
    DELIMITER $$
    -- Create a temporary stored procedure for checking if Indexes exist before attempting to re-create them.
    DROP PROCEDURE IF EXISTS `MyDatabaseName`.`spCreateIndex` $$
    CREATE PROCEDURE `MyDatabaseName`.`spCreateIndex` (tableName VARCHAR(128), in indexName VARCHAR(128), in indexColumns VARCHAR(128))
    BEGIN
      IF((SELECT COUNT(*) AS index_exists FROM information_schema.statistics WHERE TABLE_SCHEMA = DATABASE() AND table_name = tableName AND index_name = indexName)  = 0) THEN
        SET @sqlCommand = CONCAT('CREATE INDEX ' , indexName , ' ON ' , tableName, '(', indexColumns, ')');
        PREPARE _preparedStatement FROM @sqlCommand;
        EXECUTE _preparedStatement;
      END IF;
    END $$
    DELIMITER ;
    
    -- Create the Indexes if they do not exist already.
    CALL spCreateIndex('MyCustomers', 'idxCustNum', 'CustomerNumber');
    CALL spCreateIndex('MyProducts', 'idxProductName', 'ProductName');
    
    DELIMITER $$
    -- Drop the temporary stored procedure.
    DROP PROCEDURE IF EXISTS `MyDatabaseName`.`spCreateIndex` $$
    DELIMITER ;

Lascia un commento