<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h2 class="title">
      <a name="merge-storage-engine">
      </a>
      18.7 The MERGE Storage Engine
     </h2>
    </div>
   </div>
  </div>
  <div class="toc">
   <dl class="toc">
    <dt>
     <span class="section">
      <a href="merge-table-advantages.html">
       18.7.1 MERGE Table Advantages and Disadvantages
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="merge-table-problems.html">
       18.7.2 MERGE Table Problems
      </a>
     </span>
    </dt>
   </dl>
  </div>
  <a class="indexterm" name="idm46045147492704">
  </a>
  <a class="indexterm" name="idm46045147491664">
  </a>
  <a class="indexterm" name="idm46045147490176">
  </a>
  <a class="indexterm" name="idm46045147488688">
  </a>
  <a class="indexterm" name="idm46045147487200">
  </a>
  <p>
   The
   <code class="literal">
    MERGE
   </code>
   storage engine, also known as the
   <code class="literal">
    MRG_MyISAM
   </code>
   engine, is a collection of identical
   <code class="literal">
    MyISAM
   </code>
   tables that can be used as one.
   <span class="quote">
    “
    <span class="quote">
     Identical
    </span>
    ”
   </span>
   means that all tables have identical column
    data types and index information. You cannot merge
   <code class="literal">
    MyISAM
   </code>
   tables in which the columns are listed in
    a different order, do not have exactly the same data types in
    corresponding columns, or have the indexes in different order.
    However, any or all of the
   <code class="literal">
    MyISAM
   </code>
   tables can be
    compressed with
   <a class="link" href="myisampack.html" title="6.6.6 myisampack — Generate Compressed, Read-Only MyISAM Tables">
    <span class="command">
     <strong>
      myisampack
     </strong>
    </span>
   </a>
   . See
   <a class="xref" href="myisampack.html" title="6.6.6 myisampack — Generate Compressed, Read-Only MyISAM Tables">
    Section 6.6.6, “myisampack — Generate Compressed, Read-Only MyISAM Tables”
   </a>
   . Differences between tables such as
    these do not matter:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Names of corresponding columns and indexes can differ.
     </p>
    </li>
    <li class="listitem">
     <p>
      Comments for tables, columns, and indexes can differ.
     </p>
    </li>
    <li class="listitem">
     <p>
      Table options such as
      <code class="literal">
       AVG_ROW_LENGTH
      </code>
      ,
      <code class="literal">
       MAX_ROWS
      </code>
      , or
      <code class="literal">
       PACK_KEYS
      </code>
      can
        differ.
     </p>
    </li>
   </ul>
  </div>
  <p>
   An alternative to a
   <code class="literal">
    MERGE
   </code>
   table is a partitioned
    table, which stores partitions of a single table in separate files
    and enables some operations to be performed more efficiently. For
    more information, see
   <a class="xref" href="partitioning.html" title="Chapter 26 Partitioning">
    Chapter 26,
    <i>
     Partitioning
    </i>
   </a>
   .
  </p>
  <p>
   When you create a
   <code class="literal">
    MERGE
   </code>
   table, MySQL creates a
   <code class="filename">
    .MRG
   </code>
   file on disk that contains the names of
    the underlying
   <code class="literal">
    MyISAM
   </code>
   tables that should be used
    as one. The table format of the
   <code class="literal">
    MERGE
   </code>
   table is
    stored in the MySQL data dictionary. The underlying tables do not
    have to be in the same database as the
   <code class="literal">
    MERGE
   </code>
   table.
  </p>
  <p>
   You can use
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   ,
   <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
    <code class="literal">
     DELETE
    </code>
   </a>
   ,
   <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
    <code class="literal">
     UPDATE
    </code>
   </a>
   , and
   <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
    <code class="literal">
     INSERT
    </code>
   </a>
   on
   <code class="literal">
    MERGE
   </code>
   tables. You must have
   <a class="link" href="privileges-provided.html#priv_select">
    <code class="literal">
     SELECT
    </code>
   </a>
   ,
   <a class="link" href="privileges-provided.html#priv_delete">
    <code class="literal">
     DELETE
    </code>
   </a>
   , and
   <a class="link" href="privileges-provided.html#priv_update">
    <code class="literal">
     UPDATE
    </code>
   </a>
   privileges on the
   <code class="literal">
    MyISAM
   </code>
   tables that you map to a
   <code class="literal">
    MERGE
   </code>
   table.
  </p>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    The use of
    <code class="literal">
     MERGE
    </code>
    tables entails the following
      security issue: If a user has access to
    <code class="literal">
     MyISAM
    </code>
    table
    <em class="replaceable">
     <code>
      t
     </code>
    </em>
    , that user can create a
    <code class="literal">
     MERGE
    </code>
    table
    <em class="replaceable">
     <code>
      m
     </code>
    </em>
    that
      accesses
    <em class="replaceable">
     <code>
      t
     </code>
    </em>
    . However, if the user's
      privileges on
    <em class="replaceable">
     <code>
      t
     </code>
    </em>
    are subsequently
      revoked, the user can continue to access
    <em class="replaceable">
     <code>
      t
     </code>
    </em>
    by doing so through
    <em class="replaceable">
     <code>
      m
     </code>
    </em>
    .
   </p>
  </div>
  <p>
   Use of
   <a class="link" href="drop-table.html" title="15.1.32 DROP TABLE Statement">
    <code class="literal">
     DROP TABLE
    </code>
   </a>
   with a
   <code class="literal">
    MERGE
   </code>
   table drops only the
   <code class="literal">
    MERGE
   </code>
   specification. The underlying tables are
    not affected.
  </p>
  <p>
   To create a
   <code class="literal">
    MERGE
   </code>
   table, you must specify a
   <code class="literal">
    UNION=(
    <em class="replaceable">
     <code>
      list-of-tables
     </code>
    </em>
    )
   </code>
   option that indicates which
   <code class="literal">
    MyISAM
   </code>
   tables to use.
    You can optionally specify an
   <code class="literal">
    INSERT_METHOD
   </code>
   option to control how inserts into the
   <code class="literal">
    MERGE
   </code>
   table take place. Use a value of
   <code class="literal">
    FIRST
   </code>
   or
   <code class="literal">
    LAST
   </code>
   to cause inserts to be made in the first or
    last underlying table, respectively. If you specify no
   <code class="literal">
    INSERT_METHOD
   </code>
   option or if you specify it with a
    value of
   <code class="literal">
    NO
   </code>
   , inserts into the
   <code class="literal">
    MERGE
   </code>
   table are not permitted and attempts to do
    so result in an error.
  </p>
  <p>
   The following example shows how to create a
   <code class="literal">
    MERGE
   </code>
   table:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa6833634"><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>
<span class="token prompt">    -&gt;</span>    a <span class="token datatype">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">AUTO_INCREMENT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>    message <span class="token datatype">CHAR</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token keyword">ENGINE</span><span class="token operator">=</span>MyISAM<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>
<span class="token prompt">    -&gt;</span>    a <span class="token datatype">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">AUTO_INCREMENT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>    message <span class="token datatype">CHAR</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token keyword">ENGINE</span><span class="token operator">=</span>MyISAM<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>message<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'Testing'</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token string">'table'</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token string">'t1'</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> t2 <span class="token punctuation">(</span>message<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'Testing'</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token string">'table'</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token string">'t2'</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> total <span class="token punctuation">(</span>
<span class="token prompt">    -&gt;</span>    a <span class="token datatype">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>    message <span class="token datatype">CHAR</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token keyword">INDEX</span><span class="token punctuation">(</span>a<span class="token punctuation">)</span><span class="token punctuation">)</span>
<span class="token prompt">    -&gt;</span>    <span class="token keyword">ENGINE</span><span class="token operator">=</span><span class="token keyword">MERGE</span> <span class="token keyword">UNION</span><span class="token operator">=</span><span class="token punctuation">(</span>t1<span class="token punctuation">,</span>t2<span class="token punctuation">)</span> <span class="token keyword">INSERT_METHOD</span><span class="token operator">=</span><span class="token keyword">LAST</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Column
   <code class="literal">
    a
   </code>
   is indexed as a
   <code class="literal">
    PRIMARY
    KEY
   </code>
   in the underlying
   <code class="literal">
    MyISAM
   </code>
   tables,
    but not in the
   <code class="literal">
    MERGE
   </code>
   table. There it is indexed
    but not as a
   <code class="literal">
    PRIMARY KEY
   </code>
   because a
   <code class="literal">
    MERGE
   </code>
   table cannot enforce uniqueness over the
    set of underlying tables. (Similarly, a column with a
   <code class="literal">
    UNIQUE
   </code>
   index in the underlying tables should be
    indexed in the
   <code class="literal">
    MERGE
   </code>
   table but not as a
   <code class="literal">
    UNIQUE
   </code>
   index.)
  </p>
  <p>
   After creating the
   <code class="literal">
    MERGE
   </code>
   table, you can use it to
    issue queries that operate on the group of tables as a whole:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa35503054"><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> total<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> a <span class="token punctuation">|</span> message <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> Testing <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2 <span class="token punctuation">|</span> table   <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 3 <span class="token punctuation">|</span> t1      <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 1 <span class="token punctuation">|</span> Testing <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2 <span class="token punctuation">|</span> table   <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 3 <span class="token punctuation">|</span> t2      <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>
   To remap a
   <code class="literal">
    MERGE
   </code>
   table to a different collection
    of
   <code class="literal">
    MyISAM
   </code>
   tables, you can use one of the
    following methods:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       DROP
      </code>
      the
      <code class="literal">
       MERGE
      </code>
      table and
        re-create it.
     </p>
    </li>
    <li class="listitem">
     <p>
      Use
      <code class="literal">
       ALTER TABLE
       <em class="replaceable">
        <code>
         tbl_name
        </code>
       </em>
       UNION=(...)
      </code>
      to change the list of underlying tables.
     </p>
     <p>
      It is also possible to use
      <code class="literal">
       ALTER TABLE ...
        UNION=()
      </code>
      (that is, with an empty
      <a class="link" href="union.html" title="15.2.18 UNION Clause">
       <code class="literal">
        UNION
       </code>
      </a>
      clause) to remove all of
        the underlying tables. However, in this case, the table is
        effectively empty and inserts fail because there is no
        underlying table to take new rows. Such a table might be useful
        as a template for creating new
      <code class="literal">
       MERGE
      </code>
      tables
        with
      <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>
      .
     </p>
    </li>
   </ul>
  </div>
  <p>
   The underlying table definitions and indexes must conform closely to
    the definition of the
   <code class="literal">
    MERGE
   </code>
   table. Conformance is
    checked when a table that is part of a
   <code class="literal">
    MERGE
   </code>
   table is opened, not when the
   <code class="literal">
    MERGE
   </code>
   table is
    created. If any table fails the conformance checks, the operation
    that triggered the opening of the table fails. This means that
    changes to the definitions of tables within a
   <code class="literal">
    MERGE
   </code>
   may cause a failure when the
   <code class="literal">
    MERGE
   </code>
   table is accessed. The conformance checks
    applied to each table are:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      The underlying table and the
      <code class="literal">
       MERGE
      </code>
      table must
        have the same number of columns.
     </p>
    </li>
    <li class="listitem">
     <p>
      The column order in the underlying table and the
      <code class="literal">
       MERGE
      </code>
      table must match.
     </p>
    </li>
    <li class="listitem">
     <p>
      Additionally, the specification for each corresponding column in
        the parent
      <code class="literal">
       MERGE
      </code>
      table and the underlying
        tables are compared and must satisfy these checks:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         The column type in the underlying table and the
         <code class="literal">
          MERGE
         </code>
         table must be equal.
        </p>
       </li>
       <li class="listitem">
        <p>
         The column length in the underlying table and the
         <code class="literal">
          MERGE
         </code>
         table must be equal.
        </p>
       </li>
       <li class="listitem">
        <p>
         The column of the underlying table and the
         <code class="literal">
          MERGE
         </code>
         table can be
         <code class="literal">
          NULL
         </code>
         .
        </p>
       </li>
      </ul>
     </div>
    </li>
    <li class="listitem">
     <p>
      The underlying table must have at least as many indexes as the
      <code class="literal">
       MERGE
      </code>
      table. The underlying table may have
        more indexes than the
      <code class="literal">
       MERGE
      </code>
      table, but cannot
        have fewer.
     </p>
     <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
      <div class="admon-title">
       Note
      </div>
      <p>
       A known issue exists where indexes on the same columns must be
          in identical order, in both the
       <code class="literal">
        MERGE
       </code>
       table
          and the underlying
       <code class="literal">
        MyISAM
       </code>
       table. See Bug
          #33653.
      </p>
     </div>
     <p>
      Each index must satisfy these checks:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         The index type of the underlying table and the
         <code class="literal">
          MERGE
         </code>
         table must be the same.
        </p>
       </li>
       <li class="listitem">
        <p>
         The number of index parts (that is, multiple columns within
            a compound index) in the index definition for the underlying
            table and the
         <code class="literal">
          MERGE
         </code>
         table must be the
            same.
        </p>
       </li>
       <li class="listitem">
        <p>
         For each index part:
        </p>
        <div class="itemizedlist">
         <ul class="itemizedlist" style="list-style-type: square; ">
          <li class="listitem">
           <p>
            Index part lengths must be equal.
           </p>
          </li>
          <li class="listitem">
           <p>
            Index part types must be equal.
           </p>
          </li>
          <li class="listitem">
           <p>
            Index part languages must be equal.
           </p>
          </li>
          <li class="listitem">
           <p>
            Check whether index parts can be
            <code class="literal">
             NULL
            </code>
            .
           </p>
          </li>
         </ul>
        </div>
       </li>
      </ul>
     </div>
    </li>
   </ul>
  </div>
  <p>
   If a
   <code class="literal">
    MERGE
   </code>
   table cannot be opened or used because
    of a problem with an underlying table,
   <a class="link" href="check-table.html" title="15.7.3.2 CHECK TABLE Statement">
    <code class="literal">
     CHECK
    TABLE
    </code>
   </a>
   displays information about which table caused the
    problem.
  </p>
  <h3>
   <a name="idm46045147371600">
   </a>
   Additional Resources
  </h3>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      A forum dedicated to the
      <code class="literal">
       MERGE
      </code>
      storage engine
        is available at
      <a class="ulink" href="https://forums.mysql.com/list.php?93" target="_blank">
       https://forums.mysql.com/list.php?93
      </a>
      .
     </p>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
