<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h2 class="title">
      <a name="partitioning-limitations">
      </a>
      26.6 Restrictions and Limitations on Partitioning
     </h2>
    </div>
   </div>
  </div>
  <div class="toc">
   <dl class="toc">
    <dt>
     <span class="section">
      <a href="partitioning-limitations-partitioning-keys-unique-keys.html">
       26.6.1 Partitioning Keys, Primary Keys, and Unique Keys
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="partitioning-limitations-storage-engines.html">
       26.6.2 Partitioning Limitations Relating to Storage Engines
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="partitioning-limitations-functions.html">
       26.6.3 Partitioning Limitations Relating to Functions
      </a>
     </span>
    </dt>
   </dl>
  </div>
  <a class="indexterm" name="idm46045083091392">
  </a>
  <p>
   This section discusses current restrictions and limitations on
      MySQL partitioning support.
  </p>
  <p>
   <b>
    Prohibited constructs.
   </b>
   The following constructs are not permitted in partitioning
        expressions:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Stored procedures, stored functions, loadable functions, or
          plugins.
     </p>
    </li>
    <li class="listitem">
     <p>
      Declared variables or user variables.
     </p>
    </li>
   </ul>
  </div>
  <p>
   For a list of SQL functions which are permitted in partitioning
      expressions, see
   <a class="xref" href="partitioning-limitations-functions.html" title="26.6.3 Partitioning Limitations Relating to Functions">
    Section 26.6.3, “Partitioning Limitations Relating to Functions”
   </a>
   .
  </p>
  <p>
   <b>
    Arithmetic and logical operators.
   </b>
   <a class="indexterm" name="idm46045083083888">
   </a>
   <a class="indexterm" name="idm46045083082368">
   </a>
   Use of the arithmetic operators
   <a class="link" href="arithmetic-functions.html#operator_plus">
    <code class="literal">
     +
    </code>
   </a>
   ,
   <a class="link" href="arithmetic-functions.html#operator_minus">
    <code class="literal">
     -
    </code>
   </a>
   , and
   <a class="link" href="arithmetic-functions.html#operator_times">
    <code class="literal">
     *
    </code>
   </a>
   is permitted in
        partitioning expressions. However, the result must be an integer
        value or
   <code class="literal">
    NULL
   </code>
   (except in the case of
   <code class="literal">
    [LINEAR] KEY
   </code>
   partitioning, as discussed
        elsewhere in this chapter; see
   <a class="xref" href="partitioning-types.html" title="26.2 Partitioning Types">
    Section 26.2, “Partitioning Types”
   </a>
   , for more information).
  </p>
  <p>
   The
   <a class="link" href="arithmetic-functions.html#operator_div">
    <code class="literal">
     DIV
    </code>
   </a>
   operator is also supported;
      the
   <a class="link" href="arithmetic-functions.html#operator_divide">
    <code class="literal">
     /
    </code>
   </a>
   operator is
      not permitted.
  </p>
  <p>
   The bit operators
   <a class="link" href="bit-functions.html#operator_bitwise-or">
    <code class="literal">
     |
    </code>
   </a>
   ,
   <a class="link" href="bit-functions.html#operator_bitwise-and">
    <code class="literal">
     &amp;
    </code>
   </a>
   ,
   <a class="link" href="bit-functions.html#operator_bitwise-xor">
    <code class="literal">
     ^
    </code>
   </a>
   ,
   <a class="link" href="bit-functions.html#operator_left-shift">
    <code class="literal">
     &lt;&lt;
    </code>
   </a>
   ,
   <a class="link" href="bit-functions.html#operator_right-shift">
    <code class="literal">
     &gt;&gt;
    </code>
   </a>
   , and
   <a class="link" href="bit-functions.html#operator_bitwise-invert">
    <code class="literal">
     ~
    </code>
   </a>
   are not
      permitted in partitioning expressions.
  </p>
  <p>
   <b>
    Server SQL mode.
   </b>
   <a class="indexterm" name="idm46045083063072">
   </a>
   <a class="indexterm" name="idm46045083061584">
   </a>
   Tables employing user-defined partitioning do not preserve the
        SQL mode in effect at the time that they were created. As
        discussed elsewhere in this Manual (see
   <a class="xref" href="sql-mode.html" title="7.1.11 Server SQL Modes">
    Section 7.1.11, “Server SQL Modes”
   </a>
   ), the results of many MySQL functions
        and operators may change according to the server SQL mode.
        Therefore, a change in the SQL mode at any time after the
        creation of partitioned tables may lead to major changes in the
        behavior of such tables, and could easily lead to corruption or
        loss of data. For these reasons,
   <span class="emphasis">
    <em>
     it is strongly
        recommended that you never change the server SQL mode after
        creating partitioned tables
    </em>
   </span>
   .
  </p>
  <p>
   For one such change in the server SQL mode making a partitioned
      tables unusable, consider the following
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE TABLE
    </code>
   </a>
   statement, which can
      be executed successfully only if the
   <a class="link" href="sql-mode.html#sqlmode_no_unsigned_subtraction">
    <code class="literal">
     NO_UNSIGNED_SUBTRACTION
    </code>
   </a>
   mode is
      in effect:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa14258501"><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 variable">@@sql_mode</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>
<span class="token output"><span class="token punctuation">|</span> @@sql_mode <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>
<span class="token output"><span class="token punctuation">|</span>            <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>
<span class="token output">1 row in set (0.00 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> tu <span class="token punctuation">(</span>c1 <span class="token datatype">BIGINT</span> <span class="token keyword">UNSIGNED</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>c1 <span class="token operator">-</span> <span class="token number">10</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 operator">-</span><span class="token number">5</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">0</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">5</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">10</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 keyword">MAXVALUE</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">ERROR 1563 (HY000)<span class="token punctuation">:</span> Partition constant is out of partition function domain
</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SET</span> sql_mode<span class="token operator">=</span><span class="token string">'NO_UNSIGNED_SUBTRACTION'</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.00 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token variable">@@sql_mode</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>
<span class="token output"><span class="token punctuation">|</span> @@sql_mode              <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>
<span class="token output"><span class="token punctuation">|</span> NO_UNSIGNED_SUBTRACTION <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>
<span class="token output">1 row in set (0.00 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> tu <span class="token punctuation">(</span>c1 <span class="token datatype">BIGINT</span> <span class="token keyword">UNSIGNED</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>c1 <span class="token operator">-</span> <span class="token number">10</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 operator">-</span><span class="token number">5</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">0</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">5</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">10</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 keyword">MAXVALUE</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.05 sec)</span></code></pre>
  </div>
  <p>
   If you remove the
   <a class="link" href="sql-mode.html#sqlmode_no_unsigned_subtraction">
    <code class="literal">
     NO_UNSIGNED_SUBTRACTION
    </code>
   </a>
   server
      SQL mode after creating
   <code class="literal">
    tu
   </code>
   , you may no longer
      be able to access this table:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa68063195"><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">SET</span> sql_mode<span class="token operator">=</span><span class="token string">''</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows 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> tu<span class="token punctuation">;</span>
<span class="token output">ERROR 1563 (HY000)<span class="token punctuation">:</span> Partition constant is out of partition function domain</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> tu <span class="token keyword">VALUES</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 output">ERROR 1563 (HY000)<span class="token punctuation">:</span> Partition constant is out of partition function domain</span></code></pre>
  </div>
  <p>
   See also
   <a class="xref" href="sql-mode.html" title="7.1.11 Server SQL Modes">
    Section 7.1.11, “Server SQL Modes”
   </a>
   .
  </p>
  <p>
   Server SQL modes also impact replication of partitioned tables.
      Disparate SQL modes on source and replica can lead to partitioning
      expressions being evaluated differently; this can cause the
      distribution of data among partitions to be different in the
      source's and replica's copies of a given table, and may
      even cause inserts into partitioned tables that succeed on the
      source to fail on the replica. For best results, you should always
      use the same server SQL mode on the source and on the replica.
  </p>
  <p>
   <b>
    Performance considerations.
   </b>
   Some effects of partitioning operations on performance are given
        in the following list:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a name="partitioning-limitations-file-system-ops">
      </a>
      <b>
       File system operations.
      </b>
      Partitioning and repartitioning operations (such as
      <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>
      with
      <code class="literal">
       PARTITION BY ...
      </code>
      ,
      <code class="literal">
       REORGANIZE PARTITION
      </code>
      , or
      <code class="literal">
       REMOVE
            PARTITIONING
      </code>
      ) depend on file system operations for
            their implementation. This means that the speed of these
            operations is affected by such factors as file system type
            and characteristics, disk speed, swap space, file handling
            efficiency of the operating system, and MySQL server options
            and variables that relate to file handling. In particular,
            you should make sure that
      <a class="link" href="server-system-variables.html#sysvar_large_files_support">
       <code class="literal">
        large_files_support
       </code>
      </a>
      is
            enabled and that
      <a class="link" href="server-system-variables.html#sysvar_open_files_limit">
       <code class="literal">
        open_files_limit
       </code>
      </a>
      is set
            properly. Partitioning and repartitioning operations
            involving
      <code class="literal">
       InnoDB
      </code>
      tables may be made more
            efficient by enabling
      <a class="link" href="innodb-parameters.html#sysvar_innodb_file_per_table">
       <code class="literal">
        innodb_file_per_table
       </code>
      </a>
      .
     </p>
     <p>
      See also
      <a class="xref" href="partitioning-limitations.html#partitioning-limitations-max-partitions" title="Maximum number of partitions">
       Maximum number of partitions
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <b>
       Table locks.
      </b>
      Generally, the process executing a partitioning operation on
            a table takes a write lock on the table. Reads from such
            tables are relatively unaffected; pending
      <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
       <code class="literal">
        INSERT
       </code>
      </a>
      and
      <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
       <code class="literal">
        UPDATE
       </code>
      </a>
      operations are
            performed as soon as the partitioning operation has
            completed. For
      <code class="literal">
       InnoDB
      </code>
      -specific exceptions
            to this limitation, see
      <a class="xref" href="innodb-online-ddl-operations.html#online-ddl-partitioning" title="Partitioning Operations">
       Partitioning Operations
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <b>
       Indexes; partition pruning.
      </b>
      As with nonpartitioned tables, proper use of indexes can
            speed up queries on partitioned tables significantly. In
            addition, designing partitioned tables and queries on these
            tables to take advantage of
      <span class="firstterm">
       partition pruning
      </span>
      can
            improve performance dramatically. See
      <a class="xref" href="partitioning-pruning.html" title="26.4 Partition Pruning">
       Section 26.4, “Partition Pruning”
      </a>
      , for more
            information.
     </p>
     <p>
      Index condition pushdown is supported for partitioned tables.
          See
      <a class="xref" href="index-condition-pushdown-optimization.html" title="10.2.1.6 Index Condition Pushdown Optimization">
       Section 10.2.1.6, “Index Condition Pushdown Optimization”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <b>
       Performance with LOAD DATA.
      </b>
      In MySQL 8.4,
      <a class="link" href="load-data.html" title="15.2.9 LOAD DATA Statement">
       <code class="literal">
        LOAD
            DATA
       </code>
      </a>
      uses buffering to improve performance. You
            should be aware that the buffer uses 130 KB memory per
            partition to achieve this.
     </p>
    </li>
   </ul>
  </div>
  <p>
   <a name="partitioning-limitations-max-partitions">
   </a>
   <b>
    Maximum number of partitions.
   </b>
   <a class="indexterm" name="idm46045083001824">
   </a>
   <a class="indexterm" name="idm46045083000320">
   </a>
   The maximum possible number of partitions for a given table not
        using the
   <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
    <code class="literal">
     NDB
    </code>
   </a>
   storage engine is
        8192. This number includes subpartitions.
  </p>
  <p>
   The maximum possible number of user-defined partitions for a table
      using the
   <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
    <code class="literal">
     NDB
    </code>
   </a>
   storage engine is
      determined according to the version of the NDB Cluster software
      being used, the number of data nodes, and other factors. See
   <a class="xref" href="mysql-cluster-nodes-groups.html#mysql-cluster-nodes-groups-user-partitioning" title="NDB and user-defined partitioning">
    NDB and user-defined partitioning
   </a>
   ,
      for more information.
  </p>
  <p>
   If, when creating tables with a large number of partitions (but
      less than the maximum), you encounter an error message such as
   <span class="errortext">
    Got error ... from storage engine: Out of resources
      when opening file
   </span>
   , you may be able to address the
      issue by increasing the value of the
   <a class="link" href="server-system-variables.html#sysvar_open_files_limit">
    <code class="literal">
     open_files_limit
    </code>
   </a>
   system variable.
      However, this is dependent on the operating system, and may not be
      possible or advisable on all platforms; see
   <a class="xref" href="not-enough-file-handles.html" title="B.3.2.16 File Not Found and Similar Errors">
    Section B.3.2.16, “File Not Found and Similar Errors”
   </a>
   , for more information.
      In some cases, using large numbers (hundreds) of partitions may
      also not be advisable due to other concerns, so using more
      partitions does not automatically lead to better results.
  </p>
  <p>
   See also
   <a class="xref" href="partitioning-limitations.html#partitioning-limitations-file-system-ops" title="File system operations">
    File system operations
   </a>
   .
  </p>
  <p>
   <b>
    Foreign keys not supported for partitioned InnoDB tables.
   </b>
   <a class="indexterm" name="idm46045082988864">
   </a>
   Partitioned tables using the
   <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
    <code class="literal">
     InnoDB
    </code>
   </a>
   storage engine do not support foreign keys. More specifically,
        this means that the following two statements are true:
  </p>
  <div class="orderedlist">
   <ol class="orderedlist" type="1">
    <li class="listitem">
     <p>
      No definition of an
      <code class="literal">
       InnoDB
      </code>
      table employing
          user-defined partitioning may contain foreign key references;
          no
      <code class="literal">
       InnoDB
      </code>
      table whose definition contains
          foreign key references may be partitioned.
     </p>
    </li>
    <li class="listitem">
     <p>
      No
      <code class="literal">
       InnoDB
      </code>
      table definition may contain a
          foreign key reference to a user-partitioned table; no
      <code class="literal">
       InnoDB
      </code>
      table with user-defined partitioning
          may contain columns referenced by foreign keys.
     </p>
    </li>
   </ol>
  </div>
  <p>
   The scope of the restrictions just listed includes all tables that
      use the
   <code class="literal">
    InnoDB
   </code>
   storage engine.
   <a class="link" href="create-table-foreign-keys.html" title="15.1.20.5 FOREIGN KEY Constraints">
    <code class="literal">
     CREATE
      TABLE
    </code>
   </a>
   and
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
    </code>
   </a>
   statements that would result in tables violating these
      restrictions are not allowed.
  </p>
  <p>
   <b>
    ALTER TABLE ... ORDER BY.
   </b>
   An
   <code class="literal">
    ALTER TABLE ... ORDER BY
    <em class="replaceable">
     <code>
      column
     </code>
    </em>
   </code>
   statement run
        against a partitioned table causes ordering of rows only within
        each partition.
  </p>
  <p>
   <b>
    ADD COLUMN ... ALGORITHM=INSTANT.
   </b>
   Once you perform
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE ... ADD
        COLUMN ... ALGORITHM=INSTANT
    </code>
   </a>
   on a partitioned table,
        it is no longer possible to exchange partitions with this table.
  </p>
  <p>
   <b>
    Effects on REPLACE statements by modification of primary keys.
   </b>
   It can be desirable in some cases (see
   <a class="xref" href="partitioning-limitations-partitioning-keys-unique-keys.html" title="26.6.1 Partitioning Keys, Primary Keys, and Unique Keys">
    Section 26.6.1, “Partitioning Keys, Primary Keys, and Unique Keys”
   </a>
   )
        to modify a table's primary key. Be aware that, if your
        application uses
   <a class="link" href="replace.html" title="15.2.12 REPLACE Statement">
    <code class="literal">
     REPLACE
    </code>
   </a>
   statements and you do this, the results of these statements can
        be drastically altered. See
   <a class="xref" href="replace.html" title="15.2.12 REPLACE Statement">
    Section 15.2.12, “REPLACE Statement”
   </a>
   , for more
        information and an example.
  </p>
  <p>
   <b>
    FULLTEXT indexes.
   </b>
   <a class="indexterm" name="idm46045082966144">
   </a>
   Partitioned tables do not support
   <code class="literal">
    FULLTEXT
   </code>
   indexes or searches.
  </p>
  <p>
   <b>
    Spatial columns.
   </b>
   Columns with spatial data types such as
   <code class="literal">
    POINT
   </code>
   or
   <code class="literal">
    GEOMETRY
   </code>
   cannot be used in partitioned
        tables.
  </p>
  <p>
   <b>
    Temporary tables.
   </b>
   <a class="indexterm" name="idm46045082959952">
   </a>
   Temporary tables cannot be partitioned.
  </p>
  <p>
   <b>
    Log tables.
   </b>
   It is not possible to partition the log tables; an
   <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>
   statement on such a table
        fails with an error.
  </p>
  <p>
   <b>
    Data type of partitioning key.
   </b>
   <a class="indexterm" name="idm46045082954592">
   </a>
   A partitioning key must be either an integer column or an
        expression that resolves to an integer. Expressions employing
   <a class="link" href="enum.html" title="13.3.5 The ENUM Type">
    <code class="literal">
     ENUM
    </code>
   </a>
   columns cannot be used. The
        column or expression value may also be
   <code class="literal">
    NULL
   </code>
   ;
        see
   <a class="xref" href="partitioning-handling-nulls.html" title="26.2.7 How MySQL Partitioning Handles NULL">
    Section 26.2.7, “How MySQL Partitioning Handles NULL”
   </a>
   .
  </p>
  <p>
   There are two exceptions to this restriction:
  </p>
  <div class="orderedlist">
   <ol class="orderedlist" type="1">
    <li class="listitem">
     <p>
      When partitioning by [
      <code class="literal">
       LINEAR
      </code>
      ]
      <code class="literal">
       KEY
      </code>
      , it is possible to use columns of any
          valid MySQL data type other than
      <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
       <code class="literal">
        TEXT
       </code>
      </a>
      or
      <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
       <code class="literal">
        BLOB
       </code>
      </a>
      as partitioning keys,
          because the internal key-hashing functions produce the correct
          data type from these types. For example, the following two
      <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
       <code class="literal">
        CREATE TABLE
       </code>
      </a>
      statements are
          valid:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa81249479"><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> tkc <span class="token punctuation">(</span>c1 <span class="token datatype">CHAR</span><span class="token punctuation">)</span>
<span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> <span class="token keyword">KEY</span><span class="token punctuation">(</span>c1<span class="token punctuation">)</span>
<span class="token keyword">PARTITIONS</span> <span class="token number">4</span><span class="token punctuation">;</span>

<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> tke
    <span class="token punctuation">(</span> c1 <span class="token datatype">ENUM</span><span class="token punctuation">(</span><span class="token string">'red'</span><span class="token punctuation">,</span> <span class="token string">'orange'</span><span class="token punctuation">,</span> <span class="token string">'yellow'</span><span class="token punctuation">,</span> <span class="token string">'green'</span><span class="token punctuation">,</span> <span class="token string">'blue'</span><span class="token punctuation">,</span> <span class="token string">'indigo'</span><span class="token punctuation">,</span> <span class="token string">'violet'</span><span class="token punctuation">)</span> <span class="token punctuation">)</span>
<span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> <span class="token keyword">LINEAR</span> <span class="token keyword">KEY</span><span class="token punctuation">(</span>c1<span class="token punctuation">)</span>
<span class="token keyword">PARTITIONS</span> <span class="token number">6</span><span class="token punctuation">;</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      When partitioning by
      <code class="literal">
       RANGE COLUMNS
      </code>
      or
      <code class="literal">
       LIST COLUMNS
      </code>
      , it is possible to use string,
      <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
       <code class="literal">
        DATE
       </code>
      </a>
      , and
      <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
       <code class="literal">
        DATETIME
       </code>
      </a>
      columns. For example,
          each of the following
      <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
       <code class="literal">
        CREATE
          TABLE
       </code>
      </a>
      statements is valid:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa28416815"><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> rc <span class="token punctuation">(</span>c1 <span class="token datatype">INT</span><span class="token punctuation">,</span> c2 <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 keyword">COLUMNS</span><span class="token punctuation">(</span>c2<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 string">'1990-01-01'</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 string">'1995-01-01'</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 string">'2000-01-01'</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 string">'2005-01-01'</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 keyword">MAXVALUE</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> lc <span class="token punctuation">(</span>c1 <span class="token datatype">INT</span><span class="token punctuation">,</span> c2 <span class="token datatype">CHAR</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">)</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 keyword">COLUMNS</span><span class="token punctuation">(</span>c2<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 string">'a'</span><span class="token punctuation">,</span> <span class="token string">'d'</span><span class="token punctuation">,</span> <span class="token string">'g'</span><span class="token punctuation">,</span> <span class="token string">'j'</span><span class="token punctuation">,</span> <span class="token string">'m'</span><span class="token punctuation">,</span> <span class="token string">'p'</span><span class="token punctuation">,</span> <span class="token string">'s'</span><span class="token punctuation">,</span> <span class="token string">'v'</span><span class="token punctuation">,</span> <span class="token string">'y'</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 string">'b'</span><span class="token punctuation">,</span> <span class="token string">'e'</span><span class="token punctuation">,</span> <span class="token string">'h'</span><span class="token punctuation">,</span> <span class="token string">'k'</span><span class="token punctuation">,</span> <span class="token string">'n'</span><span class="token punctuation">,</span> <span class="token string">'q'</span><span class="token punctuation">,</span> <span class="token string">'t'</span><span class="token punctuation">,</span> <span class="token string">'w'</span><span class="token punctuation">,</span> <span class="token string">'z'</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">IN</span><span class="token punctuation">(</span><span class="token string">'c'</span><span class="token punctuation">,</span> <span class="token string">'f'</span><span class="token punctuation">,</span> <span class="token string">'i'</span><span class="token punctuation">,</span> <span class="token string">'l'</span><span class="token punctuation">,</span> <span class="token string">'o'</span><span class="token punctuation">,</span> <span class="token string">'r'</span><span class="token punctuation">,</span> <span class="token string">'u'</span><span class="token punctuation">,</span> <span class="token string">'x'</span><span class="token punctuation">,</span> <span class="token boolean">NULL</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
     </div>
    </li>
   </ol>
  </div>
  <p>
   Neither of the preceding exceptions applies to
   <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
    <code class="literal">
     BLOB
    </code>
   </a>
   or
   <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
    <code class="literal">
     TEXT
    </code>
   </a>
   column types.
  </p>
  <p>
   <b>
    Subqueries.
   </b>
   <a class="indexterm" name="idm46045082929248">
   </a>
   A partitioning key may not be a subquery, even if that subquery
        resolves to an integer value or
   <code class="literal">
    NULL
   </code>
   .
  </p>
  <a class="indexterm" name="idm46045082926400">
  </a>
  <a class="indexterm" name="idm46045082924912">
  </a>
  <p>
   <a name="partitioning-limitations-prefixes">
   </a>
   <b>
    Column index prefixes not supported for key partitioning.
   </b>
   When creating a table that is partitioned by key, any columns in
        the partitioning key which use column prefixes are not allowed
        in the table's partitioning function. Consider the
        following
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE TABLE
    </code>
   </a>
   statement,
        which has three
   <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
    <code class="literal">
     VARCHAR
    </code>
   </a>
   columns,
        and whose primary key uses all three columns and specifies a
        prefix for one of them. This statement is rejected with an
        error, as shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa81760888"><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">USE</span> d<span class="token punctuation">;</span>
<span class="token keyword">Database</span> <span class="token keyword">changed</span>
<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">VARCHAR</span><span class="token punctuation">(</span><span class="token number">10000</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>     b <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>
<span class="token prompt">    -&gt;</span>     c <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>     <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span>a<span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span><span class="token punctuation">,</span> b<span class="token punctuation">,</span> c<span class="token punctuation">)</span>
<span class="token prompt">    -&gt;</span> <span class="token punctuation">)</span> <span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> <span class="token keyword">KEY</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token keyword">PARTITIONS</span> <span class="token number">2</span><span class="token punctuation">;</span>
<span class="token output">ERROR 6123 (HY000)<span class="token punctuation">:</span> Column 'd.t1.a' having prefix key part 'a(10)' in the
PARTITION BY KEY() clause is not supported.</span></code></pre>
  </div>
  <p>
   For general information about partitioning tables by key, see
   <a class="xref" href="partitioning-key.html" title="26.2.5 KEY Partitioning">
    Section 26.2.5, “KEY Partitioning”
   </a>
   .
  </p>
  <p>
   <a name="partitioning-limitations-subpartitions">
   </a>
   <b>
    Issues with subpartitions.
   </b>
   <a class="indexterm" name="idm46045082910032">
   </a>
   <a class="indexterm" name="idm46045082908544">
   </a>
   Subpartitions must use
   <code class="literal">
    HASH
   </code>
   or
   <code class="literal">
    KEY
   </code>
   partitioning. Only
   <code class="literal">
    RANGE
   </code>
   and
   <code class="literal">
    LIST
   </code>
   partitions
        may be subpartitioned;
   <code class="literal">
    HASH
   </code>
   and
   <code class="literal">
    KEY
   </code>
   partitions cannot be subpartitioned.
  </p>
  <p>
   <a class="indexterm" name="idm46045082901904">
   </a>
   <code class="literal">
    SUBPARTITION BY KEY
   </code>
   requires that the
      subpartitioning column or columns be specified explicitly, unlike
      the case with
   <code class="literal">
    PARTITION BY KEY
   </code>
   , where it can be
      omitted (in which case the table's primary key column is used
      by default). Consider the table created by this statement:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa72953334"><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> ts <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 keyword">AUTO_INCREMENT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</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">30</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   You can create a table having the same columns, partitioned by
   <code class="literal">
    KEY
   </code>
   , using a statement such as this one:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa29554488"><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> ts <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 keyword">AUTO_INCREMENT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</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">30</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span>
<span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> <span class="token keyword">KEY</span><span class="token punctuation">(</span><span class="token punctuation">)</span>
<span class="token keyword">PARTITIONS</span> <span class="token number">4</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   The previous statement is treated as though it had been written
      like this, with the table's primary key column used as the
      partitioning column:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa27964780"><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> ts <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 keyword">AUTO_INCREMENT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</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">30</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span>
<span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> <span class="token keyword">KEY</span><span class="token punctuation">(</span>id<span class="token punctuation">)</span>
<span class="token keyword">PARTITIONS</span> <span class="token number">4</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   However, the following statement that attempts to create a
      subpartitioned table using the default column as the
      subpartitioning column fails, and the column must be specified for
      the statement to succeed, as shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa26734690"><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> ts <span class="token punctuation">(</span>
<span class="token prompt">    -&gt;</span>     id <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>     <span class="token keyword">name</span> <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">30</span><span class="token punctuation">)</span>
<span class="token prompt">    -&gt;</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>id<span class="token punctuation">)</span>
<span class="token prompt">    -&gt;</span> <span class="token keyword">SUBPARTITION</span> <span class="token keyword">BY</span> <span class="token keyword">KEY</span><span class="token punctuation">(</span><span class="token punctuation">)</span>
<span class="token prompt">    -&gt;</span> <span class="token keyword">SUBPARTITIONS</span> <span class="token number">4</span>
<span class="token prompt">    -&gt;</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">100</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 keyword">MAXVALUE</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">ERROR 1064 (42000)<span class="token punctuation">:</span> You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near ')
</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> ts <span class="token punctuation">(</span>
<span class="token prompt">    -&gt;</span>     id <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>     <span class="token keyword">name</span> <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">30</span><span class="token punctuation">)</span>
<span class="token prompt">    -&gt;</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>id<span class="token punctuation">)</span>
<span class="token prompt">    -&gt;</span> <span class="token keyword">SUBPARTITION</span> <span class="token keyword">BY</span> <span class="token keyword">KEY</span><span class="token punctuation">(</span>id<span class="token punctuation">)</span>
<span class="token prompt">    -&gt;</span> <span class="token keyword">SUBPARTITIONS</span> <span class="token number">4</span>
<span class="token prompt">    -&gt;</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">100</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 keyword">MAXVALUE</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.07 sec)</span></code></pre>
  </div>
  <p>
   This is a known issue (see Bug #51470).
  </p>
  <p>
   <b>
    DATA DIRECTORY and INDEX DIRECTORY options.
   </b>
   Table-level
   <code class="literal">
    DATA DIRECTORY
   </code>
   and
   <code class="literal">
    INDEX
        DIRECTORY
   </code>
   options are ignored (see Bug #32091). You
        can employ these options for individual partitions or
        subpartitions of
   <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
    <code class="literal">
     InnoDB
    </code>
   </a>
   tables. The
        directory specified in a
   <code class="literal">
    DATA DIRECTORY
   </code>
   clause must be known to
   <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
    <code class="literal">
     InnoDB
    </code>
   </a>
   . For
        more information, see
   <a class="xref" href="innodb-create-table-external.html#innodb-create-table-external-data-directory" title="Using the DATA DIRECTORY Clause">
    Using the DATA DIRECTORY Clause
   </a>
   .
  </p>
  <p>
   <b>
    Repairing and rebuilding partitioned tables.
   </b>
   The statements
   <a class="link" href="check-table.html" title="15.7.3.2 CHECK TABLE Statement">
    <code class="literal">
     CHECK TABLE
    </code>
   </a>
   ,
   <a class="link" href="optimize-table.html" title="15.7.3.4 OPTIMIZE TABLE Statement">
    <code class="literal">
     OPTIMIZE TABLE
    </code>
   </a>
   ,
   <a class="link" href="analyze-table.html" title="15.7.3.1 ANALYZE TABLE Statement">
    <code class="literal">
     ANALYZE TABLE
    </code>
   </a>
   , and
   <a class="link" href="repair-table.html" title="15.7.3.5 REPAIR TABLE Statement">
    <code class="literal">
     REPAIR TABLE
    </code>
   </a>
   are supported for
        partitioned tables.
  </p>
  <p>
   In addition, you can use
   <code class="literal">
    ALTER TABLE ... REBUILD
      PARTITION
   </code>
   to rebuild one or more partitions of a
      partitioned table;
   <code class="literal">
    ALTER TABLE ... REORGANIZE
      PARTITION
   </code>
   also causes partitions to be rebuilt. See
   <a class="xref" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    Section 15.1.9, “ALTER TABLE Statement”
   </a>
   , for more information about these
      two statements.
  </p>
  <p>
   <code class="literal">
    ANALYZE
   </code>
   ,
   <code class="literal">
    CHECK
   </code>
   ,
   <code class="literal">
    OPTIMIZE
   </code>
   ,
   <code class="literal">
    REPAIR
   </code>
   , and
   <code class="literal">
    TRUNCATE
   </code>
   operations are supported with
      subpartitions. See
   <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>
   <b>
    File name delimiters for partitions and subpartitions.
   </b>
   Table partition and subpartition file names include generated
        delimiters such as
   <code class="literal">
    #P#
   </code>
   and
   <code class="literal">
    #SP#
   </code>
   . The lettercase of such delimiters can
        vary and should not be depended upon.
  </p>
 </div>
 <br/>
</div>
