<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="information-schema-innodb-virtual-table">
      </a>
      28.4.29 The INFORMATION_SCHEMA INNODB_VIRTUAL Table
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045076146128">
  </a>
  <p>
   The
   <a class="link" href="information-schema-innodb-virtual-table.html" title="28.4.29 The INFORMATION_SCHEMA INNODB_VIRTUAL Table">
    <code class="literal">
     INNODB_VIRTUAL
    </code>
   </a>
   table provides
      metadata about
   <code class="literal">
    InnoDB
   </code>
   <a class="link" href="glossary.html#glos_virtual_generated_column" title="virtual generated column">
    virtual generated
      columns
   </a>
   and columns upon which virtual generated columns
      are based.
  </p>
  <p>
   A row appears in the
   <a class="link" href="information-schema-innodb-virtual-table.html" title="28.4.29 The INFORMATION_SCHEMA INNODB_VIRTUAL Table">
    <code class="literal">
     INNODB_VIRTUAL
    </code>
   </a>
   table for each column upon which a virtual generated column is
      based.
  </p>
  <p>
   The
   <a class="link" href="information-schema-innodb-virtual-table.html" title="28.4.29 The INFORMATION_SCHEMA INNODB_VIRTUAL Table">
    <code class="literal">
     INNODB_VIRTUAL
    </code>
   </a>
   table has these
      columns:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       TABLE_ID
      </code>
     </p>
     <p>
      An identifier representing the table associated with the
          virtual column; the same value as
      <code class="literal">
       INNODB_TABLES.TABLE_ID
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       POS
      </code>
     </p>
     <p>
      The position value of the
      <a class="link" href="glossary.html#glos_virtual_generated_column" title="virtual generated column">
       virtual
          generated column
      </a>
      . The value is large because it encodes
          the column sequence number and ordinal position. The formula
          used to calculate the value uses a bitwise operation:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-simple"><div class="docs-select-all right" id="sa9600309"><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-simple"><span class="token punctuation">(</span><span class="token punctuation">(</span><em class="replaceable">n</em>th virtual generated column for the InnoDB instance <span class="token operator">+</span> 1<span class="token punctuation">)</span> <span class="token operator">&lt;&lt;</span> 16<span class="token punctuation">)</span>
<span class="token operator">+</span> the ordinal position of the virtual generated column</code></pre>
     </div>
     <p>
      For example, if the first virtual generated column in the
      <code class="literal">
       InnoDB
      </code>
      instance is the third column of the
          table, the formula is
      <code class="literal">
       (0 + 1) &lt;&lt; 16) +
          2
      </code>
      . The first virtual generated column in the
      <code class="literal">
       InnoDB
      </code>
      instance is always number 0. As the
          third column in the table, the ordinal position of the virtual
          generated column is 2. Ordinal positions are counted from 0.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       BASE_POS
      </code>
     </p>
     <p>
      The ordinal position of the columns upon which a virtual
          generated column is based.
     </p>
    </li>
   </ul>
  </div>
  <h4>
   <a name="idm46045076125248">
   </a>
   Example
  </h4>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa43141537"><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">TABLE</span> <span class="token punctuation">`</span>t1<span class="token punctuation">`</span> <span class="token punctuation">(</span>
         <span class="token punctuation">`</span>a<span class="token punctuation">`</span> <span class="token datatype">int</span><span class="token punctuation">(</span><span class="token number">11</span><span class="token punctuation">)</span> <span class="token keyword">DEFAULT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
         <span class="token punctuation">`</span>b<span class="token punctuation">`</span> <span class="token datatype">int</span><span class="token punctuation">(</span><span class="token number">11</span><span class="token punctuation">)</span> <span class="token keyword">DEFAULT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
         <span class="token punctuation">`</span>c<span class="token punctuation">`</span> <span class="token datatype">int</span><span class="token punctuation">(</span><span class="token number">11</span><span class="token punctuation">)</span> <span class="token keyword">GENERATED</span> <span class="token keyword">ALWAYS</span> <span class="token keyword">AS</span> <span class="token punctuation">(</span>a<span class="token operator">+</span>b<span class="token punctuation">)</span> <span class="token keyword">VIRTUAL</span><span class="token punctuation">,</span>
         <span class="token punctuation">`</span>h<span class="token punctuation">`</span> <span class="token datatype">varchar</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span> <span class="token keyword">DEFAULT</span> <span class="token boolean">NULL</span>
       <span class="token punctuation">)</span> <span class="token keyword">ENGINE</span><span class="token operator">=</span>InnoDB <span class="token keyword">DEFAULT</span> <span class="token keyword">CHARSET</span><span class="token operator">=</span>utf8mb4<span class="token punctuation">;</span>

<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_VIRTUAL
       <span class="token keyword">WHERE</span> TABLE_ID <span class="token keyword">IN</span>
         <span class="token punctuation">(</span><span class="token keyword">SELECT</span> TABLE_ID <span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span>INNODB_TABLES
          <span class="token keyword">WHERE</span> <span class="token keyword">NAME</span> <span class="token operator">LIKE</span> <span class="token string">"test/t1"</span><span class="token punctuation">)</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>
<span class="token output"><span class="token punctuation">|</span> TABLE_ID <span class="token punctuation">|</span> POS   <span class="token punctuation">|</span> BASE_POS <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>
<span class="token output"><span class="token punctuation">|</span>       98 <span class="token punctuation">|</span> 65538 <span class="token punctuation">|</span>        0 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>       98 <span class="token punctuation">|</span> 65538 <span class="token punctuation">|</span>        1 <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></code></pre>
  </div>
  <h4>
   <a name="idm46045076116320">
   </a>
   Notes
  </h4>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      If a constant value is assigned to a
      <a class="link" href="glossary.html#glos_virtual_generated_column" title="virtual generated column">
       virtual
          generated column
      </a>
      , as in the following table, an entry
          for the column does not appear in the
      <code class="literal">
       INNODB_VIRTUAL
      </code>
      table. For an entry to
          appear, a virtual generated column must have a base column.
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa26535097"><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">TABLE</span> <span class="token punctuation">`</span>t1<span class="token punctuation">`</span> <span class="token punctuation">(</span>
  <span class="token punctuation">`</span>a<span class="token punctuation">`</span> <span class="token datatype">int</span><span class="token punctuation">(</span><span class="token number">11</span><span class="token punctuation">)</span> <span class="token keyword">DEFAULT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
  <span class="token punctuation">`</span>b<span class="token punctuation">`</span> <span class="token datatype">int</span><span class="token punctuation">(</span><span class="token number">11</span><span class="token punctuation">)</span> <span class="token keyword">DEFAULT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
  <span class="token punctuation">`</span>c<span class="token punctuation">`</span> <span class="token datatype">int</span><span class="token punctuation">(</span><span class="token number">11</span><span class="token punctuation">)</span> <span class="token keyword">GENERATED</span> <span class="token keyword">ALWAYS</span> <span class="token keyword">AS</span> <span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">)</span> <span class="token keyword">VIRTUAL</span>
<span class="token punctuation">)</span> <span class="token keyword">ENGINE</span><span class="token operator">=</span>InnoDB <span class="token keyword">DEFAULT</span> <span class="token keyword">CHARSET</span><span class="token operator">=</span>utf8mb4<span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      However, metadata for such a column does appear in the
      <a class="link" href="information-schema-innodb-columns-table.html" title="28.4.9 The INFORMATION_SCHEMA INNODB_COLUMNS Table">
       <code class="literal">
        INNODB_COLUMNS
       </code>
      </a>
      table.
     </p>
    </li>
    <li class="listitem">
     <p>
      You must have the
      <a class="link" href="privileges-provided.html#priv_process">
       <code class="literal">
        PROCESS
       </code>
      </a>
      privilege to query this table.
     </p>
    </li>
    <li class="listitem">
     <p>
      Use the
      <code class="literal">
       INFORMATION_SCHEMA
      </code>
      <a class="link" href="information-schema-columns-table.html" title="28.3.8 The INFORMATION_SCHEMA COLUMNS Table">
       <code class="literal">
        COLUMNS
       </code>
      </a>
      table or the
      <a class="link" href="show-columns.html" title="15.7.7.6 SHOW COLUMNS Statement">
       <code class="literal">
        SHOW COLUMNS
       </code>
      </a>
      statement to view
          additional information about the columns of this table,
          including data types and default values.
     </p>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
