<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h2 class="title">
      <a name="partitioning-types">
      </a>
      26.2 Partitioning Types
     </h2>
    </div>
   </div>
  </div>
  <div class="toc">
   <dl class="toc">
    <dt>
     <span class="section">
      <a href="partitioning-range.html">
       26.2.1 RANGE Partitioning
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="partitioning-list.html">
       26.2.2 LIST Partitioning
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="partitioning-columns.html">
       26.2.3 COLUMNS Partitioning
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="partitioning-hash.html">
       26.2.4 HASH Partitioning
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="partitioning-key.html">
       26.2.5 KEY Partitioning
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="partitioning-subpartitions.html">
       26.2.6 Subpartitioning
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="partitioning-handling-nulls.html">
       26.2.7 How MySQL Partitioning Handles NULL
      </a>
     </span>
    </dt>
   </dl>
  </div>
  <a class="indexterm" name="idm46045084868544">
  </a>
  <p>
   This section discusses the types of partitioning which are
      available in MySQL 8.4. These include the types
      listed here:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <b>
       RANGE partitioning.
      </b>
      This type of partitioning assigns rows to partitions based
            on column values falling within a given range. See
      <a class="xref" href="partitioning-range.html" title="26.2.1 RANGE Partitioning">
       Section 26.2.1, “RANGE Partitioning”
      </a>
      . For information about
            an extension to this type,
      <code class="literal">
       RANGE COLUMNS
      </code>
      ,
            see
      <a class="xref" href="partitioning-columns-range.html" title="26.2.3.1 RANGE COLUMNS partitioning">
       Section 26.2.3.1, “RANGE COLUMNS partitioning”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <b>
       LIST partitioning.
      </b>
      Similar to partitioning by
      <code class="literal">
       RANGE
      </code>
      , except
            that the partition is selected based on columns matching one
            of a set of discrete values. See
      <a class="xref" href="partitioning-list.html" title="26.2.2 LIST Partitioning">
       Section 26.2.2, “LIST Partitioning”
      </a>
      . For information about
            an extension to this type,
      <code class="literal">
       LIST COLUMNS
      </code>
      ,
            see
      <a class="xref" href="partitioning-columns-list.html" title="26.2.3.2 LIST COLUMNS partitioning">
       Section 26.2.3.2, “LIST COLUMNS partitioning”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <b>
       HASH partitioning.
      </b>
      With this type of partitioning, a partition is selected
            based on the value returned by a user-defined expression
            that operates on column values in rows to be inserted into
            the table. The function may consist of any expression valid
            in MySQL that yields an integer value. See
      <a class="xref" href="partitioning-hash.html" title="26.2.4 HASH Partitioning">
       Section 26.2.4, “HASH Partitioning”
      </a>
      .
     </p>
     <p>
      An extension to this type,
      <code class="literal">
       LINEAR HASH
      </code>
      , is
          also available, see
      <a class="xref" href="partitioning-linear-hash.html" title="26.2.4.1 LINEAR HASH Partitioning">
       Section 26.2.4.1, “LINEAR HASH Partitioning”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <b>
       KEY partitioning.
      </b>
      This type of partitioning is similar to partitioning by
      <code class="literal">
       HASH
      </code>
      , except that only one or more
            columns to be evaluated are supplied, and the MySQL server
            provides its own hashing function. These columns can contain
            other than integer values, since the hashing function
            supplied by MySQL guarantees an integer result regardless of
            the column data type. An extension to this type,
      <code class="literal">
       LINEAR KEY
      </code>
      , is also available. See
      <a class="xref" href="partitioning-key.html" title="26.2.5 KEY Partitioning">
       Section 26.2.5, “KEY Partitioning”
      </a>
      .
     </p>
    </li>
   </ul>
  </div>
  <a class="indexterm" name="idm46045084848752">
  </a>
  <a class="indexterm" name="idm46045084847264">
  </a>
  <p>
   A very common use of database partitioning is to segregate data by
      date. Some database systems support explicit date partitioning,
      which MySQL does not implement in 8.4. However, it is
      not difficult in MySQL to create partitioning schemes based on
   <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
    <code class="literal">
     DATE
    </code>
   </a>
   ,
   <a class="link" href="time.html" title="13.2.3 The TIME Type">
    <code class="literal">
     TIME
    </code>
   </a>
   , or
   <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
    <code class="literal">
     DATETIME
    </code>
   </a>
   columns, or based on
      expressions making use of such columns.
  </p>
  <p>
   When partitioning by
   <code class="literal">
    KEY
   </code>
   or
   <code class="literal">
    LINEAR
      KEY
   </code>
   , you can use a
   <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
    <code class="literal">
     DATE
    </code>
   </a>
   ,
   <a class="link" href="time.html" title="13.2.3 The TIME Type">
    <code class="literal">
     TIME
    </code>
   </a>
   , or
   <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
    <code class="literal">
     DATETIME
    </code>
   </a>
   column as the partitioning
      column without performing any modification of the column value.
      For example, this table creation statement is perfectly valid in
      MySQL:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa53486086"><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>
    firstname <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 operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    lastname <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 operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    username <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">16</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    email <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">35</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    joined <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">KEY</span><span class="token punctuation">(</span>joined<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>
  <p>
   In MySQL 8.4, it is also possible to use a
   <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
    <code class="literal">
     DATE
    </code>
   </a>
   or
   <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
    <code class="literal">
     DATETIME
    </code>
   </a>
   column as the partitioning
      column using
   <code class="literal">
    RANGE COLUMNS
   </code>
   and
   <code class="literal">
    LIST
      COLUMNS
   </code>
   partitioning.
  </p>
  <p>
   Other partitioning types require a partitioning expression that
      yields an integer value or
   <code class="literal">
    NULL
   </code>
   . If you wish to
      use date-based partitioning by
   <code class="literal">
    RANGE
   </code>
   ,
   <code class="literal">
    LIST
   </code>
   ,
   <code class="literal">
    HASH
   </code>
   , or
   <code class="literal">
    LINEAR HASH
   </code>
   , you can simply employ a function
      that operates on a
   <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
    <code class="literal">
     DATE
    </code>
   </a>
   ,
   <a class="link" href="time.html" title="13.2.3 The TIME Type">
    <code class="literal">
     TIME
    </code>
   </a>
   , or
   <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
    <code class="literal">
     DATETIME
    </code>
   </a>
   column and returns such a
      value, as shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa4048087"><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>
    firstname <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 operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    lastname <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 operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    username <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">16</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    email <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">35</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    joined <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>joined<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">1960</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">1970</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">1980</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">1990</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 keyword">MAXVALUE</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Additional examples of partitioning using dates may be found in
      the following sections of this chapter:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="xref" href="partitioning-range.html" title="26.2.1 RANGE Partitioning">
       Section 26.2.1, “RANGE Partitioning”
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="partitioning-hash.html" title="26.2.4 HASH Partitioning">
       Section 26.2.4, “HASH Partitioning”
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="partitioning-linear-hash.html" title="26.2.4.1 LINEAR HASH Partitioning">
       Section 26.2.4.1, “LINEAR HASH Partitioning”
      </a>
     </p>
    </li>
   </ul>
  </div>
  <p>
   For more complex examples of date-based partitioning, see the
      following sections:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="xref" href="partitioning-pruning.html" title="26.4 Partition Pruning">
       Section 26.4, “Partition Pruning”
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="partitioning-subpartitions.html" title="26.2.6 Subpartitioning">
       Section 26.2.6, “Subpartitioning”
      </a>
     </p>
    </li>
   </ul>
  </div>
  <p>
   MySQL partitioning is optimized for use with the
   <a class="link" href="date-and-time-functions.html#function_to-days">
    <code class="literal">
     TO_DAYS()
    </code>
   </a>
   ,
   <a class="link" href="date-and-time-functions.html#function_year">
    <code class="literal">
     YEAR()
    </code>
   </a>
   , and
   <a class="link" href="date-and-time-functions.html#function_to-seconds">
    <code class="literal">
     TO_SECONDS()
    </code>
   </a>
   functions. However,
      you can use other date and time functions that return an integer
      or
   <code class="literal">
    NULL
   </code>
   , such as
   <a class="link" href="date-and-time-functions.html#function_weekday">
    <code class="literal">
     WEEKDAY()
    </code>
   </a>
   ,
   <a class="link" href="date-and-time-functions.html#function_dayofyear">
    <code class="literal">
     DAYOFYEAR()
    </code>
   </a>
   , or
   <a class="link" href="date-and-time-functions.html#function_month">
    <code class="literal">
     MONTH()
    </code>
   </a>
   . See
   <a class="xref" href="date-and-time-functions.html" title="14.7 Date and Time Functions">
    Section 14.7, “Date and Time Functions”
   </a>
   , for more information
      about such functions.
  </p>
  <p>
   It is important to remember—regardless of the type of
      partitioning that you use—that partitions are always
      numbered automatically and in sequence when created, starting with
   <code class="literal">
    0
   </code>
   . When a new row is inserted into a
      partitioned table, it is these partition numbers that are used in
      identifying the correct partition. For example, if your table uses
      4 partitions, these partitions are numbered
   <code class="literal">
    0
   </code>
   ,
   <code class="literal">
    1
   </code>
   ,
   <code class="literal">
    2
   </code>
   , and
   <code class="literal">
    3
   </code>
   . For the
   <code class="literal">
    RANGE
   </code>
   and
   <code class="literal">
    LIST
   </code>
   partitioning types, it is necessary to
      ensure that there is a partition defined for each partition
      number. For
   <code class="literal">
    HASH
   </code>
   partitioning, the
      user-supplied expression must evaluate to an integer value. For
   <code class="literal">
    KEY
   </code>
   partitioning, this issue is taken care of
      automatically by the hashing function which the MySQL server
      employs internally.
  </p>
  <p>
   Names of partitions generally follow the rules governing other
      MySQL identifiers, such as those for tables and databases.
      However, you should note that partition names are not
      case-sensitive. For example, the following
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE TABLE
    </code>
   </a>
   statement fails as
      shown:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa3271456"><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> t2 <span class="token punctuation">(</span>val <span class="token datatype">INT</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">LIST</span><span class="token punctuation">(</span>val<span class="token punctuation">)</span><span class="token punctuation">(</span>
<span class="token prompt">    -&gt;</span>     <span class="token keyword">PARTITION</span> mypart <span class="token keyword">VALUES</span> <span class="token keyword">IN</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token number">3</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> MyPart <span class="token keyword">VALUES</span> <span class="token keyword">IN</span> <span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token number">4</span><span class="token punctuation">,</span><span class="token number">6</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 1488 (HY000)<span class="token punctuation">:</span> Duplicate partition name mypart</span></code></pre>
  </div>
  <p>
   Failure occurs because MySQL sees no difference between the
      partition names
   <code class="literal">
    mypart
   </code>
   and
   <code class="literal">
    MyPart
   </code>
   .
  </p>
  <p>
   When you specify the number of partitions for the table, this must
      be expressed as a positive, nonzero integer literal with no
      leading zeros, and may not be an expression such as
   <code class="literal">
    0.8E+01
   </code>
   or
   <code class="literal">
    6-2
   </code>
   , even if it
      evaluates to an integer value. Decimal fractions are not
      permitted.
  </p>
  <p>
   In the sections that follow, we do not necessarily provide all
      possible forms for the syntax that can be used for creating each
      partition type; for this information, see
   <a class="xref" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    Section 15.1.20, “CREATE TABLE Statement”
   </a>
   .
  </p>
 </div>
 <br/>
</div>
