<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="partitioning-management-range-list">
      </a>
      26.3.1 Management of RANGE and LIST Partitions
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045083924240">
  </a>
  <a class="indexterm" name="idm46045083922752">
  </a>
  <a class="indexterm" name="idm46045083921264">
  </a>
  <a class="indexterm" name="idm46045083919776">
  </a>
  <p>
   Adding and dropping of range and list partitions are handled in
        a similar fashion, so we discuss the management of both sorts of
        partitioning in this section. For information about working with
        tables that are partitioned by hash or key, see
   <a class="xref" href="partitioning-management-hash-key.html" title="26.3.2 Management of HASH and KEY Partitions">
    Section 26.3.2, “Management of HASH and KEY Partitions”
   </a>
   .
  </p>
  <p>
   Dropping a partition from a table that is partitioned by either
   <code class="literal">
    RANGE
   </code>
   or by
   <code class="literal">
    LIST
   </code>
   can be
        accomplished using the
   <a class="link" href="alter-table-partition-operations.html" title="15.1.9.1 ALTER TABLE Partition Operations">
    <code class="literal">
     ALTER
        TABLE
    </code>
   </a>
   statement with the
   <code class="literal">
    DROP
        PARTITION
   </code>
   option. Suppose that you have created a
        table that is partitioned by range and then populated with 10
        records using the following
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE
        TABLE
    </code>
   </a>
   and
   <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
    <code class="literal">
     INSERT
    </code>
   </a>
   statements:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa27349871"><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> tr <span class="token punctuation">(</span>id <span class="token datatype">INT</span><span class="token punctuation">,</span> <span class="token keyword">name</span> <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">50</span><span class="token punctuation">)</span><span class="token punctuation">,</span> purchased <span class="token datatype">DATE</span><span class="token punctuation">)</span>
<span class="token prompt">    -&gt;</span>     <span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> <span class="token keyword">RANGE</span><span class="token punctuation">(</span> <span class="token function">YEAR</span><span class="token punctuation">(</span>purchased<span class="token punctuation">)</span> <span class="token punctuation">)</span> <span class="token punctuation">(</span>
<span class="token prompt">    -&gt;</span>         <span class="token keyword">PARTITION</span> p0 <span class="token keyword">VALUES</span> <span class="token keyword">LESS</span> <span class="token keyword">THAN</span> <span class="token punctuation">(</span><span class="token number">1990</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>         <span class="token keyword">PARTITION</span> p1 <span class="token keyword">VALUES</span> <span class="token keyword">LESS</span> <span class="token keyword">THAN</span> <span class="token punctuation">(</span><span class="token number">1995</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>         <span class="token keyword">PARTITION</span> p2 <span class="token keyword">VALUES</span> <span class="token keyword">LESS</span> <span class="token keyword">THAN</span> <span class="token punctuation">(</span><span class="token number">2000</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>         <span class="token keyword">PARTITION</span> p3 <span class="token keyword">VALUES</span> <span class="token keyword">LESS</span> <span class="token keyword">THAN</span> <span class="token punctuation">(</span><span class="token number">2005</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>         <span class="token keyword">PARTITION</span> p4 <span class="token keyword">VALUES</span> <span class="token keyword">LESS</span> <span class="token keyword">THAN</span> <span class="token punctuation">(</span><span class="token number">2010</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>         <span class="token keyword">PARTITION</span> p5 <span class="token keyword">VALUES</span> <span class="token keyword">LESS</span> <span class="token keyword">THAN</span> <span class="token punctuation">(</span><span class="token number">2015</span><span class="token punctuation">)</span>
<span class="token prompt">    -&gt;</span>     <span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.28 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> tr <span class="token keyword">VALUES</span>
<span class="token prompt">    -&gt;</span>     <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token string">'desk organiser'</span><span class="token punctuation">,</span> <span class="token string">'2003-10-15'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>     <span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span> <span class="token string">'alarm clock'</span><span class="token punctuation">,</span> <span class="token string">'1997-11-05'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>     <span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">,</span> <span class="token string">'chair'</span><span class="token punctuation">,</span> <span class="token string">'2009-03-10'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>     <span class="token punctuation">(</span><span class="token number">4</span><span class="token punctuation">,</span> <span class="token string">'bookcase'</span><span class="token punctuation">,</span> <span class="token string">'1989-01-10'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>     <span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">,</span> <span class="token string">'exercise bike'</span><span class="token punctuation">,</span> <span class="token string">'2014-05-09'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>     <span class="token punctuation">(</span><span class="token number">6</span><span class="token punctuation">,</span> <span class="token string">'sofa'</span><span class="token punctuation">,</span> <span class="token string">'1987-06-05'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>     <span class="token punctuation">(</span><span class="token number">7</span><span class="token punctuation">,</span> <span class="token string">'espresso maker'</span><span class="token punctuation">,</span> <span class="token string">'2011-11-22'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>     <span class="token punctuation">(</span><span class="token number">8</span><span class="token punctuation">,</span> <span class="token string">'aquarium'</span><span class="token punctuation">,</span> <span class="token string">'1992-08-04'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>     <span class="token punctuation">(</span><span class="token number">9</span><span class="token punctuation">,</span> <span class="token string">'study desk'</span><span class="token punctuation">,</span> <span class="token string">'2006-09-16'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>     <span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">,</span> <span class="token string">'lava lamp'</span><span class="token punctuation">,</span> <span class="token string">'1998-12-25'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 10 rows affected (0.05 sec)</span>
<span class="token output">Records: 10  Duplicates: 0  Warnings: 0</span></code></pre>
  </div>
  <p>
   You can see which items should have been inserted into partition
   <code class="literal">
    p2
   </code>
   as shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa93551934"><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> tr
<span class="token prompt">    -&gt;</span>     <span class="token keyword">WHERE</span> purchased <span class="token operator">BETWEEN</span> <span class="token string">'1995-01-01'</span> <span class="token operator">AND</span> <span class="token string">'1999-12-31'</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>
<span class="token output"><span class="token punctuation">|</span> id   <span class="token punctuation">|</span> name        <span class="token punctuation">|</span> purchased  <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>
<span class="token output"><span class="token punctuation">|</span>    2 <span class="token punctuation">|</span> alarm clock <span class="token punctuation">|</span> 1997<span class="token punctuation">-</span>11<span class="token punctuation">-</span>05 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>   10 <span class="token punctuation">|</span> lava lamp   <span class="token punctuation">|</span> 1998<span class="token punctuation">-</span>12<span class="token punctuation">-</span>25 <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>
<span class="token output">2 rows in set (0.00 sec)</span></code></pre>
  </div>
  <p>
   You can also get this information using partition selection, as
        shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa64688815"><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> tr <span class="token keyword">PARTITION</span> <span class="token punctuation">(</span>p2<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 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> id   <span class="token punctuation">|</span> name        <span class="token punctuation">|</span> purchased  <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>
<span class="token output"><span class="token punctuation">|</span>    2 <span class="token punctuation">|</span> alarm clock <span class="token punctuation">|</span> 1997<span class="token punctuation">-</span>11<span class="token punctuation">-</span>05 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>   10 <span class="token punctuation">|</span> lava lamp   <span class="token punctuation">|</span> 1998<span class="token punctuation">-</span>12<span class="token punctuation">-</span>25 <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>
<span class="token output">2 rows in set (0.00 sec)</span></code></pre>
  </div>
  <p>
   See
   <a class="xref" href="partitioning-selection.html" title="26.5 Partition Selection">
    Section 26.5, “Partition Selection”
   </a>
   , for more
        information.
  </p>
  <p>
   To drop the partition named
   <code class="literal">
    p2
   </code>
   , execute the
        following command:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa61940382"><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">ALTER</span> <span class="token keyword">TABLE</span> tr <span class="token keyword">DROP</span> <span class="token keyword">PARTITION</span> p2<span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.03 sec)</span></code></pre>
  </div>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    The
    <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
     <code class="literal">
      NDBCLUSTER
     </code>
    </a>
    storage engine
          does not support
    <code class="literal">
     ALTER TABLE ... DROP
          PARTITION
    </code>
    . It does, however, support the other
          partitioning-related extensions to
    <a class="link" href="alter-table-partition-operations.html" title="15.1.9.1 ALTER TABLE Partition Operations">
     <code class="literal">
      ALTER
          TABLE
     </code>
    </a>
    that are described in this chapter.
   </p>
  </div>
  <p>
   It is very important to remember that,
   <span class="emphasis">
    <em>
     when you drop a
        partition, you also delete all the data that was stored in that
        partition
    </em>
   </span>
   . You can see that this is the case by
        re-running the previous
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   query:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa94436620"><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> tr <span class="token keyword">WHERE</span> purchased
<span class="token prompt">    -&gt;</span> <span class="token operator">BETWEEN</span> <span class="token string">'1995-01-01'</span> <span class="token operator">AND</span> <span class="token string">'1999-12-31'</span><span class="token punctuation">;</span>
<span class="token output">Empty set (0.00 sec)</span></code></pre>
  </div>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    <code class="literal">
     DROP PARTITION
    </code>
    is supported by native
          partitioning in-place APIs and may be used with
    <code class="literal">
     ALGORITHM={COPY|INPLACE}
    </code>
    .
    <code class="literal">
     DROP
          PARTITION
    </code>
    with
    <code class="literal">
     ALGORITHM=INPLACE
    </code>
    deletes data stored in the partition and drops the partition.
          However,
    <code class="literal">
     DROP PARTITION
    </code>
    with
    <code class="literal">
     ALGORITHM=COPY
    </code>
    or
    <a class="link" href="server-system-variables.html#sysvar_old_alter_table">
     <code class="literal">
      old_alter_table=ON
     </code>
    </a>
    rebuilds
          the partitioned table and attempts to move data from the
          dropped partition to another partition with a compatible
    <code class="literal">
     PARTITION ... VALUES
    </code>
    definition. Data that
          cannot be moved to another partition is deleted.
   </p>
  </div>
  <p>
   Because of this, you must have the
   <a class="link" href="privileges-provided.html#priv_drop">
    <code class="literal">
     DROP
    </code>
   </a>
   privilege for a table before
        you can execute
   <code class="literal">
    ALTER TABLE ... DROP
        PARTITION
   </code>
   on that table.
  </p>
  <p>
   If you wish to drop all data from all partitions while
        preserving the table definition and its partitioning scheme, use
        the
   <a class="link" href="truncate-table.html" title="15.1.37 TRUNCATE TABLE Statement">
    <code class="literal">
     TRUNCATE TABLE
    </code>
   </a>
   statement.
        (See
   <a class="xref" href="truncate-table.html" title="15.1.37 TRUNCATE TABLE Statement">
    Section 15.1.37, “TRUNCATE TABLE Statement”
   </a>
   .)
  </p>
  <p>
   If you intend to change the partitioning of a table
   <span class="emphasis">
    <em>
     without
    </em>
   </span>
   losing data, use
   <code class="literal">
    ALTER
        TABLE ... REORGANIZE PARTITION
   </code>
   instead. See below or
        in
   <a class="xref" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    Section 15.1.9, “ALTER TABLE Statement”
   </a>
   , for information about
   <code class="literal">
    REORGANIZE PARTITION
   </code>
   .
  </p>
  <p>
   If you now execute a
   <a class="link" href="show-create-table.html" title="15.7.7.11 SHOW CREATE TABLE Statement">
    <code class="literal">
     SHOW CREATE
        TABLE
    </code>
   </a>
   statement, you can see how the partitioning
        makeup of the table has been changed:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa38416029"><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> tr\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> tr
Create Table<span class="token punctuation">:</span> CREATE TABLE `tr` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `purchased` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/<span class="token punctuation">*</span>!50100 PARTITION BY RANGE ( YEAR(purchased))
(PARTITION p0 VALUES LESS THAN (1990) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (1995) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (2005) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (2010) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN (2015) ENGINE = InnoDB) <span class="token punctuation">*</span>/
</span><span class="token output">1 row in set (0.00 sec)</span></code></pre>
  </div>
  <p>
   When you insert new rows into the changed table with
   <code class="literal">
    purchased
   </code>
   column values between
   <code class="literal">
    '1995-01-01'
   </code>
   and
   <code class="literal">
    '2004-12-31'
   </code>
   inclusive, those rows are stored
        in partition
   <code class="literal">
    p3
   </code>
   . You can verify this as
        follows:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa59775079"><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">INSERT</span> <span class="token keyword">INTO</span> tr <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">11</span><span class="token punctuation">,</span> <span class="token string">'pencil holder'</span><span class="token punctuation">,</span> <span class="token string">'1995-07-12'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 1 row affected (0.00 sec)</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> tr <span class="token keyword">WHERE</span> purchased
<span class="token prompt">    -&gt;</span> <span class="token operator">BETWEEN</span> <span class="token string">'1995-01-01'</span> <span class="token operator">AND</span> <span class="token string">'2004-12-31'</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>
<span class="token output"><span class="token punctuation">|</span> id   <span class="token punctuation">|</span> name           <span class="token punctuation">|</span> purchased  <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>
<span class="token output"><span class="token punctuation">|</span>    1 <span class="token punctuation">|</span> desk organiser <span class="token punctuation">|</span> 2003<span class="token punctuation">-</span>10<span class="token punctuation">-</span>15 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>   11 <span class="token punctuation">|</span> pencil holder  <span class="token punctuation">|</span> 1995<span class="token punctuation">-</span>07<span class="token punctuation">-</span>12 <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>
<span class="token output">2 rows in set (0.00 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> tr <span class="token keyword">DROP</span> <span class="token keyword">PARTITION</span> p3<span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.03 sec)</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> tr <span class="token keyword">WHERE</span> purchased
<span class="token prompt">    -&gt;</span> <span class="token operator">BETWEEN</span> <span class="token string">'1995-01-01'</span> <span class="token operator">AND</span> <span class="token string">'2004-12-31'</span><span class="token punctuation">;</span>
<span class="token output">Empty set (0.00 sec)</span></code></pre>
  </div>
  <p>
   The number of rows dropped from the table as a result of
   <code class="literal">
    ALTER TABLE ... DROP PARTITION
   </code>
   is not
        reported by the server as it would be by the equivalent
   <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
    <code class="literal">
     DELETE
    </code>
   </a>
   query.
  </p>
  <p>
   Dropping
   <code class="literal">
    LIST
   </code>
   partitions uses exactly the
        same
   <code class="literal">
    ALTER TABLE ... DROP PARTITION
   </code>
   syntax as
        used for dropping
   <code class="literal">
    RANGE
   </code>
   partitions. However,
        there is one important difference in the effect this has on your
        use of the table afterward: You can no longer insert into the
        table any rows having any of the values that were included in
        the value list defining the deleted partition. (See
   <a class="xref" href="partitioning-list.html" title="26.2.2 LIST Partitioning">
    Section 26.2.2, “LIST Partitioning”
   </a>
   , for an example.)
  </p>
  <p>
   To add a new range or list partition to a previously partitioned
        table, use the
   <code class="literal">
    ALTER TABLE ... ADD PARTITION
   </code>
   statement. For tables which are partitioned by
   <code class="literal">
    RANGE
   </code>
   , this can be used to add a new range to
        the end of the list of existing partitions. Suppose that you
        have a partitioned table containing membership data for your
        organization, which is defined as follows:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa17073534"><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> members <span class="token punctuation">(</span>
    id <span class="token datatype">INT</span><span class="token punctuation">,</span>
    fname <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">25</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    lname <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">25</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    dob <span class="token datatype">DATE</span>
<span class="token punctuation">)</span>
<span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> <span class="token keyword">RANGE</span><span class="token punctuation">(</span> <span class="token function">YEAR</span><span class="token punctuation">(</span>dob<span class="token punctuation">)</span> <span class="token punctuation">)</span> <span class="token punctuation">(</span>
    <span class="token keyword">PARTITION</span> p0 <span class="token keyword">VALUES</span> <span class="token keyword">LESS</span> <span class="token keyword">THAN</span> <span class="token punctuation">(</span><span class="token number">1980</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">PARTITION</span> p1 <span class="token keyword">VALUES</span> <span class="token keyword">LESS</span> <span class="token keyword">THAN</span> <span class="token punctuation">(</span><span class="token number">1990</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">PARTITION</span> p2 <span class="token keyword">VALUES</span> <span class="token keyword">LESS</span> <span class="token keyword">THAN</span> <span class="token punctuation">(</span><span class="token number">2000</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Suppose further that the minimum age for members is 16. As the
        calendar approaches the end of 2015, you realize that you must
        soon be prepared to admit members who were born in 2000 (and
        later). You can modify the
   <code class="literal">
    members
   </code>
   table to
        accommodate new members born in the years 2000 to 2010 as shown
        here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa98858957"><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> members <span class="token keyword">ADD</span> <span class="token keyword">PARTITION</span> <span class="token punctuation">(</span><span class="token keyword">PARTITION</span> p3 <span class="token keyword">VALUES</span> <span class="token keyword">LESS</span> <span class="token keyword">THAN</span> <span class="token punctuation">(</span><span class="token number">2010</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   With tables that are partitioned by range, you can use
   <code class="literal">
    ADD PARTITION
   </code>
   to add new partitions to the
        high end of the partitions list only. Trying to add a new
        partition in this manner between or before existing partitions
        results in an error as shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa69916575"><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">ALTER</span> <span class="token keyword">TABLE</span> members
     <span class="token operator">&gt;</span>     <span class="token keyword">ADD</span> <span class="token keyword">PARTITION</span> <span class="token punctuation">(</span>
     <span class="token operator">&gt;</span>     <span class="token keyword">PARTITION</span> n <span class="token keyword">VALUES</span> <span class="token keyword">LESS</span> <span class="token keyword">THAN</span> <span class="token punctuation">(</span><span class="token number">1970</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">ERROR 1463 (HY000)<span class="token punctuation">:</span> VALUES LESS THAN value must be strictly »
   increasing for each partition</span></code></pre>
  </div>
  <p>
   You can work around this problem by reorganizing the first
        partition into two new ones that split the range between them,
        like this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa45467189"><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> members
    <span class="token keyword">REORGANIZE</span> <span class="token keyword">PARTITION</span> p0 <span class="token keyword">INTO</span> <span class="token punctuation">(</span>
        <span class="token keyword">PARTITION</span> n0 <span class="token keyword">VALUES</span> <span class="token keyword">LESS</span> <span class="token keyword">THAN</span> <span class="token punctuation">(</span><span class="token number">1970</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
        <span class="token keyword">PARTITION</span> n1 <span class="token keyword">VALUES</span> <span class="token keyword">LESS</span> <span class="token keyword">THAN</span> <span class="token punctuation">(</span><span class="token number">1980</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Using
   <a class="link" href="show-create-table.html" title="15.7.7.11 SHOW CREATE TABLE Statement">
    <code class="literal">
     SHOW CREATE TABLE
    </code>
   </a>
   you can
        see that the
   <code class="literal">
    ALTER TABLE
   </code>
   statement has had
        the desired effect:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa28617008"><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> members\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> members
Create Table<span class="token punctuation">:</span> CREATE TABLE `members` (
  `id` int(11) DEFAULT NULL,
  `fname` varchar(25) DEFAULT NULL,
  `lname` varchar(25) DEFAULT NULL,
  `dob` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/<span class="token punctuation">*</span>!50100 PARTITION BY RANGE ( YEAR(dob))
(PARTITION n0 VALUES LESS THAN (1970) ENGINE = InnoDB,
 PARTITION n1 VALUES LESS THAN (1980) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (1990) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (2010) ENGINE = InnoDB) <span class="token punctuation">*</span>/
</span><span class="token output">1 row in set (0.00 sec)</span></code></pre>
  </div>
  <p>
   See also
   <a class="xref" href="alter-table-partition-operations.html" title="15.1.9.1 ALTER TABLE Partition Operations">
    Section 15.1.9.1, “ALTER TABLE Partition Operations”
   </a>
   .
  </p>
  <p>
   You can also use
   <code class="literal">
    ALTER TABLE ... ADD
        PARTITION
   </code>
   to add new partitions to a table that is
        partitioned by
   <code class="literal">
    LIST
   </code>
   . Suppose a table
   <code class="literal">
    tt
   </code>
   is defined using the following
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE TABLE
    </code>
   </a>
   statement:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa45275084"><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> tt <span class="token punctuation">(</span>
    id <span class="token datatype">INT</span><span class="token punctuation">,</span>
    <span class="token keyword">data</span> <span class="token datatype">INT</span>
<span class="token punctuation">)</span>
<span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> <span class="token keyword">LIST</span><span class="token punctuation">(</span><span class="token keyword">data</span><span class="token punctuation">)</span> <span class="token punctuation">(</span>
    <span class="token keyword">PARTITION</span> p0 <span class="token keyword">VALUES</span> <span class="token keyword">IN</span> <span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">,</span> <span class="token number">10</span><span class="token punctuation">,</span> <span class="token number">15</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">PARTITION</span> p1 <span class="token keyword">VALUES</span> <span class="token keyword">IN</span> <span class="token punctuation">(</span><span class="token number">6</span><span class="token punctuation">,</span> <span class="token number">12</span><span class="token punctuation">,</span> <span class="token number">18</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   You can add a new partition in which to store rows having the
   <code class="literal">
    data
   </code>
   column values
   <code class="literal">
    7
   </code>
   ,
   <code class="literal">
    14
   </code>
   , and
   <code class="literal">
    21
   </code>
   as shown:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa74784917"><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> tt <span class="token keyword">ADD</span> <span class="token keyword">PARTITION</span> <span class="token punctuation">(</span><span class="token keyword">PARTITION</span> p2 <span class="token keyword">VALUES</span> <span class="token keyword">IN</span> <span class="token punctuation">(</span><span class="token number">7</span><span class="token punctuation">,</span> <span class="token number">14</span><span class="token punctuation">,</span> <span class="token number">21</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Keep in mind that you
   <span class="emphasis">
    <em>
     cannot
    </em>
   </span>
   add a new
   <code class="literal">
    LIST
   </code>
   partition encompassing any values that
        are already included in the value list of an existing partition.
        If you attempt to do so, an error results:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa63731974"><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">ALTER</span> <span class="token keyword">TABLE</span> tt <span class="token keyword">ADD</span> <span class="token keyword">PARTITION</span> 
     <span class="token operator">&gt;</span>     <span class="token punctuation">(</span><span class="token keyword">PARTITION</span> np <span class="token keyword">VALUES</span> <span class="token keyword">IN</span> <span class="token punctuation">(</span><span class="token number">4</span><span class="token punctuation">,</span> <span class="token number">8</span><span class="token punctuation">,</span> <span class="token number">12</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">ERROR 1465 (HY000)<span class="token punctuation">:</span> Multiple definition of same constant »
                    in list partitioning</span></code></pre>
  </div>
  <p>
   Because any rows with the
   <code class="literal">
    data
   </code>
   column value
   <code class="literal">
    12
   </code>
   have already been assigned to partition
   <code class="literal">
    p1
   </code>
   , you cannot create a new partition on
        table
   <code class="literal">
    tt
   </code>
   that includes
   <code class="literal">
    12
   </code>
   in its value list. To accomplish this, you could drop
   <code class="literal">
    p1
   </code>
   , and add
   <code class="literal">
    np
   </code>
   and then a
        new
   <code class="literal">
    p1
   </code>
   with a modified definition. However,
        as discussed earlier, this would result in the loss of all data
        stored in
   <code class="literal">
    p1
   </code>
   —and it is often the case
        that this is not what you really want to do. Another solution
        might appear to be to make a copy of the table with the new
        partitioning and to copy the data into it using
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE TABLE ...
        SELECT ...
    </code>
   </a>
   , then drop the old table and rename the new
        one, but this could be very time-consuming when dealing with a
        large amounts of data. This also might not be feasible in
        situations where high availability is a requirement.
  </p>
  <p>
   You can add multiple partitions in a single
   <code class="literal">
    ALTER TABLE
        ... ADD PARTITION
   </code>
   statement as shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa41058868"><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> employees <span class="token punctuation">(</span>
  id <span class="token datatype">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
  fname <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">50</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
  lname <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">50</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
  hired <span class="token datatype">DATE</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span>
<span class="token punctuation">)</span>
<span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> <span class="token keyword">RANGE</span><span class="token punctuation">(</span> <span class="token function">YEAR</span><span class="token punctuation">(</span>hired<span class="token punctuation">)</span> <span class="token punctuation">)</span> <span class="token punctuation">(</span>
  <span class="token keyword">PARTITION</span> p1 <span class="token keyword">VALUES</span> <span class="token keyword">LESS</span> <span class="token keyword">THAN</span> <span class="token punctuation">(</span><span class="token number">1991</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
  <span class="token keyword">PARTITION</span> p2 <span class="token keyword">VALUES</span> <span class="token keyword">LESS</span> <span class="token keyword">THAN</span> <span class="token punctuation">(</span><span class="token number">1996</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
  <span class="token keyword">PARTITION</span> p3 <span class="token keyword">VALUES</span> <span class="token keyword">LESS</span> <span class="token keyword">THAN</span> <span class="token punctuation">(</span><span class="token number">2001</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
  <span class="token keyword">PARTITION</span> p4 <span class="token keyword">VALUES</span> <span class="token keyword">LESS</span> <span class="token keyword">THAN</span> <span class="token punctuation">(</span><span class="token number">2005</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> employees <span class="token keyword">ADD</span> <span class="token keyword">PARTITION</span> <span class="token punctuation">(</span>
    <span class="token keyword">PARTITION</span> p5 <span class="token keyword">VALUES</span> <span class="token keyword">LESS</span> <span class="token keyword">THAN</span> <span class="token punctuation">(</span><span class="token number">2010</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">PARTITION</span> p6 <span class="token keyword">VALUES</span> <span class="token keyword">LESS</span> <span class="token keyword">THAN</span> <span class="token keyword">MAXVALUE</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Fortunately, MySQL's partitioning implementation provides ways
        to redefine partitions without losing data. Let us look first at
        a couple of simple examples involving
   <code class="literal">
    RANGE
   </code>
   partitioning. Recall the
   <code class="literal">
    members
   </code>
   table which
        is now defined as shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa46338770"><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> members\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> members
Create Table<span class="token punctuation">:</span> CREATE TABLE `members` (
  `id` int(11) DEFAULT NULL,
  `fname` varchar(25) DEFAULT NULL,
  `lname` varchar(25) DEFAULT NULL,
  `dob` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/<span class="token punctuation">*</span>!50100 PARTITION BY RANGE ( YEAR(dob))
(PARTITION n0 VALUES LESS THAN (1970) ENGINE = InnoDB,
 PARTITION n1 VALUES LESS THAN (1980) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (1990) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (2010) ENGINE = InnoDB) <span class="token punctuation">*</span>/
</span><span class="token output">1 row in set (0.00 sec)</span></code></pre>
  </div>
  <p>
   Suppose that you would like to move all rows representing
        members born before 1960 into a separate partition. As we have
        already seen, this cannot be done using
   <a class="link" href="alter-table-partition-operations.html" title="15.1.9.1 ALTER TABLE Partition Operations">
    <code class="literal">
     ALTER
        TABLE ... ADD PARTITION
    </code>
   </a>
   . However, you can use another
        partition-related extension to
   <a class="link" href="alter-table-partition-operations.html" title="15.1.9.1 ALTER TABLE Partition Operations">
    <code class="literal">
     ALTER
        TABLE
    </code>
   </a>
   to accomplish this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa71290203"><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> members <span class="token keyword">REORGANIZE</span> <span class="token keyword">PARTITION</span> n0 <span class="token keyword">INTO</span> <span class="token punctuation">(</span>
    <span class="token keyword">PARTITION</span> s0 <span class="token keyword">VALUES</span> <span class="token keyword">LESS</span> <span class="token keyword">THAN</span> <span class="token punctuation">(</span><span class="token number">1960</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">PARTITION</span> s1 <span class="token keyword">VALUES</span> <span class="token keyword">LESS</span> <span class="token keyword">THAN</span> <span class="token punctuation">(</span><span class="token number">1970</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   In effect, this command splits partition
   <code class="literal">
    p0
   </code>
   into two new partitions
   <code class="literal">
    s0
   </code>
   and
   <code class="literal">
    s1
   </code>
   . It also moves the data that was stored in
   <code class="literal">
    p0
   </code>
   into the new partitions according to the
        rules embodied in the two
   <code class="literal">
    PARTITION ... VALUES
        ...
   </code>
   clauses, so that
   <code class="literal">
    s0
   </code>
   contains
        only those records for which
   <a class="link" href="date-and-time-functions.html#function_year">
    <code class="literal">
     YEAR(dob)
    </code>
   </a>
   is less than 1960 and
   <code class="literal">
    s1
   </code>
   contains those rows in which
   <a class="link" href="date-and-time-functions.html#function_year">
    <code class="literal">
     YEAR(dob)
    </code>
   </a>
   is greater than or
        equal to 1960 but less than 1970.
  </p>
  <p>
   A
   <code class="literal">
    REORGANIZE PARTITION
   </code>
   clause may also be
        used for merging adjacent partitions. You can reverse the effect
        of the previous statement on the
   <code class="literal">
    members
   </code>
   table as shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa26495988"><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> members <span class="token keyword">REORGANIZE</span> <span class="token keyword">PARTITION</span> s0<span class="token punctuation">,</span>s1 <span class="token keyword">INTO</span> <span class="token punctuation">(</span>
    <span class="token keyword">PARTITION</span> p0 <span class="token keyword">VALUES</span> <span class="token keyword">LESS</span> <span class="token keyword">THAN</span> <span class="token punctuation">(</span><span class="token number">1970</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   No data is lost in splitting or merging partitions using
   <code class="literal">
    REORGANIZE PARTITION
   </code>
   . In executing the above
        statement, MySQL moves all of the records that were stored in
        partitions
   <code class="literal">
    s0
   </code>
   and
   <code class="literal">
    s1
   </code>
   into
        partition
   <code class="literal">
    p0
   </code>
   .
  </p>
  <p>
   The general syntax for
   <code class="literal">
    REORGANIZE PARTITION
   </code>
   is shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa98955978"><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> <em class="replaceable">tbl_name</em>
    <span class="token keyword">REORGANIZE</span> <span class="token keyword">PARTITION</span> <em class="replaceable">partition_list</em>
    <span class="token keyword">INTO</span> <span class="token punctuation">(</span><em class="replaceable">partition_definitions</em><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Here,
   <em class="replaceable">
    <code>
     tbl_name
    </code>
   </em>
   is the name of the
        partitioned table, and
   <em class="replaceable">
    <code>
     partition_list
    </code>
   </em>
   is a comma-separated list of names of one or more existing
        partitions to be changed.
   <em class="replaceable">
    <code>
     partition_definitions
    </code>
   </em>
   is a
        comma-separated list of new partition definitions, which follow
        the same rules as for the
   <em class="replaceable">
    <code>
     partition_definitions
    </code>
   </em>
   list used in
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE TABLE
    </code>
   </a>
   . You are not limited
        to merging several partitions into one, or to splitting one
        partition into many, when using
   <code class="literal">
    REORGANIZE
        PARTITION
   </code>
   . For example, you can reorganize all four
        partitions of the
   <code class="literal">
    members
   </code>
   table into two,
        like this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa70567490"><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> members <span class="token keyword">REORGANIZE</span> <span class="token keyword">PARTITION</span> p0<span class="token punctuation">,</span>p1<span class="token punctuation">,</span>p2<span class="token punctuation">,</span>p3 <span class="token keyword">INTO</span> <span class="token punctuation">(</span>
    <span class="token keyword">PARTITION</span> m0 <span class="token keyword">VALUES</span> <span class="token keyword">LESS</span> <span class="token keyword">THAN</span> <span class="token punctuation">(</span><span class="token number">1980</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">PARTITION</span> m1 <span class="token keyword">VALUES</span> <span class="token keyword">LESS</span> <span class="token keyword">THAN</span> <span class="token punctuation">(</span><span class="token number">2000</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   You can also use
   <code class="literal">
    REORGANIZE PARTITION
   </code>
   with
        tables that are partitioned by
   <code class="literal">
    LIST
   </code>
   . Let us
        return to the problem of adding a new partition to the
        list-partitioned
   <code class="literal">
    tt
   </code>
   table and failing because
        the new partition had a value that was already present in the
        value-list of one of the existing partitions. We can handle this
        by adding a partition that contains only nonconflicting values,
        and then reorganizing the new partition and the existing one so
        that the value which was stored in the existing one is now moved
        to the new one:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa62553044"><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> tt <span class="token keyword">ADD</span> <span class="token keyword">PARTITION</span> <span class="token punctuation">(</span><span class="token keyword">PARTITION</span> np <span class="token keyword">VALUES</span> <span class="token keyword">IN</span> <span class="token punctuation">(</span><span class="token number">4</span><span class="token punctuation">,</span> <span class="token number">8</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> tt <span class="token keyword">REORGANIZE</span> <span class="token keyword">PARTITION</span> p1<span class="token punctuation">,</span>np <span class="token keyword">INTO</span> <span class="token punctuation">(</span>
    <span class="token keyword">PARTITION</span> p1 <span class="token keyword">VALUES</span> <span class="token keyword">IN</span> <span class="token punctuation">(</span><span class="token number">6</span><span class="token punctuation">,</span> <span class="token number">18</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">PARTITION</span> np <span class="token keyword">VALUES</span> <span class="token keyword">in</span> <span class="token punctuation">(</span><span class="token number">4</span><span class="token punctuation">,</span> <span class="token number">8</span><span class="token punctuation">,</span> <span class="token number">12</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Here are some key points to keep in mind when using
   <code class="literal">
    ALTER TABLE ... REORGANIZE PARTITION
   </code>
   to
        repartition tables that are partitioned by
   <code class="literal">
    RANGE
   </code>
   or
   <code class="literal">
    LIST
   </code>
   :
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      The
      <code class="literal">
       PARTITION
      </code>
      options used to determine
            the new partitioning scheme are subject to the same rules as
            those used with a
      <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
       <code class="literal">
        CREATE
            TABLE
       </code>
      </a>
      statement.
     </p>
     <p>
      A new
      <code class="literal">
       RANGE
      </code>
      partitioning scheme cannot
            have any overlapping ranges; a new
      <code class="literal">
       LIST
      </code>
      partitioning scheme cannot have any overlapping sets of
            values.
     </p>
    </li>
    <li class="listitem">
     <p>
      The combination of partitions in the
      <em class="replaceable">
       <code>
        partition_definitions
       </code>
      </em>
      list should
            account for the same range or set of values overall as the
            combined partitions named in the
      <em class="replaceable">
       <code>
        partition_list
       </code>
      </em>
      .
     </p>
     <p>
      For example, partitions
      <code class="literal">
       p1
      </code>
      and
      <code class="literal">
       p2
      </code>
      together cover the years 1980 through
            1999 in the
      <code class="literal">
       members
      </code>
      table used as an
            example in this section. Any reorganization of these two
            partitions should cover the same range of years overall.
     </p>
    </li>
    <li class="listitem">
     <p>
      For tables partitioned by
      <code class="literal">
       RANGE
      </code>
      , you can
            reorganize only adjacent partitions; you cannot skip range
            partitions.
     </p>
     <p>
      For instance, you could not reorganize the example
      <code class="literal">
       members
      </code>
      table using a statement beginning
            with
      <code class="literal">
       ALTER TABLE members REORGANIZE PARTITION p0,p2
            INTO ...
      </code>
      because
      <code class="literal">
       p0
      </code>
      covers the
            years prior to 1970 and
      <code class="literal">
       p2
      </code>
      the years from
            1990 through 1999 inclusive, so these are not adjacent
            partitions. (You cannot skip partition
      <code class="literal">
       p1
      </code>
      in this case.)
     </p>
    </li>
    <li class="listitem">
     <p>
      You cannot use
      <code class="literal">
       REORGANIZE PARTITION
      </code>
      to
            change the type of partitioning used by the table (for
            example, you cannot change
      <code class="literal">
       RANGE
      </code>
      partitions to
      <code class="literal">
       HASH
      </code>
      partitions or the
            reverse). You also cannot use this statement to change the
            partitioning expression or column. To accomplish either of
            these tasks without dropping and re-creating the table, you
            can use
      <a class="link" href="alter-table-partition-operations.html" title="15.1.9.1 ALTER TABLE Partition Operations">
       <code class="literal">
        ALTER
            TABLE ... PARTITION BY ...
       </code>
      </a>
      , as shown here:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa17975765"><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> members
    <span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> <span class="token keyword">HASH</span><span class="token punctuation">(</span> <span class="token function">YEAR</span><span class="token punctuation">(</span>dob<span class="token punctuation">)</span> <span class="token punctuation">)</span>
    <span class="token keyword">PARTITIONS</span> <span class="token number">8</span><span class="token punctuation">;</span></code></pre>
     </div>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
