在 SELECT 语句中创建没有单独的 Create TABLE 的临时表

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.

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.

Use this syntax:

CREATE TEMPORARY TABLE t1 (select * from t2);

Engine must be before select:

CREATE TEMPORARY TABLE temp1 ENGINE=MEMORY 
as (select * from table1)

ENGINE=MEMORY is not supported when table contains BLOB/TEXT columns

In addition to psparrow's answer if you need to add an index to your temporary table do:

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