在MySQL中使用选择索引创建临时表 [英] Create a temporary table in MySQL with an index from a select

问题描述

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

I have a stored function where I use temporary tables. For performance reasons, I need an index in that table. Unfortunately, I cannot use ALTER TABLE because this causes an implicit commit.

因此，我正在寻找在创建过程中为tempid添加INDEX的语法.任何人都可以帮忙吗?

Therefore I'm looking for the syntax to add the INDEX for tempid during creation. Can anyone be of help?

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

推荐答案

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

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.

问候...

这篇关于在MySQL中使用选择索引创建临时表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！