撒拉嘿哟木头
2011-05-02 16:03
采纳率: 40%
浏览 292

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

转载于:https://stackoverflow.com/questions/5859391/create-a-temporary-table-in-a-select-statement-without-a-separate-create-table

  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

5条回答 默认 最新

  • ℙℕℤℝ 2011-05-02 16:11
    已采纳
    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

    You can use the TEMPORARY keyword when creating a table. A TEMPORARY table is visible only to the current session, and is dropped automatically when 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.

    已采纳该答案
    评论
    解决 无用
    打赏 举报
  • 喵-见缝插针 2012-12-06 21:55

    Use this syntax:

    CREATE TEMPORARY TABLE t1 (select * from t2);
    
    评论
    解决 无用
    打赏 举报
  • ℡Wang Yan 2013-02-14 14:02

    Engine must be before select:

    CREATE TEMPORARY TABLE temp1 ENGINE=MEMORY 
    as (select * from table1)
    
    评论
    解决 无用
    打赏 举报
  • 喵-见缝插针 2013-02-23 17:36

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

    评论
    解决 无用
    打赏 举报
  • 衫裤跑路 2013-06-17 19:43

    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

    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题