Créer une table temporaire dans MySQL avec un index à partir d'un select

Question

J'ai une fonction stockée où j'utilise des tables temporaires. Pour des raisons de performance, j'ai besoin d'un index dans cette table. Malheureusement, je ne peux pas utiliser ALTER TABLE car cela provoque un commit implicite.

Je cherche donc la syntaxe permettant d'ajouter l'élément INDEX pour tempid pendant la création. Quelqu'un peut-il m'aider ?

CREATE TEMPORARY TABLE tmpLivecheck 
(
    tmpid INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY
)
SELECT *
FROM   tblLivecheck_copy
WHERE  tblLivecheck_copy.devId = did;

Answer 1

J'ai lutté pendant un certain temps avec la syntaxe appropriée pour CREATE TEMPORARY TABLE SELECT. Après avoir compris certaines choses, j'ai voulu partager mes réponses avec le reste de la communauté.

Des informations de base sur cette déclaration sont disponibles sur les liens MySQL suivants :

CRÉER UNE TABLE SELECT y CRÉER UNE TABLE .

L'interprétation de la spécification peut parfois être déconcertante. Puisque la plupart des gens apprennent mieux à partir d'exemples, je vais partager la façon dont j'ai créé une déclaration fonctionnelle, et comment vous pouvez la modifier pour qu'elle vous convienne.

1. Ajouter des index multiples

   Cette déclaration montre comment ajouter plusieurs index (notez que les noms d'index - en minuscules - sont facultatifs) :

   CREATE TEMPORARY TABLE core.my_tmp_table 
   (INDEX my_index_name (tag, time), UNIQUE my_unique_index_name (order_number))
   SELECT * FROM core.my_big_table
   WHERE my_val = 1

2. Ajouter une nouvelle clé primaire :

   CREATE TEMPORARY TABLE core.my_tmp_table 
   (PRIMARY KEY my_pkey (order_number),
   INDEX cmpd_key (user_id, time))
   SELECT * FROM core.my_big_table

3. Créer des colonnes supplémentaires

   Vous pouvez créer une nouvelle table avec plus de colonnes que celles spécifiées dans l'instruction SELECT. Spécifiez la colonne supplémentaire dans la définition de la table. Les colonnes spécifiées dans la définition de la table et non trouvées dans l'instruction SELECT seront les premières colonnes de la nouvelle table, suivies des colonnes insérées par l'instruction SELECT.

   CREATE TEMPORARY TABLE core.my_tmp_table 
   (my_new_id BIGINT NOT NULL AUTO_INCREMENT,  
   PRIMARY KEY my_pkey (my_new_id), INDEX my_unique_index_name (invoice_number))
   SELECT * FROM core.my_big_table

4. Redéfinition des types de données pour les colonnes de SELECT

   Vous pouvez redéfinir le type de données d'une colonne faisant l'objet d'une SÉLECTION. Dans l'exemple ci-dessous, la colonne tag est un MEDIUMINT dans core.my_big_table et je la redéfinis en BIGINT dans core.my_tmp_table.

   CREATE TEMPORARY TABLE core.my_tmp_table 
   (tag BIGINT,
   my_time DATETIME,  
   INDEX my_unique_index_name (tag) )
   SELECT * FROM core.my_big_table

5. Définitions avancées des champs lors de la création

   Toutes les définitions de colonnes habituelles sont disponibles comme lorsque vous créez une table normale. Exemple :

   CREATE TEMPORARY TABLE core.my_tmp_table 
   (id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
   value BIGINT UNSIGNED NOT NULL DEFAULT 0 UNIQUE,
   location VARCHAR(20) DEFAULT "NEEDS TO BE SET",
   country CHAR(2) DEFAULT "XX" COMMENT "Two-letter country code",  
   INDEX my_index_name (location))
   ENGINE=MyISAM 
   SELECT * FROM core.my_big_table

Answer 2

J'ai trouvé la réponse par moi-même. Mon problème est que j'utilise deux tables temporaires pour une jointure et que je crée la seconde à partir de la première. Mais l'index n'a pas été copié lors de la création...

CREATE TEMPORARY TABLE tmpLivecheck (tmpid INTEGER NOT NULL AUTO_INCREMENT, PRIMARY    
KEY(tmpid), INDEX(tmpid))
SELECT * FROM tblLivecheck_copy WHERE tblLivecheck_copy.devId = did;

CREATE TEMPORARY TABLE tmpLiveCheck2 (tmpid INTEGER NOT NULL, PRIMARY KEY(tmpid), 
INDEX(tmpid))  
SELECT * FROM tmpLivecheck;

... a résolu mon problème.

Salutations...

Answer 3

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
[table_options]
select_statement

Exemple :

CREATE TEMPORARY TABLE IF NOT EXISTS mytable
(id int(11) NOT NULL, PRIMARY KEY (id)) ENGINE=MyISAM;
INSERT IGNORE INTO mytable SELECT id FROM table WHERE xyz;