<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="innodb-information-schema-temp-table-info">
      </a>
      17.15.7 InnoDB INFORMATION_SCHEMA Temporary Table Info Table
     </h3>
    </div>
   </div>
  </div>
  <p>
   <a class="link" href="information-schema-innodb-temp-table-info-table.html" title="28.4.27 The INFORMATION_SCHEMA INNODB_TEMP_TABLE_INFO Table">
    <code class="literal">
     INNODB_TEMP_TABLE_INFO
    </code>
   </a>
   provides
      information about user-created
   <code class="literal">
    InnoDB
   </code>
   temporary
      tables that are active in the
   <code class="literal">
    InnoDB
   </code>
   instance.
      It does not provide information about internal
   <code class="literal">
    InnoDB
   </code>
   temporary tables used by the optimizer.
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa25148507"><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 prompt">mysql&gt;</span> <span class="token keyword">SHOW</span> <span class="token keyword">TABLES</span> <span class="token keyword">FROM</span> INFORMATION_SCHEMA <span class="token operator">LIKE</span> <span class="token string">'INNODB_TEMP%'</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> Tables_in_INFORMATION_SCHEMA (INNODB_TEMP%) <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> INNODB_TEMP_TABLE_INFO                      <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
  </div>
  <p>
   For the table definition, see
   <a class="xref" href="information-schema-innodb-temp-table-info-table.html" title="28.4.27 The INFORMATION_SCHEMA INNODB_TEMP_TABLE_INFO Table">
    Section 28.4.27, “The INFORMATION_SCHEMA INNODB_TEMP_TABLE_INFO Table”
   </a>
   .
  </p>
  <div class="example">
   <a name="innodb-information-schema-temp-table-info-example">
   </a>
   <p class="title">
    <b>
     Example 17.12 INNODB_TEMP_TABLE_INFO
    </b>
   </p>
   <div class="example-contents">
    <p>
     This example demonstrates characteristics of the
     <a class="link" href="information-schema-innodb-temp-table-info-table.html" title="28.4.27 The INFORMATION_SCHEMA INNODB_TEMP_TABLE_INFO Table">
      <code class="literal">
       INNODB_TEMP_TABLE_INFO
      </code>
     </a>
     table.
    </p>
    <div class="orderedlist">
     <ol class="orderedlist" type="1">
      <li class="listitem">
       <p>
        Create a simple
        <code class="literal">
         InnoDB
        </code>
        temporary table:
       </p>
       <div class="copytoclipboard-wrapper">
        <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa20324115"><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 prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TEMPORARY</span> <span class="token keyword">TABLE</span> t1 <span class="token punctuation">(</span>c1 <span class="token datatype">INT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">)</span> <span class="token keyword">ENGINE</span><span class="token operator">=</span>INNODB<span class="token punctuation">;</span></code></pre>
       </div>
      </li>
      <li class="listitem">
       <p>
        Query
        <a class="link" href="information-schema-innodb-temp-table-info-table.html" title="28.4.27 The INFORMATION_SCHEMA INNODB_TEMP_TABLE_INFO Table">
         <code class="literal">
          INNODB_TEMP_TABLE_INFO
         </code>
        </a>
        to
            view the temporary table metadata.
       </p>
       <div class="copytoclipboard-wrapper">
        <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa17172592"><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 prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span>INNODB_TEMP_TABLE_INFO\G
<span class="token output"><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span> 1. row <span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span>
            TABLE_ID<span class="token punctuation">:</span> 194
                NAME<span class="token punctuation">:</span> #sql7a79_1_0
              N_COLS<span class="token punctuation">:</span> 4
               SPACE<span class="token punctuation">:</span> 182</span></code></pre>
       </div>
       <p>
        The
        <code class="literal">
         TABLE_ID
        </code>
        is a unique identifier for
            the temporary table. The
        <code class="literal">
         NAME
        </code>
        column
            displays the system-generated name for the temporary table,
            which is prefixed with
        <span class="quote">
         “
         <span class="quote">
          #sql
         </span>
         ”
        </span>
        . The number of
            columns (
        <code class="literal">
         N_COLS
        </code>
        ) is 4 rather than 1
            because
        <code class="literal">
         InnoDB
        </code>
        always creates three
            hidden table columns (
        <code class="literal">
         DB_ROW_ID
        </code>
        ,
        <code class="literal">
         DB_TRX_ID
        </code>
        , and
        <code class="literal">
         DB_ROLL_PTR
        </code>
        ).
       </p>
      </li>
      <li class="listitem">
       <p>
        Restart MySQL and query
        <a class="link" href="information-schema-innodb-temp-table-info-table.html" title="28.4.27 The INFORMATION_SCHEMA INNODB_TEMP_TABLE_INFO Table">
         <code class="literal">
          INNODB_TEMP_TABLE_INFO
         </code>
        </a>
        .
       </p>
       <div class="copytoclipboard-wrapper">
        <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa46310824"><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 prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span>INNODB_TEMP_TABLE_INFO\G</code></pre>
       </div>
       <p>
        An empty set is returned because
        <a class="link" href="information-schema-innodb-temp-table-info-table.html" title="28.4.27 The INFORMATION_SCHEMA INNODB_TEMP_TABLE_INFO Table">
         <code class="literal">
          INNODB_TEMP_TABLE_INFO
         </code>
        </a>
        and its
            data are not persisted to disk when the server is shut down.
       </p>
      </li>
      <li class="listitem">
       <p>
        Create a new temporary table.
       </p>
       <div class="copytoclipboard-wrapper">
        <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa69949400"><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 prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TEMPORARY</span> <span class="token keyword">TABLE</span> t1 <span class="token punctuation">(</span>c1 <span class="token datatype">INT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">)</span> <span class="token keyword">ENGINE</span><span class="token operator">=</span>INNODB<span class="token punctuation">;</span></code></pre>
       </div>
      </li>
      <li class="listitem">
       <p>
        Query
        <a class="link" href="information-schema-innodb-temp-table-info-table.html" title="28.4.27 The INFORMATION_SCHEMA INNODB_TEMP_TABLE_INFO Table">
         <code class="literal">
          INNODB_TEMP_TABLE_INFO
         </code>
        </a>
        to
            view the temporary table metadata.
       </p>
       <div class="copytoclipboard-wrapper">
        <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa14724969"><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 prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span>INNODB_TEMP_TABLE_INFO\G
<span class="token output"><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span> 1. row <span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span>
            TABLE_ID<span class="token punctuation">:</span> 196
                NAME<span class="token punctuation">:</span> #sql7b0e_1_0
              N_COLS<span class="token punctuation">:</span> 4
               SPACE<span class="token punctuation">:</span> 184</span></code></pre>
       </div>
       <p>
        The
        <code class="literal">
         SPACE
        </code>
        ID may be different because it
            is dynamically generated when the server is started.
       </p>
      </li>
     </ol>
    </div>
   </div>
  </div>
  <br class="example-break"/>
 </div>
 <br/>
</div>
