<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="invisible-columns">
      </a>
      15.1.20.10 Invisible Columns
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045184610736">
  </a>
  <p>
   MySQL 8.4 supports invisible columns. An invisible
        column is normally hidden to queries, but can be accessed if
        explicitly referenced.
  </p>
  <p>
   As an illustration of when invisible columns may be useful,
        suppose that an application uses
   <code class="literal">
    SELECT *
   </code>
   queries to access a table, and must continue to work without
        modification even if the table is altered to add a new column
        that the application does not expect to be there. In a
   <code class="literal">
    SELECT *
   </code>
   query, the
   <code class="literal">
    *
   </code>
   evaluates to all table columns, except those that are invisible,
        so the solution is to add the new column as an invisible column.
        The column remains
   <span class="quote">
    “
    <span class="quote">
     hidden
    </span>
    ”
   </span>
   from
   <code class="literal">
    SELECT
        *
   </code>
   queries, and the application continues to work as
        previously. A newer version of the application can refer to the
        invisible column if necessary by explicitly referencing it.
  </p>
  <p>
   The following sections detail how MySQL treats invisible
        columns.
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="xref" href="invisible-columns.html#invisible-column-ddl-statements" title="DDL Statements and Invisible Columns">
       DDL Statements and Invisible Columns
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="invisible-columns.html#invisible-column-dml-statements" title="DML Statements and Invisible Columns">
       DML Statements and Invisible Columns
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="invisible-columns.html#invisible-column-metadata" title="Invisible Column Metadata">
       Invisible Column Metadata
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="invisible-columns.html#invisible-column-binary-logging" title="The Binary Log and Invisible Columns">
       The Binary Log and Invisible Columns
      </a>
     </p>
    </li>
   </ul>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="invisible-column-ddl-statements">
       </a>
       DDL Statements and Invisible Columns
      </h5>
     </div>
    </div>
   </div>
   <p>
    Columns are visible by default. To explicitly specify
          visibility for a new column, use a
    <code class="literal">
     VISIBLE
    </code>
    or
    <code class="literal">
     INVISIBLE
    </code>
    keyword as part of the column
          definition for
    <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
     <code class="literal">
      CREATE TABLE
     </code>
    </a>
    or
    <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
     <code class="literal">
      ALTER TABLE
     </code>
    </a>
    :
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa92909814"><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> t1 <span class="token punctuation">(</span>
  i <span class="token datatype">INT</span><span class="token punctuation">,</span>
  j <span class="token datatype">DATE</span> <span class="token keyword">INVISIBLE</span>
<span class="token punctuation">)</span> <span class="token keyword">ENGINE</span> <span class="token operator">=</span> InnoDB<span class="token punctuation">;</span>
<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">ADD</span> <span class="token keyword">COLUMN</span> k <span class="token datatype">INT</span> <span class="token keyword">INVISIBLE</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    To alter the visibility of an existing column, use a
    <code class="literal">
     VISIBLE
    </code>
    or
    <code class="literal">
     INVISIBLE
    </code>
    keyword with one of the
    <code class="literal">
     ALTER TABLE
    </code>
    column-modification clauses:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa19956830"><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">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">CHANGE</span> <span class="token keyword">COLUMN</span> j j <span class="token datatype">DATE</span> <span class="token keyword">VISIBLE</span><span class="token punctuation">;</span>
<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">MODIFY</span> <span class="token keyword">COLUMN</span> j <span class="token datatype">DATE</span> <span class="token keyword">INVISIBLE</span><span class="token punctuation">;</span>
<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">ALTER</span> <span class="token keyword">COLUMN</span> j <span class="token keyword">SET</span> <span class="token keyword">VISIBLE</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    A table must have at least one visible column. Attempting to
          make all columns invisible produces an error.
   </p>
   <p>
    Invisible columns support the usual column attributes:
    <code class="literal">
     NULL
    </code>
    ,
    <code class="literal">
     NOT NULL
    </code>
    ,
    <code class="literal">
     AUTO_INCREMENT
    </code>
    , and so forth.
   </p>
   <p>
    Generated columns can be invisible.
   </p>
   <p>
    Index definitions can name invisible columns, including
          definitions for
    <code class="literal">
     PRIMARY KEY
    </code>
    and
    <code class="literal">
     UNIQUE
    </code>
    indexes. Although a table must have
          at least one visible column, an index definition need not have
          any visible columns.
   </p>
   <p>
    An invisible column dropped from a table is dropped in the
          usual way from any index definition that names the column.
   </p>
   <p>
    Foreign key constraints can be defined on invisible columns,
          and foreign key constraints can reference invisible columns.
   </p>
   <p>
    <code class="literal">
     CHECK
    </code>
    constraints can be defined on
          invisible columns. For new or modified rows, violation of a
    <code class="literal">
     CHECK
    </code>
    constraint on an invisible column
          produces an error.
   </p>
   <p>
    <a class="link" href="create-table-like.html" title="15.1.20.3 CREATE TABLE ... LIKE Statement">
     <code class="literal">
      CREATE
          TABLE ... LIKE
     </code>
    </a>
    includes invisible columns, and they
          are invisible in the new table.
   </p>
   <p>
    <a class="link" href="create-table-select.html" title="15.1.20.4 CREATE TABLE ... SELECT Statement">
     <code class="literal">
      CREATE
          TABLE ... SELECT
     </code>
    </a>
    does not include invisible columns,
          unless they are explicitly referenced in the
    <a class="link" href="select.html" title="15.2.13 SELECT Statement">
     <code class="literal">
      SELECT
     </code>
    </a>
    part. However, even if
          explicitly referenced, a column that is invisible in the
          existing table is visible in the new table:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa44145903"><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> t1 <span class="token punctuation">(</span>col1 <span class="token datatype">INT</span><span class="token punctuation">,</span> col2 <span class="token datatype">INT</span> <span class="token keyword">INVISIBLE</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t2 <span class="token keyword">AS</span> <span class="token keyword">SELECT</span> col1<span class="token punctuation">,</span> col2 <span class="token keyword">FROM</span> t1<span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SHOW</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t2\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<span class="token punctuation">:</span> t2
Create Table<span class="token punctuation">:</span> CREATE TABLE `t2` (
  `col1` int DEFAULT NULL,
  `col2` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci</span></code></pre>
   </div>
   <p>
    If invisibility should be preserved, provide a definition for
          the invisible column in the
    <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
     <code class="literal">
      CREATE
          TABLE
     </code>
    </a>
    part of the
    <a class="link" href="create-table-select.html" title="15.1.20.4 CREATE TABLE ... SELECT Statement">
     <code class="literal">
      CREATE
          TABLE ... SELECT
     </code>
    </a>
    statement:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa90850421"><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> t1 <span class="token punctuation">(</span>col1 <span class="token datatype">INT</span><span class="token punctuation">,</span> col2 <span class="token datatype">INT</span> <span class="token keyword">INVISIBLE</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t2 <span class="token punctuation">(</span>col2 <span class="token datatype">INT</span> <span class="token keyword">INVISIBLE</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> <span class="token keyword">SELECT</span> col1<span class="token punctuation">,</span> col2 <span class="token keyword">FROM</span> t1<span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SHOW</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t2\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<span class="token punctuation">:</span> t2
Create Table<span class="token punctuation">:</span> CREATE TABLE `t2` (
  `col1` int DEFAULT NULL,
  `col2` int DEFAULT NULL /<span class="token punctuation">*</span>!80023 INVISIBLE <span class="token punctuation">*</span>/
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci</span></code></pre>
   </div>
   <p>
    Views can refer to invisible columns by explicitly referencing
          them in the
    <code class="literal">
     SELECT
    </code>
    statement that defines
          the view. Changing a column's visibility subsequent to
          defining a view that references the column does not change
          view behavior.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="invisible-column-dml-statements">
       </a>
       DML Statements and Invisible Columns
      </h5>
     </div>
    </div>
   </div>
   <p>
    For
    <a class="link" href="select.html" title="15.2.13 SELECT Statement">
     <code class="literal">
      SELECT
     </code>
    </a>
    statements, an
          invisible column is not part of the result set unless
          explicitly referenced in the select list. In a select list,
          the
    <code class="literal">
     *
    </code>
    and
    <code class="literal">
     <em class="replaceable">
      <code>
       tbl_name
      </code>
     </em>
     .*
    </code>
    shorthands do not include invisible columns. Natural joins do
          not include invisible columns.
   </p>
   <p>
    Consider the following statement sequence:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa86642639"><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> t1 <span class="token punctuation">(</span>col1 <span class="token datatype">INT</span><span class="token punctuation">,</span> col2 <span class="token datatype">INT</span> <span class="token keyword">INVISIBLE</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t1 <span class="token punctuation">(</span>col1<span class="token punctuation">,</span> col2<span class="token punctuation">)</span> <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">,</span> <span class="token number">4</span><span class="token punctuation">)</span><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> t1<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>
<span class="token output"><span class="token punctuation">|</span> col1 <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>
<span class="token output"><span class="token punctuation">|</span>    1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    3 <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>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> col1<span class="token punctuation">,</span> col2 <span class="token keyword">FROM</span> t1<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>
<span class="token output"><span class="token punctuation">|</span> col1 <span class="token punctuation">|</span> col2 <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>
<span class="token output"><span class="token punctuation">|</span>    1 <span class="token punctuation">|</span>    2 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>    3 <span class="token punctuation">|</span>    4 <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></code></pre>
   </div>
   <p>
    The first
    <code class="literal">
     SELECT
    </code>
    does not reference the
          invisible column
    <code class="literal">
     col2
    </code>
    in the select list
          (because
    <code class="literal">
     *
    </code>
    does not include invisible
          columns), so
    <code class="literal">
     col2
    </code>
    does not appear in the
          statement result. The second
    <code class="literal">
     SELECT
    </code>
    explicitly references
    <code class="literal">
     col2
    </code>
    , so the column
          appears in the result.
   </p>
   <p>
    The statement
    <a class="link" href="table.html" title="15.2.16 TABLE Statement">
     <code class="literal">
      TABLE
          t1
     </code>
    </a>
    produces the same output as the first
    <code class="literal">
     SELECT
    </code>
    statement. Since there is no way to
          specify columns in a
    <code class="literal">
     TABLE
    </code>
    statement,
    <code class="literal">
     TABLE
    </code>
    never displays invisible columns.
   </p>
   <p>
    For statements that create new rows, an invisible column is
          assigned its implicit default value unless explicitly
          referenced and assigned a value. For information about
          implicit defaults, see
    <a class="xref" href="data-type-defaults.html#data-type-defaults-implicit" title="Implicit Default Handling">
     Implicit Default Handling
    </a>
    .
   </p>
   <p>
    For
    <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
     <code class="literal">
      INSERT
     </code>
    </a>
    (and
    <a class="link" href="replace.html" title="15.2.12 REPLACE Statement">
     <code class="literal">
      REPLACE
     </code>
    </a>
    , for non-replaced
          rows), implicit default assignment occurs with a missing
          column list, an empty column list, or a nonempty column list
          that does not include the invisible column:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa44515554"><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> t1 <span class="token punctuation">(</span>col1 <span class="token datatype">INT</span><span class="token punctuation">,</span> col2 <span class="token datatype">INT</span> <span class="token keyword">INVISIBLE</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t1 <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t1 <span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t1 <span class="token punctuation">(</span>col1<span class="token punctuation">)</span> <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    For the first two
    <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
     <code class="literal">
      INSERT
     </code>
    </a>
    statements, the
    <code class="literal">
     VALUES()
    </code>
    list must provide
          a value for each visible column and no invisible column. For
          the third
    <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
     <code class="literal">
      INSERT
     </code>
    </a>
    statement, the
    <code class="literal">
     VALUES()
    </code>
    list must provide the same number
          of values as the number of named columns; the same is true
          when you use
    <a class="link" href="values.html" title="15.2.19 VALUES Statement">
     <code class="literal">
      VALUES
          ROW()
     </code>
    </a>
    rather than
    <code class="literal">
     VALUES()
    </code>
    .
   </p>
   <p>
    For
    <a class="link" href="load-data.html" title="15.2.9 LOAD DATA Statement">
     <code class="literal">
      LOAD DATA
     </code>
    </a>
    and
    <a class="link" href="load-xml.html" title="15.2.10 LOAD XML Statement">
     <code class="literal">
      LOAD XML
     </code>
    </a>
    , implicit default
          assignment occurs with a missing column list or a nonempty
          column list that does not include the invisible column. Input
          rows should not include a value for the invisible column.
   </p>
   <p>
    To assign a value other than the implicit default for the
          preceding statements, explicitly name the invisible column in
          the column list and provide a value for it.
   </p>
   <p>
    <a class="link" href="insert-select.html" title="15.2.7.1 INSERT ... SELECT Statement">
     <code class="literal">
      INSERT INTO ...
          SELECT *
     </code>
    </a>
    and
    <a class="link" href="replace.html" title="15.2.12 REPLACE Statement">
     <code class="literal">
      REPLACE INTO ...
          SELECT *
     </code>
    </a>
    do not include invisible columns because
    <code class="literal">
     *
    </code>
    does not include invisible columns.
          Implicit default assignment occurs as described previously.
   </p>
   <p>
    For statements that insert or ignore new rows, or that replace
          or modify existing rows, based on values in a
    <code class="literal">
     PRIMARY
          KEY
    </code>
    or
    <code class="literal">
     UNIQUE
    </code>
    index, MySQL treats
          invisible columns the same as visible columns: Invisible
          columns participate in key value comparisons. Specifically, if
          a new row has the same value as an existing row for a unique
          key value, these behaviors occur whether the index columns are
          visible or invisible:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       With the
       <code class="literal">
        IGNORE
       </code>
       modifier,
       <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
        <code class="literal">
         INSERT
        </code>
       </a>
       ,
       <a class="link" href="load-data.html" title="15.2.9 LOAD DATA Statement">
        <code class="literal">
         LOAD DATA
        </code>
       </a>
       , and
       <a class="link" href="load-xml.html" title="15.2.10 LOAD XML Statement">
        <code class="literal">
         LOAD XML
        </code>
       </a>
       ignore the new
              row.
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="link" href="replace.html" title="15.2.12 REPLACE Statement">
        <code class="literal">
         REPLACE
        </code>
       </a>
       replaces the
              existing row with the new row. With the
       <code class="literal">
        REPLACE
       </code>
       modifier,
       <a class="link" href="load-data.html" title="15.2.9 LOAD DATA Statement">
        <code class="literal">
         LOAD DATA
        </code>
       </a>
       and
       <a class="link" href="load-xml.html" title="15.2.10 LOAD XML Statement">
        <code class="literal">
         LOAD XML
        </code>
       </a>
       do the same.
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="link" href="insert-on-duplicate.html" title="15.2.7.2 INSERT ... ON DUPLICATE KEY UPDATE Statement">
        <code class="literal">
         INSERT
              ... ON DUPLICATE KEY UPDATE
        </code>
       </a>
       updates the existing
              row.
      </p>
     </li>
    </ul>
   </div>
   <p>
    To update invisible columns for
    <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
     <code class="literal">
      UPDATE
     </code>
    </a>
    statements, name them
          and assign a value, just as for visible columns.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="invisible-column-metadata">
       </a>
       Invisible Column Metadata
      </h5>
     </div>
    </div>
   </div>
   <a class="indexterm" name="idm46045184508656">
   </a>
   <a class="indexterm" name="idm46045184507168">
   </a>
   <p>
    Information about whether a column is visible or invisible is
          available from the
    <code class="literal">
     EXTRA
    </code>
    column of the
          Information Schema
    <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
    <a class="link" href="show-columns.html" title="15.7.7.6 SHOW COLUMNS Statement">
     <code class="literal">
      SHOW COLUMNS
     </code>
    </a>
    output. For
          example:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa9353432"><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 keyword">TABLE_NAME</span><span class="token punctuation">,</span> <span class="token keyword">COLUMN_NAME</span><span class="token punctuation">,</span> EXTRA
       <span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span><span class="token keyword">COLUMNS</span>
       <span class="token keyword">WHERE</span> TABLE_SCHEMA <span class="token operator">=</span> <span class="token string">'test'</span> <span class="token operator">AND</span> <span class="token keyword">TABLE_NAME</span> <span class="token operator">=</span> <span class="token string">'t1'</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>
<span class="token output"><span class="token punctuation">|</span> TABLE_NAME <span class="token punctuation">|</span> COLUMN_NAME <span class="token punctuation">|</span> EXTRA     <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>
<span class="token output"><span class="token punctuation">|</span> t1         <span class="token punctuation">|</span> i           <span class="token punctuation">|</span>           <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> t1         <span class="token punctuation">|</span> j           <span class="token punctuation">|</span>           <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> t1         <span class="token punctuation">|</span> k           <span class="token punctuation">|</span> INVISIBLE <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></code></pre>
   </div>
   <p>
    Columns are visible by default, so in that case,
    <code class="literal">
     EXTRA
    </code>
    displays no visibility information.
          For invisible columns,
    <code class="literal">
     EXTRA
    </code>
    displays
    <code class="literal">
     INVISIBLE
    </code>
    .
   </p>
   <p>
    <code class="literal">
     SHOW CREATE TABLE
    </code>
    displays invisible
          columns in the table definition, with the
    <code class="literal">
     INVISIBLE
    </code>
    keyword in a version-specific
          comment:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa69592236"><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">CREATE</span> <span class="token keyword">TABLE</span> t1\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<span class="token punctuation">:</span> t1
Create Table<span class="token punctuation">:</span> CREATE TABLE `t1` (
  `i` int DEFAULT NULL,
  `j` int DEFAULT NULL,
  `k` int DEFAULT NULL /<span class="token punctuation">*</span>!80023 INVISIBLE <span class="token punctuation">*</span>/
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci</span></code></pre>
   </div>
   <p>
    <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
     <span class="command">
      <strong>
       mysqldump
      </strong>
     </span>
    </a>
    uses
    <code class="literal">
     SHOW CREATE
          TABLE
    </code>
    , so they include invisible columns in dumped
          table definitions. They also include invisible column values
          in dumped data.
   </p>
   <p>
    Reloading a dump file into an older version of MySQL that does
          not support invisible columns causes the version-specific
          comment to be ignored, which creates any invisible columns as
          visible.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="invisible-column-binary-logging">
       </a>
       The Binary Log and Invisible Columns
      </h5>
     </div>
    </div>
   </div>
   <a class="indexterm" name="idm46045184487632">
   </a>
   <a class="indexterm" name="idm46045184486144">
   </a>
   <p>
    MySQL treats invisible columns as follows with respect to
          events in the binary log:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       Table-creation events include the
       <code class="literal">
        INVISIBLE
       </code>
       attribute for invisible
              columns.
      </p>
     </li>
     <li class="listitem">
      <p>
       Invisible columns are treated like visible columns in row
              events. They are included if needed according to the
       <a class="link" href="replication-options-binary-log.html#sysvar_binlog_row_image">
        <code class="literal">
         binlog_row_image
        </code>
       </a>
       system
              variable setting.
      </p>
     </li>
     <li class="listitem">
      <p>
       When row events are applied, invisible columns are treated
              like visible columns in row events.
      </p>
     </li>
     <li class="listitem">
      <p>
       Invisible columns are treated like visible columns when
              computing writesets. In particular, writesets include
              indexes defined on invisible columns.
      </p>
     </li>
     <li class="listitem">
      <p>
       The
       <a class="link" href="mysqlbinlog.html" title="6.6.9 mysqlbinlog — Utility for Processing Binary Log Files">
        <span class="command">
         <strong>
          mysqlbinlog
         </strong>
        </span>
       </a>
       command includes
              visibility in column metadata.
      </p>
     </li>
    </ul>
   </div>
  </div>
 </div>
 <br/>
</div>
