在 MySQL 中使用 select 中的索引创建一个临时表

Create a temporary table in MySQL with an index from a select

提问by solick

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 TABLEbecause this causes an implicit commit.

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

Therefore I'm looking for the syntax to add the INDEXfor tempidduring creation. Can anyone be of help?

所以我在寻找的语法来添加INDEX对tempid创建过程中。任何人都可以提供帮助吗？

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

采纳答案by solick

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

你好...

回答by IvanD

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.

我为 CREATE TEMPORARY TABLE SELECT 的正确语法挣扎了很长时间。想通了一些事情后，我想与社区的其他人分享答案。

Basic information about the statement is available at the following MySQL links:

以下 MySQL 链接提供了有关该语句的基本信息：

CREATE TABLE SELECTand 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 
   (tag BIGINT,
   my_time DATETIME,  
   INDEX my_unique_index_name (tag) )
   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
   

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
   

2. 添加一个新的主键：

   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. 创建附加列

   您可以创建一个新表，该表的列数多于 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
   

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

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

   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. 创建期间的高级字段定义

   创建普通表时，所有常用的列定义都可用。例子：

   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
   

回答by Ayush Bilala

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;