<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="partitioning-range">
      </a>
      26.2.1 RANGE Partitioning
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045084782144">
  </a>
  <a class="indexterm" name="idm46045084780656">
  </a>
  <p>
   A table that is partitioned by range is partitioned in such a
        way that each partition contains rows for which the partitioning
        expression value lies within a given range. Ranges should be
        contiguous but not overlapping, and are defined using the
   <code class="literal">
    VALUES LESS THAN
   </code>
   operator. For the next few
        examples, suppose that you are creating a table such as the
        following to hold personnel records for a chain of 20 video
        stores, numbered 1 through 20:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa7729602"><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">30</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">30</span><span class="token punctuation">)</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 keyword">DEFAULT</span> <span class="token string">'1970-01-01'</span><span class="token punctuation">,</span>
    separated <span class="token datatype">DATE</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token string">'9999-12-31'</span><span class="token punctuation">,</span>
    job_code <span class="token datatype">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    store_id <span class="token datatype">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    The
    <code class="literal">
     employees
    </code>
    table used here has no
          primary or unique keys. While the examples work as shown for
          purposes of the present discussion, you should keep in mind
          that tables are extremely likely in practice to have primary
          keys, unique keys, or both, and that allowable choices for
          partitioning columns depend on the columns used for these
          keys, if any are present. For a discussion of these issues,
          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>
    .
   </p>
  </div>
  <p>
   This table can be partitioned by range in a number of ways,
        depending on your needs. One way would be to use the
   <code class="literal">
    store_id
   </code>
   column. For instance, you might
        decide to partition the table 4 ways by adding a
   <code class="literal">
    PARTITION BY RANGE
   </code>
   clause as shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa80183598"><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">30</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">30</span><span class="token punctuation">)</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 keyword">DEFAULT</span> <span class="token string">'1970-01-01'</span><span class="token punctuation">,</span>
    separated <span class="token datatype">DATE</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token string">'9999-12-31'</span><span class="token punctuation">,</span>
    job_code <span class="token datatype">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    store_id <span class="token datatype">INT</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>store_id<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">6</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">11</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">16</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">21</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   In this partitioning scheme, all rows corresponding to employees
        working at stores 1 through 5 are stored in partition
   <code class="literal">
    p0
   </code>
   , to those employed at stores 6 through 10
        are stored in partition
   <code class="literal">
    p1
   </code>
   , and so on. Each
        partition is defined in order, from lowest to highest. This is a
        requirement of the
   <code class="literal">
    PARTITION BY RANGE
   </code>
   syntax;
        you can think of it as being analogous to a series of
   <code class="literal">
    if ... elseif ...
   </code>
   statements in C or Java in
        this regard.
  </p>
  <p>
   It is easy to determine that a new row containing the data
   <code class="literal">
    (72, 'Mitchell', 'Wilson', '1998-06-25', DEFAULT, 7,
        13)
   </code>
   is inserted into partition
   <code class="literal">
    p2
   </code>
   ,
        but what happens when your chain adds a
        21
   <sup>
    st
   </sup>
   store? Under this scheme, there
        is no rule that covers a row whose
   <code class="literal">
    store_id
   </code>
   is greater than 20, so an error results because the server does
        not know where to place it. You can keep this from occurring by
        using a
   <span class="quote">
    “
    <span class="quote">
     catchall
    </span>
    ”
   </span>
   <code class="literal">
    VALUES LESS
        THAN
   </code>
   clause in the
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE
        TABLE
    </code>
   </a>
   statement that provides for all values greater
        than the highest value explicitly named:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa30795140"><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">30</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">30</span><span class="token punctuation">)</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 keyword">DEFAULT</span> <span class="token string">'1970-01-01'</span><span class="token punctuation">,</span>
    separated <span class="token datatype">DATE</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token string">'9999-12-31'</span><span class="token punctuation">,</span>
    job_code <span class="token datatype">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    store_id <span class="token datatype">INT</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>store_id<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">6</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">11</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">16</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword"></span><em><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 keyword">MAXVALUE</span></em><span class="token keyword"></span>
<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   (As with the other examples in this chapter, we assume that the
        default storage engine is
   <code class="literal">
    InnoDB
   </code>
   .)
  </p>
  <p>
   Another way to avoid an error when no matching value is found is
        to use the
   <code class="literal">
    IGNORE
   </code>
   keyword as part of the
   <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
    <code class="literal">
     INSERT
    </code>
   </a>
   statement. For an example,
        see
   <a class="xref" href="partitioning-list.html" title="26.2.2 LIST Partitioning">
    Section 26.2.2, “LIST Partitioning”
   </a>
   .
  </p>
  <p>
   <code class="literal">
    MAXVALUE
   </code>
   represents an integer value that is
        always greater than the largest possible integer value (in
        mathematical language, it serves as a
   <span class="firstterm">
    least upper bound
   </span>
   ). Now,
        any rows whose
   <code class="literal">
    store_id
   </code>
   column value is
        greater than or equal to 16 (the highest value defined) are
        stored in partition
   <code class="literal">
    p3
   </code>
   . At some point in the
        future—when the number of stores has increased to 25, 30,
        or more—you can use an
   <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 to add new partitions for stores
        21-25, 26-30, and so on (see
   <a class="xref" href="partitioning-management.html" title="26.3 Partition Management">
    Section 26.3, “Partition Management”
   </a>
   , for details of how to
        do this).
  </p>
  <p>
   In much the same fashion, you could partition the table based on
        employee job codes—that is, based on ranges of
   <code class="literal">
    job_code
   </code>
   column values. For
        example—assuming that two-digit job codes are used for
        regular (in-store) workers, three-digit codes are used for
        office and support personnel, and four-digit codes are used for
        management positions—you could create the partitioned
        table using the following statement:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa4771991"><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">30</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">30</span><span class="token punctuation">)</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 keyword">DEFAULT</span> <span class="token string">'1970-01-01'</span><span class="token punctuation">,</span>
    separated <span class="token datatype">DATE</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token string">'9999-12-31'</span><span class="token punctuation">,</span>
    job_code <span class="token datatype">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    store_id <span class="token datatype">INT</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>job_code<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">100</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">1000</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">10000</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   In this instance, all rows relating to in-store workers would be
        stored in partition
   <code class="literal">
    p0
   </code>
   , those relating to
        office and support staff in
   <code class="literal">
    p1
   </code>
   , and those
        relating to managers in partition
   <code class="literal">
    p2
   </code>
   .
  </p>
  <p>
   It is also possible to use an expression in
   <code class="literal">
    VALUES LESS
        THAN
   </code>
   clauses. However, MySQL must be able to evaluate
        the expression's return value as part of a
   <code class="literal">
    LESS
        THAN
   </code>
   (
   <code class="literal">
    &lt;
   </code>
   ) comparison.
  </p>
  <a class="indexterm" name="idm46045084740592">
  </a>
  <p>
   Rather than splitting up the table data according to store
        number, you can use an expression based on one of the two
   <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
    <code class="literal">
     DATE
    </code>
   </a>
   columns instead. For
        example, let us suppose that you wish to partition based on the
        year that each employee left the company; that is, the value of
   <a class="link" href="date-and-time-functions.html#function_year">
    <code class="literal">
     YEAR(separated)
    </code>
   </a>
   . An example of a
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE TABLE
    </code>
   </a>
   statement that
        implements such a partitioning scheme is shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa58045171"><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">30</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">30</span><span class="token punctuation">)</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 keyword">DEFAULT</span> <span class="token string">'1970-01-01'</span><span class="token punctuation">,</span>
    separated <span class="token datatype">DATE</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token string">'9999-12-31'</span><span class="token punctuation">,</span>
    job_code <span class="token datatype">INT</span><span class="token punctuation">,</span>
    store_id <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">RANGE</span> <span class="token punctuation">(</span> <span class="token function">YEAR</span><span class="token punctuation">(</span>separated<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">1991</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">1996</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">2001</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 keyword">MAXVALUE</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   In this scheme, for all employees who left before 1991, the rows
        are stored in partition
   <code class="literal">
    p0
   </code>
   ; for those who
        left in the years 1991 through 1995, in
   <code class="literal">
    p1
   </code>
   ;
        for those who left in the years 1996 through 2000, in
   <code class="literal">
    p2
   </code>
   ; and for any workers who left after the
        year 2000, in
   <code class="literal">
    p3
   </code>
   .
  </p>
  <p>
   It is also possible to partition a table by
   <code class="literal">
    RANGE
   </code>
   , based on the value of a
   <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
    <code class="literal">
     TIMESTAMP
    </code>
   </a>
   column, using the
   <a class="link" href="date-and-time-functions.html#function_unix-timestamp">
    <code class="literal">
     UNIX_TIMESTAMP()
    </code>
   </a>
   function, as
        shown in this example:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa33777354"><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> quarterly_report_status <span class="token punctuation">(</span>
    report_id <span class="token datatype">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    report_status <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    report_updated <span class="token datatype">TIMESTAMP</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token keyword">CURRENT_TIMESTAMP</span> <span class="token keyword">ON</span> <span class="token keyword">UPDATE</span> <span class="token keyword">CURRENT_TIMESTAMP</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">UNIX_TIMESTAMP</span><span class="token punctuation">(</span>report_updated<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 function">UNIX_TIMESTAMP</span><span class="token punctuation">(</span><span class="token string">'2008-01-01 00:00:00'</span><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 function">UNIX_TIMESTAMP</span><span class="token punctuation">(</span><span class="token string">'2008-04-01 00:00:00'</span><span class="token punctuation">)</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 function">UNIX_TIMESTAMP</span><span class="token punctuation">(</span><span class="token string">'2008-07-01 00:00:00'</span><span class="token punctuation">)</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 function">UNIX_TIMESTAMP</span><span class="token punctuation">(</span><span class="token string">'2008-10-01 00:00:00'</span><span class="token punctuation">)</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 function">UNIX_TIMESTAMP</span><span class="token punctuation">(</span><span class="token string">'2009-01-01 00:00:00'</span><span class="token punctuation">)</span> <span class="token punctuation">)</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 function">UNIX_TIMESTAMP</span><span class="token punctuation">(</span><span class="token string">'2009-04-01 00:00:00'</span><span class="token punctuation">)</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 punctuation">(</span> <span class="token function">UNIX_TIMESTAMP</span><span class="token punctuation">(</span><span class="token string">'2009-07-01 00:00:00'</span><span class="token punctuation">)</span> <span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">PARTITION</span> p7 <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 function">UNIX_TIMESTAMP</span><span class="token punctuation">(</span><span class="token string">'2009-10-01 00:00:00'</span><span class="token punctuation">)</span> <span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">PARTITION</span> p8 <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 function">UNIX_TIMESTAMP</span><span class="token punctuation">(</span><span class="token string">'2010-01-01 00:00:00'</span><span class="token punctuation">)</span> <span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">PARTITION</span> p9 <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></code></pre>
  </div>
  <p>
   Any other expressions involving
   <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
    <code class="literal">
     TIMESTAMP
    </code>
   </a>
   values are not
        permitted. (See Bug #42849.)
  </p>
  <p>
   Range partitioning is particularly useful when one or more of
        the following conditions is true:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      You want or need to delete
      <span class="quote">
       “
       <span class="quote">
        old
       </span>
       ”
      </span>
      data. If you
            are using the partitioning scheme shown previously for the
      <code class="literal">
       employees
      </code>
      table, you can simply use
      <code class="literal">
       ALTER TABLE employees DROP PARTITION p0;
      </code>
      to delete all rows relating to employees who stopped working
            for the firm prior to 1991. (See
      <a class="xref" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
       Section 15.1.9, “ALTER TABLE Statement”
      </a>
      , and
      <a class="xref" href="partitioning-management.html" title="26.3 Partition Management">
       Section 26.3, “Partition Management”
      </a>
      , for more
            information.) For a table with a great many rows, this can
            be much more efficient than running a
      <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
       <code class="literal">
        DELETE
       </code>
      </a>
      query such as
      <code class="literal">
       DELETE FROM employees WHERE YEAR(separated) &lt;=
            1990;
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      You want to use a column containing date or time values, or
            containing values arising from some other series.
     </p>
    </li>
    <li class="listitem">
     <p>
      You frequently run queries that depend directly on the
            column used for partitioning the table. For example, when
            executing a query such as
      <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
       <code class="literal">
        EXPLAIN SELECT
            COUNT(*) FROM employees WHERE separated BETWEEN '2000-01-01'
            AND '2000-12-31' GROUP BY store_id;
       </code>
      </a>
      , MySQL can
            quickly determine that only partition
      <code class="literal">
       p2
      </code>
      needs to be scanned because the remaining partitions cannot
            contain any records satisfying the
      <code class="literal">
       WHERE
      </code>
      clause. See
      <a class="xref" href="partitioning-pruning.html" title="26.4 Partition Pruning">
       Section 26.4, “Partition Pruning”
      </a>
      , for more
            information about how this is accomplished.
     </p>
    </li>
   </ul>
  </div>
  <p>
   A variant on this type of partitioning is
   <code class="literal">
    RANGE
        COLUMNS
   </code>
   partitioning. Partitioning by
   <code class="literal">
    RANGE
        COLUMNS
   </code>
   makes it possible to employ multiple columns
        for defining partitioning ranges that apply both to placement of
        rows in partitions and for determining the inclusion or
        exclusion of specific partitions when performing partition
        pruning. 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>
   , for
        more information.
  </p>
  <p>
   <a name="partitioning-time-intervals">
   </a>
   <b>
    Partitioning schemes based on time intervals.
   </b>
   If you wish to implement a partitioning scheme based on ranges
          or intervals of time in MySQL 8.4, you have two
          options:
  </p>
  <div class="orderedlist">
   <ol class="orderedlist" type="1">
    <li class="listitem">
     <p>
      Partition the table by
      <code class="literal">
       RANGE
      </code>
      , and for the
            partitioning expression, employ a function operating 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 returning
            an integer value, as shown here:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa7635592"><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>
      In MySQL 8.4, it is also possible to partition
            a table by
      <code class="literal">
       RANGE
      </code>
      based on the value of a
      <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
       <code class="literal">
        TIMESTAMP
       </code>
      </a>
      column, using the
      <a class="link" href="date-and-time-functions.html#function_unix-timestamp">
       <code class="literal">
        UNIX_TIMESTAMP()
       </code>
      </a>
      function, as
            shown in this example:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa24610061"><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> quarterly_report_status <span class="token punctuation">(</span>
    report_id <span class="token datatype">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    report_status <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    report_updated <span class="token datatype">TIMESTAMP</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token keyword">CURRENT_TIMESTAMP</span> <span class="token keyword">ON</span> <span class="token keyword">UPDATE</span> <span class="token keyword">CURRENT_TIMESTAMP</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">UNIX_TIMESTAMP</span><span class="token punctuation">(</span>report_updated<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 function">UNIX_TIMESTAMP</span><span class="token punctuation">(</span><span class="token string">'2008-01-01 00:00:00'</span><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 function">UNIX_TIMESTAMP</span><span class="token punctuation">(</span><span class="token string">'2008-04-01 00:00:00'</span><span class="token punctuation">)</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 function">UNIX_TIMESTAMP</span><span class="token punctuation">(</span><span class="token string">'2008-07-01 00:00:00'</span><span class="token punctuation">)</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 function">UNIX_TIMESTAMP</span><span class="token punctuation">(</span><span class="token string">'2008-10-01 00:00:00'</span><span class="token punctuation">)</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 function">UNIX_TIMESTAMP</span><span class="token punctuation">(</span><span class="token string">'2009-01-01 00:00:00'</span><span class="token punctuation">)</span> <span class="token punctuation">)</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 function">UNIX_TIMESTAMP</span><span class="token punctuation">(</span><span class="token string">'2009-04-01 00:00:00'</span><span class="token punctuation">)</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 punctuation">(</span> <span class="token function">UNIX_TIMESTAMP</span><span class="token punctuation">(</span><span class="token string">'2009-07-01 00:00:00'</span><span class="token punctuation">)</span> <span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">PARTITION</span> p7 <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 function">UNIX_TIMESTAMP</span><span class="token punctuation">(</span><span class="token string">'2009-10-01 00:00:00'</span><span class="token punctuation">)</span> <span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">PARTITION</span> p8 <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 function">UNIX_TIMESTAMP</span><span class="token punctuation">(</span><span class="token string">'2010-01-01 00:00:00'</span><span class="token punctuation">)</span> <span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">PARTITION</span> p9 <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></code></pre>
     </div>
     <p>
      In MySQL 8.4, any other expressions involving
      <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
       <code class="literal">
        TIMESTAMP
       </code>
      </a>
      values are not
            permitted. (See Bug #42849.)
     </p>
     <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
      <div class="admon-title">
       Note
      </div>
      <p>
       It is also possible in MySQL 8.4 to use
       <a class="link" href="date-and-time-functions.html#function_unix-timestamp">
        <code class="literal">
         UNIX_TIMESTAMP(timestamp_column)
        </code>
       </a>
       as a partitioning expression for tables that are
              partitioned by
       <code class="literal">
        LIST
       </code>
       . However, it is
              usually not practical to do so.
      </p>
     </div>
    </li>
    <li class="listitem">
     <p>
      Partition the table by
      <code class="literal">
       RANGE COLUMNS
      </code>
      ,
            using 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. For example, the
      <code class="literal">
       members
      </code>
      table could be defined using the
      <code class="literal">
       joined
      </code>
      column directly, as shown here:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa20772030"><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 keyword">COLUMNS</span><span class="token punctuation">(</span>joined<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">'1960-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">'1970-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">'1980-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">'1990-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 keyword">MAXVALUE</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
     </div>
    </li>
   </ol>
  </div>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    The use of partitioning columns employing date or time types
          other than
    <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>
    is not supported with
    <code class="literal">
     RANGE COLUMNS
    </code>
    .
   </p>
  </div>
 </div>
 <br/>
</div>
