在MySQL中使用SELECT语句创建临时表并添加索引

Question

我有一个存储函数，在其中使用了临时表。出于性能原因，我需要在该表中添加索引。不幸的是，我无法使用 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;

- solick

Answer 1

我曾经花了很长时间来探索CREATE TEMPORARY TABLE SELECT的正确语法。在弄清楚一些问题后，我想与社区的其他人分享答案。

有关该语句的基本信息可在以下MySQL链接中找到：

CREATE TABLE SELECT和CREATE TABLE。

有时要解释规范可能会令人生畏。由于大多数人从示例中学习得最好，我将分享如何创建一个工作语句以及如何修改它以适合您的需求。

1. 添加多个索引

   此语句显示如何添加多个索引（请注意，索引名称（小写）是可选的）：

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

添加新的主键:

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

创建额外的列

您可以创建一个新表，该表具有比 SELECT 语句中指定的列更多的列。 在表定义中指定附加列。 在表定义中指定但在 select 中未找到的列将是新表中的第一列，紧随其后的是 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

重新定义SELECT中的列的数据类型

您可以重新定义正在SELECT的列的数据类型。在下面的示例中，标签列是core.my_big_table中的MEDIUMINT类型，我将其重新定义为core.my_tmp_table中的BIGINT类型。

 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

创建过程中的高级字段定义

您可以像创建普通表格一样使用所有常见的列定义。例如：

 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

- IvanD

---

你让我开心了，这真的很有帮助！ - BastiaanWW

虽然我可能会让人觉得这只是一个无用的“谢谢”评论，但我希望你知道你救了我的大忙。我希望我能给你更多的回报，而不仅仅是点个赞。使用带有索引的临时表可以解决临时表自身连接的限制，在我的情况下索引非常关键。 - Plasmarob

Answer 2

我没有自己找到答案。我的问题是，我为连接使用了两个临时表，并将第二个表从第一个表中创建。但是在创建过程中未复制索引...

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;

...解决了我的问题。

问候...

- solick

Answer 3

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

例子：

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;

- Ayush Bilala

---

@solick PRIMARY KEY 总是被索引。 - ebyrob