Created
March 11, 2021 12:31
-
-
Save slifin/47e4e3d5c94e1a0d5ef6d8c4626d9be0 to your computer and use it in GitHub Desktop.
temp table syntax with indexes
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE TEMPORARY TABLE | |
view_range ( INDEX view_date(date) ) | |
AS | |
SELECT "2021-01-01" AS `date`, 1 AS overnight | |
UNION ALL | |
SELECT "2021-01-02" AS `date`, 0 AS overnight; | |
SHOW INDEX FROM view_range; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
238
I wrestled quite a while with the proper syntax for CREATE TEMPORARY TABLE SELECT. Having figured out a few things, I wanted to share the answers with the rest of the community.
Basic information about the statement is available at the following MySQL links:
CREATE TABLE SELECT and CREATE TABLE.
At times it can be daunting to interpret the spec. Since most people learn best from examples, I will share how I have created a working statement, and how you can modify it to work for you.
Add multiple indexes
This statement shows how to add multiple indexes (note that index names - in lower case - are optional):
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
Add a new primary key:
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
Create additional columns
You can create a new table with more columns than are specified in the SELECT statement. Specify the additional column in the table definition. Columns specified in the table definition and not found in select will be first columns in the new table, followed by the columns inserted by the SELECT statement.
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
Redefining data types for the columns from SELECT
You can redefine the data type of a column being SELECTed. In the example below, column tag is a MEDIUMINT in core.my_big_table and I am redefining it to a BIGINT in 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
Advanced field definitions during create
All the usual column definitions are available as when you create a normal table. Example:
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