MySQL 在没有单独的 CREATE TABLE 的情况下在 SELECT 语句中创建临时表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5859391/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Create a temporary table in a SELECT statement without a separate CREATE TABLE
提问by Bryan Field
Is it possible to create a temporary (session only) table from a select statement without using a create table statement and specifying each column type? I know derived tables are capable of this, but those are super-temporary (statement-only) and I want to re-use.
是否可以在不使用 create table 语句并指定每个列类型的情况下从 select 语句创建临时（仅限会话）表？我知道派生表能够做到这一点，但那些是超临时的（仅限语句），我想重用。
It would save time if I did not have to write up a create table command and keep the column list and type list matched up.
如果我不必编写创建表命令并保持列列表和类型列表匹配，这将节省时间。
回答by psparrow
CREATE TEMPORARY TABLE IF NOT EXISTS table2 AS (SELECT * FROM table1)
From the manual found at http://dev.mysql.com/doc/refman/5.7/en/create-table.html
来自http://dev.mysql.com/doc/refman/5.7/en/create-table.html的手册
You can use the TEMPORARY keyword when creating a table. A TEMPORARY table is visible only to the current session, and is dropped automaticallywhen the session is closed. This means that two different sessions can use the same temporary table name without conflicting with each other or with an existing non-TEMPORARY table of the same name. (The existing table is hidden until the temporary table is dropped.) To create temporary tables, you must have the CREATE TEMPORARY TABLES privilege.
创建表时可以使用 TEMPORARY 关键字。TEMPORARY 表仅对当前会话可见，并在会话关闭时自动删除。这意味着两个不同的会话可以使用相同的临时表名称，而不会相互冲突或与现有的同名非临时表发生冲突。（在删除临时表之前，现有表是隐藏的。）要创建临时表，您必须具有 CREATE TEMPORARY TABLES 权限。
回答by RafaSashi
In addition to psparrow'sanswer if you need to add an indexto your temporary table do:
除了psparrow 的答案之外，如果您需要向临时表添加索引，请执行以下操作：
CREATE TEMPORARY TABLE IF NOT EXISTS
temp_table ( INDEX(col_2) )
ENGINE=MyISAM
AS (
SELECT col_1, coll_2, coll_3
FROM mytable
)
It also works with PRIMARY KEY
它也适用于 PRIMARY KEY
回答by rizon
Use this syntax:
使用以下语法：
CREATE TEMPORARY TABLE t1 (select * from t2);
回答by Crusader
Engine must be before select:
引擎必须在选择之前：
CREATE TEMPORARY TABLE temp1 ENGINE=MEMORY
as (select * from table1)
回答by Cris
ENGINE=MEMORYis not supported when table contains BLOB/TEXTcolumns
ENGINE=MEMORY当表包含BLOB/TEXT列时不支持
回答by user11949964
CREATE TEMPORARY TABLE IF NOT EXISTS to_table_name AS (SELECT * FROM from_table_name)
