<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="create-temporary-table">
      </a>
      15.1.20.2 CREATE TEMPORARY TABLE Statement
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045185392176">
  </a>
  <a class="indexterm" name="idm46045185391136">
  </a>
  <p>
   You can use the
   <code class="literal">
    TEMPORARY
   </code>
   keyword when
        creating a table. A
   <code class="literal">
    TEMPORARY
   </code>
   table is
        visible only within 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-
   <code class="literal">
    TEMPORARY
   </code>
   table of the same name. (The
        existing table is hidden until the temporary table is dropped.)
  </p>
  <p>
   <code class="literal">
    InnoDB
   </code>
   does not support compressed temporary
        tables. When
   <a class="link" href="innodb-parameters.html#sysvar_innodb_strict_mode">
    <code class="literal">
     innodb_strict_mode
    </code>
   </a>
   is enabled (the default),
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE TEMPORARY
        TABLE
    </code>
   </a>
   returns an error if
   <code class="literal">
    ROW_FORMAT=COMPRESSED
   </code>
   or
   <code class="literal">
    KEY_BLOCK_SIZE
   </code>
   is specified. If
   <a class="link" href="innodb-parameters.html#sysvar_innodb_strict_mode">
    <code class="literal">
     innodb_strict_mode
    </code>
   </a>
   is disabled,
        warnings are issued and the temporary table is created using a
        non-compressed row format. The
   <a class="link" href="innodb-parameters.html#sysvar_innodb_file_per_table">
    <code class="literal">
     innodb_file_per-table
    </code>
   </a>
   option
        does not affect the creation of
   <code class="literal">
    InnoDB
   </code>
   temporary tables.
  </p>
  <p>
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE TABLE
    </code>
   </a>
   causes an implicit
        commit, except when used with the
   <code class="literal">
    TEMPORARY
   </code>
   keyword. See
   <a class="xref" href="implicit-commit.html" title="15.3.3 Statements That Cause an Implicit Commit">
    Section 15.3.3, “Statements That Cause an Implicit Commit”
   </a>
   .
  </p>
  <p>
   <code class="literal">
    TEMPORARY
   </code>
   tables have a very loose
        relationship with databases (schemas). Dropping a database does
        not automatically drop any
   <code class="literal">
    TEMPORARY
   </code>
   tables
        created within that database.
  </p>
  <a class="indexterm" name="idm46045185373056">
  </a>
  <a class="indexterm" name="idm46045185371568">
  </a>
  <p>
   To create a temporary table, you must have the
   <a class="link" href="privileges-provided.html#priv_create-temporary-tables">
    <code class="literal">
     CREATE TEMPORARY TABLES
    </code>
   </a>
   privilege. After a session has created a temporary table, the
        server performs no further privilege checks on the table. The
        creating session can perform any operation on the table, such as
   <a class="link" href="drop-table.html" title="15.1.32 DROP TABLE Statement">
    <code class="literal">
     DROP TABLE
    </code>
   </a>
   ,
   <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
    <code class="literal">
     INSERT
    </code>
   </a>
   ,
   <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
    <code class="literal">
     UPDATE
    </code>
   </a>
   , or
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   .
  </p>
  <p>
   One implication of this behavior is that a session can
        manipulate its temporary tables even if the current user has no
        privilege to create them. Suppose that the current user does not
        have the
   <a class="link" href="privileges-provided.html#priv_create-temporary-tables">
    <code class="literal">
     CREATE TEMPORARY TABLES
    </code>
   </a>
   privilege but is able to execute a definer-context stored
        procedure that executes with the privileges of a user who does
        have
   <a class="link" href="privileges-provided.html#priv_create-temporary-tables">
    <code class="literal">
     CREATE TEMPORARY TABLES
    </code>
   </a>
   and
        that creates a temporary table. While the procedure executes,
        the session uses the privileges of the defining user. After the
        procedure returns, the effective privileges revert to those of
        the current user, which can still see the temporary table and
        perform any operation on it.
  </p>
  <p>
   You cannot use
   <code class="literal">
    CREATE TEMPORARY TABLE ...
        LIKE
   </code>
   to create an empty table based on the definition
        of a table that resides in the
   <code class="literal">
    mysql
   </code>
   tablespace,
   <code class="literal">
    InnoDB
   </code>
   system tablespace
        (
   <code class="literal">
    innodb_system
   </code>
   ), or a general tablespace. The
        tablespace definition for such a table includes a
   <code class="literal">
    TABLESPACE
   </code>
   attribute that defines the
        tablespace where the table resides, and the aforementioned
        tablespaces do not support temporary tables. To create a
        temporary table based on the definition of such a table, use
        this syntax instead:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa73478680"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TEMPORARY</span> <span class="token keyword">TABLE</span> <em class="replaceable">new_tbl</em> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> <em class="replaceable">orig_tbl</em> <span class="token keyword">LIMIT</span> <span class="token number">0</span><span class="token punctuation">;</span></code></pre>
  </div>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    Support for
    <code class="literal">
     TABLESPACE =
          innodb_file_per_table
    </code>
    and
    <code class="literal">
     TABLESPACE =
          innodb_temporary
    </code>
    clauses with
    <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
     <code class="literal">
      CREATE TEMPORARY
          TABLE
     </code>
    </a>
    is deprecated; expect it to be removed in a
          future version of MySQL.
   </p>
  </div>
 </div>
 <br/>
</div>
