Crear una tabla temporal en MySQL con un índice de una selección

Luché bastante tiempo con la sintaxis adecuada para CREAR SELECCIÓN DE TABLA TEMPORAL. Habiendo descubierto algunas cosas, quería compartir las respuestas con el resto de la comunidad.

La información básica sobre la instrucción está disponible en los siguientes enlaces MySQL:

CREAR TABLA SELECCIONE y CREATE TABLE.

A veces puede ser desalentador interpretar la especificación. Dado que la mayoría de la gente aprende mejor de los ejemplos, voy a compartir cómo he creado un declaración de trabajo, y cómo puede modificarla para que funcione para usted.

1. Añadir múltiples índices

   Esta instrucción muestra cómo agregar múltiples índices (tenga en cuenta que los nombres de los índices, en minúsculas, son opcionales):

   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. Añadir una nueva clave primaria :

   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. Crear columnas adicionales

   Puede crear una nueva tabla con más columnas que las especificadas en la instrucción SELECT. Especificar el adicional columna en la definición de la tabla. Las columnas especificadas en la definición de tabla y no encontradas en select serán las primeras columnas de la nueva tabla, seguidas de las columnas insertadas por la instrucción 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. Redefiniendo los tipos de datos para las columnas de SELECT

   Puede redefinir el tipo de datos de una columna seleccionada. En el siguiente ejemplo, la etiqueta de columna es un MEDIUMINT en core. my_big_table y lo estoy redefiniendo a un BIGINT en núcleo.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. Definiciones avanzadas de campos durante create

   Todas las definiciones de columna habituales están disponibles como cuando se crea una tabla normal. Ejemplo:

   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
   

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

Ejemplo:

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;