在 MySQL 中创建一个临时表，其中包含一个来自 select 的索引

我有一个使用临时表的存储函数。出于性能原因，我需要在该表中添加一个索引。不幸的是，我不能使用 ALTER TABLE，因为这会导致隐式提交。

因此，我正在寻找在创建期间为 tempid添加 INDEX的语法。有人能帮忙吗？

CREATE TEMPORARY TABLE tmpLivecheck
(
tmpid INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY
)
SELECT *
FROM   tblLivecheck_copy
WHERE  tblLivecheck_copy.devId = did;
125569 次浏览
小开 
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
[table_options]
select_statement

Example : 

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;
小开
最佳答案

Did find the answer on my own. My problem was, that i use two temporary tables for a join and create the second one out of the first one. But the Index was not copied during creation...

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;

... solved my problem.

Greetings...

小开

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.

  1. 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

  2. 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

  3. 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

  4. 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
(my_important_column BIGINT,
my_time DATETIME,
INDEX my_unique_index_name (my_important_column) )
SELECT * FROM core.my_big_table

  5. 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

提交答案