<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="partitioning-columns-range">
      </a>
      26.2.3.1 RANGE COLUMNS partitioning
     </h4>
    </div>
   </div>
  </div>
  <p>
   Range columns partitioning is similar to range partitioning,
          but enables you to define partitions using ranges based on
          multiple column values. In addition, you can define the ranges
          using columns of types other than integer types.
  </p>
  <p>
   <code class="literal">
    RANGE COLUMNS
   </code>
   partitioning differs
          significantly from
   <code class="literal">
    RANGE
   </code>
   partitioning in
          the following ways:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       RANGE COLUMNS
      </code>
      does not accept
              expressions, only names of columns.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       RANGE COLUMNS
      </code>
      accepts a list of one or
              more columns.
     </p>
     <p>
      <code class="literal">
       RANGE COLUMNS
      </code>
      partitions are based on
              comparisons between
      <span class="firstterm">
       tuples
      </span>
      (lists of
              column values) rather than comparisons between scalar
              values. Placement of rows in
      <code class="literal">
       RANGE
              COLUMNS
      </code>
      partitions is also based on comparisons
              between tuples; this is discussed further later in this
              section.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       RANGE COLUMNS
      </code>
      partitioning columns are
              not restricted to integer columns; 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 can also
              be used as partitioning columns. (See
      <a class="xref" href="partitioning-columns.html" title="26.2.3 COLUMNS Partitioning">
       Section 26.2.3, “COLUMNS Partitioning”
      </a>
      , for details.)
     </p>
    </li>
   </ul>
  </div>
  <p>
   The basic syntax for creating a table partitioned by
   <code class="literal">
    RANGE COLUMNS
   </code>
   is shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa24534008"><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> <span class="token keyword"><em class="replaceable">table_name</em></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><em class="replaceable">column_list</em><span class="token punctuation">)</span> <span class="token punctuation">(</span>
    <span class="token keyword">PARTITION</span> <em class="replaceable">partition_name</em> <span class="token keyword">VALUES</span> <span class="token keyword">LESS</span> <span class="token keyword">THAN</span> <span class="token punctuation">(</span><em class="replaceable">value_list</em><span class="token punctuation">)</span><span class="token punctuation">[</span><span class="token punctuation">,</span>
    <span class="token keyword">PARTITION</span> <em class="replaceable">partition_name</em> <span class="token keyword">VALUES</span> <span class="token keyword">LESS</span> <span class="token keyword">THAN</span> <span class="token punctuation">(</span><em class="replaceable">value_list</em><span class="token punctuation">)</span><span class="token punctuation">]</span><span class="token punctuation">[</span><span class="token punctuation">,</span>
    <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">]</span>
<span class="token punctuation">)</span>

<em class="replaceable">column_list</em>:
    <span class="token keyword"><em class="replaceable">column_name</em></span><span class="token punctuation">[</span><span class="token punctuation">,</span> <span class="token keyword"><em class="replaceable">column_name</em></span><span class="token punctuation">]</span><span class="token punctuation">[</span><span class="token punctuation">,</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">]</span>

<em class="replaceable">value_list</em>:
    <span class="token keyword"><em class="replaceable">value</em></span><span class="token punctuation">[</span><span class="token punctuation">,</span> <span class="token keyword"><em class="replaceable">value</em></span><span class="token punctuation">]</span><span class="token punctuation">[</span><span class="token punctuation">,</span> <span class="token punctuation">.</span><span class="token punctuation">.</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>
    Not all
    <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
     <code class="literal">
      CREATE TABLE
     </code>
    </a>
    options
            that can be used when creating partitioned tables are shown
            here. For complete 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>
  <p>
   In the syntax just shown,
   <em class="replaceable">
    <code>
     column_list
    </code>
   </em>
   is a list of one or
          more columns (sometimes called a
   <span class="firstterm">
    partitioning column
          list
   </span>
   ), and
   <em class="replaceable">
    <code>
     value_list
    </code>
   </em>
   is
          a list of values (that is, it is a
   <span class="firstterm">
    partition definition value
          list
   </span>
   ). A
   <em class="replaceable">
    <code>
     value_list
    </code>
   </em>
   must
          be supplied for each partition definition, and each
   <em class="replaceable">
    <code>
     value_list
    </code>
   </em>
   must have the same
          number of values as the
   <em class="replaceable">
    <code>
     column_list
    </code>
   </em>
   has columns. Generally speaking, if you use
   <em class="replaceable">
    <code>
     N
    </code>
   </em>
   columns in the
   <code class="literal">
    COLUMNS
   </code>
   clause, then each
   <code class="literal">
    VALUES
          LESS THAN
   </code>
   clause must also be supplied with a list
          of
   <em class="replaceable">
    <code>
     N
    </code>
   </em>
   values.
  </p>
  <p>
   The elements in the partitioning column list and in the value
          list defining each partition must occur in the same order. In
          addition, each element in the value list must be of the same
          data type as the corresponding element in the column list.
          However, the order of the column names in the partitioning
          column list and the value lists does not have to be the same
          as the order of the table column definitions in the main part
          of the
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE TABLE
    </code>
   </a>
   statement.
          As with table partitioned by
   <code class="literal">
    RANGE
   </code>
   , you can
          use
   <code class="literal">
    MAXVALUE
   </code>
   to represent a value such that
          any legal value inserted into a given column is always less
          than this value. Here is 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
          helps to illustrate all of these points:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa89092468"><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> rcx <span class="token punctuation">(</span>
<span class="token prompt">    -&gt;</span>     a <span class="token datatype">INT</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>     b <span class="token datatype">INT</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>     c <span class="token datatype">CHAR</span><span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>     d <span class="token datatype">INT</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 keyword">COLUMNS</span><span class="token punctuation">(</span>a<span class="token punctuation">,</span>d<span class="token punctuation">,</span>c<span class="token punctuation">)</span> <span class="token punctuation">(</span>
<span class="token prompt">    -&gt;</span>     <span class="token keyword">PARTITION</span> p0 <span class="token keyword">VALUES</span> <span class="token keyword">LESS</span> <span class="token keyword">THAN</span> <span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">,</span><span class="token number">10</span><span class="token punctuation">,</span><span class="token string">'ggg'</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">10</span><span class="token punctuation">,</span><span class="token number">20</span><span class="token punctuation">,</span><span class="token string">'mmm'</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">15</span><span class="token punctuation">,</span><span class="token number">30</span><span class="token punctuation">,</span><span class="token string">'sss'</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 keyword">MAXVALUE</span><span class="token punctuation">,</span><span class="token keyword">MAXVALUE</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.15 sec)</span></code></pre>
  </div>
  <p>
   Table
   <code class="literal">
    rcx
   </code>
   contains the columns
   <code class="literal">
    a
   </code>
   ,
   <code class="literal">
    b
   </code>
   ,
   <code class="literal">
    c
   </code>
   ,
   <code class="literal">
    d
   </code>
   . The partitioning
          column list supplied to the
   <code class="literal">
    COLUMNS
   </code>
   clause
          uses 3 of these columns, in the order
   <code class="literal">
    a
   </code>
   ,
   <code class="literal">
    d
   </code>
   ,
   <code class="literal">
    c
   </code>
   . Each value list
          used to define a partition contains 3 values in the same
          order; that is, each value list tuple has the form
          (
   <code class="literal">
    INT
   </code>
   ,
   <code class="literal">
    INT
   </code>
   ,
   <code class="literal">
    CHAR(3)
   </code>
   ), which corresponds to the data
          types used by columns
   <code class="literal">
    a
   </code>
   ,
   <code class="literal">
    d
   </code>
   , and
   <code class="literal">
    c
   </code>
   (in that
          order).
  </p>
  <p>
   Placement of rows into partitions is determined by comparing
          the tuple from a row to be inserted that matches the column
          list in the
   <code class="literal">
    COLUMNS
   </code>
   clause with the tuples
          used in the
   <code class="literal">
    VALUES LESS THAN
   </code>
   clauses to
          define partitions of the table. Because we are comparing
          tuples (that is, lists or sets of values) rather than scalar
          values, the semantics of
   <code class="literal">
    VALUES LESS THAN
   </code>
   as used with
   <code class="literal">
    RANGE COLUMNS
   </code>
   partitions
          differs somewhat from the case with simple
   <code class="literal">
    RANGE
   </code>
   partitions. In
   <code class="literal">
    RANGE
   </code>
   partitioning, a row generating an
          expression value that is equal to a limiting value in a
   <code class="literal">
    VALUES LESS THAN
   </code>
   is never placed in the
          corresponding partition; however, when using
   <code class="literal">
    RANGE
          COLUMNS
   </code>
   partitioning, it is sometimes possible for a
          row whose partitioning column list's first element is
          equal in value to the that of the first element in a
   <code class="literal">
    VALUES LESS THAN
   </code>
   value list to be placed in
          the corresponding partition.
  </p>
  <p>
   Consider the
   <code class="literal">
    RANGE
   </code>
   partitioned table
          created by this statement:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa72772758"><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> r1 <span class="token punctuation">(</span>
    a <span class="token datatype">INT</span><span class="token punctuation">,</span>
    b <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>a<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">5</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 keyword">MAXVALUE</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   If we insert 3 rows into this table such that the column value
          for
   <code class="literal">
    a
   </code>
   is
   <code class="literal">
    5
   </code>
   for each row,
          all 3 rows are stored in partition
   <code class="literal">
    p1
   </code>
   because the
   <code class="literal">
    a
   </code>
   column value is in each case
          not less than 5, as we can see by executing the proper query
          against the Information Schema
   <a class="link" href="information-schema-partitions-table.html" title="28.3.21 The INFORMATION_SCHEMA PARTITIONS Table">
    <code class="literal">
     PARTITIONS
    </code>
   </a>
   table:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa21516889"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> r1 <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">,</span><span class="token number">10</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token number">5</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 punctuation">(</span><span class="token number">5</span><span class="token punctuation">,</span><span class="token number">12</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 3 rows affected (0.00 sec)</span>
<span class="token output">Records: 3  Duplicates: 0  Warnings: 0</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> PARTITION_NAME<span class="token punctuation">,</span> TABLE_ROWS
<span class="token prompt">    -&gt;</span>     <span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span><span class="token keyword">PARTITIONS</span>
<span class="token prompt">    -&gt;</span>     <span class="token keyword">WHERE</span> <span class="token keyword">TABLE_NAME</span> <span class="token operator">=</span> <span class="token string">'r1'</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> PARTITION_NAME <span class="token punctuation">|</span> TABLE_ROWS <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> p0             <span class="token punctuation">|</span>          0 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> p1             <span class="token punctuation">|</span>          3 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">2 rows in set (0.00 sec)</span></code></pre>
  </div>
  <p>
   Now consider a similar table
   <code class="literal">
    rc1
   </code>
   that uses
   <code class="literal">
    RANGE COLUMNS
   </code>
   partitioning with both
          columns
   <code class="literal">
    a
   </code>
   and
   <code class="literal">
    b
   </code>
   referenced in the
   <code class="literal">
    COLUMNS
   </code>
   clause, created
          as shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa57220079"><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> rc1 <span class="token punctuation">(</span>
    a <span class="token datatype">INT</span><span class="token punctuation">,</span>
    b <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 keyword">COLUMNS</span><span class="token punctuation">(</span>a<span class="token punctuation">,</span> b<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">5</span><span class="token punctuation">,</span> <span class="token number">12</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token 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 keyword">MAXVALUE</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>
   If we insert exactly the same rows into
   <code class="literal">
    rc1
   </code>
   as we just inserted into
   <code class="literal">
    r1
   </code>
   , the
          distribution of the rows is quite different:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa34260857"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> rc1 <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">,</span><span class="token number">10</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token number">5</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 punctuation">(</span><span class="token number">5</span><span class="token punctuation">,</span><span class="token number">12</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 3 rows affected (0.00 sec)</span>
<span class="token output">Records: 3  Duplicates: 0  Warnings: 0</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> PARTITION_NAME<span class="token punctuation">,</span> TABLE_ROWS
<span class="token prompt">    -&gt;</span>     <span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span><span class="token keyword">PARTITIONS</span>
<span class="token prompt">    -&gt;</span>     <span class="token keyword">WHERE</span> <span class="token keyword">TABLE_NAME</span> <span class="token operator">=</span> <span class="token string">'rc1'</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> PARTITION_NAME <span class="token punctuation">|</span> TABLE_ROWS <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> p0             <span class="token punctuation">|</span>          2 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> p3             <span class="token punctuation">|</span>          1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">2 rows in set (0.00 sec)</span></code></pre>
  </div>
  <p>
   This is because we are comparing rows rather than scalar
          values. We can compare the row values inserted with the
          limiting row value from the
   <code class="literal">
    VALUES THAN LESS
          THAN
   </code>
   clause used to define partition
   <code class="literal">
    p0
   </code>
   in table
   <code class="literal">
    rc1
   </code>
   , like
          this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa93357089"><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 punctuation">(</span><span class="token number">5</span><span class="token punctuation">,</span><span class="token number">10</span><span class="token punctuation">)</span> <span class="token operator">&lt;</span> <span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">,</span><span class="token number">12</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">,</span><span class="token number">11</span><span class="token punctuation">)</span> <span class="token operator">&lt;</span> <span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">,</span><span class="token number">12</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">,</span><span class="token number">12</span><span class="token punctuation">)</span> <span class="token operator">&lt;</span> <span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">,</span><span class="token number">12</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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> (5,10) &lt; (5,12) <span class="token punctuation">|</span> (5,11) &lt; (5,12) <span class="token punctuation">|</span> (5,12) &lt; (5,12) <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>               1 <span class="token punctuation">|</span>               1 <span class="token punctuation">|</span>               0 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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></code></pre>
  </div>
  <p>
   The 2 tuples
   <code class="literal">
    (5,10)
   </code>
   and
   <code class="literal">
    (5,11)
   </code>
   evaluate as less than
   <code class="literal">
    (5,12)
   </code>
   , so they are stored in partition
   <code class="literal">
    p0
   </code>
   . Since 5 is not less than 5 and 12 is
          not less than 12,
   <code class="literal">
    (5,12)
   </code>
   is considered not
          less than
   <code class="literal">
    (5,12)
   </code>
   , and is stored in
          partition
   <code class="literal">
    p1
   </code>
   .
  </p>
  <p>
   The
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   statement in the
          preceding example could also have been written using explicit
          row constructors, like this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa92155890"><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">SELECT</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">,</span><span class="token number">10</span><span class="token punctuation">)</span> <span class="token operator">&lt;</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">,</span><span class="token number">12</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">,</span><span class="token number">11</span><span class="token punctuation">)</span> <span class="token operator">&lt;</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">,</span><span class="token number">12</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">,</span><span class="token number">12</span><span class="token punctuation">)</span> <span class="token operator">&lt;</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">,</span><span class="token number">12</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   For more information about the use of row constructors in
          MySQL, see
   <a class="xref" href="row-subqueries.html" title="15.2.15.5 Row Subqueries">
    Section 15.2.15.5, “Row Subqueries”
   </a>
   .
  </p>
  <p>
   For a table partitioned by
   <code class="literal">
    RANGE COLUMNS
   </code>
   using only a single partitioning column, the storing of rows
          in partitions is the same as that of an equivalent table that
          is partitioned by
   <code class="literal">
    RANGE
   </code>
   . The following
   <code class="literal">
    CREATE TABLE
   </code>
   statement creates a table
          partitioned by
   <code class="literal">
    RANGE COLUMNS
   </code>
   using 1
          partitioning column:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa69911990"><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> rx <span class="token punctuation">(</span>
    a <span class="token datatype">INT</span><span class="token punctuation">,</span>
    b <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 keyword">COLUMNS</span> <span class="token punctuation">(</span>a<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">5</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 keyword">MAXVALUE</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   If we insert the rows
   <code class="literal">
    (5,10)
   </code>
   ,
   <code class="literal">
    (5,11)
   </code>
   , and
   <code class="literal">
    (5,12)
   </code>
   into
          this table, we can see that their placement is the same as it
          is for the table
   <code class="literal">
    r
   </code>
   we created and populated
          earlier:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa281339"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> rx <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">,</span><span class="token number">10</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token number">5</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 punctuation">(</span><span class="token number">5</span><span class="token punctuation">,</span><span class="token number">12</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 3 rows affected (0.00 sec)</span>
<span class="token output">Records: 3  Duplicates: 0  Warnings: 0</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> PARTITION_NAME<span class="token punctuation">,</span>TABLE_ROWS
<span class="token prompt">    -&gt;</span>     <span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span><span class="token keyword">PARTITIONS</span>
<span class="token prompt">    -&gt;</span>     <span class="token keyword">WHERE</span> <span class="token keyword">TABLE_NAME</span> <span class="token operator">=</span> <span class="token string">'rx'</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> PARTITION_NAME <span class="token punctuation">|</span> TABLE_ROWS <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> p0             <span class="token punctuation">|</span>          0 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> p1             <span class="token punctuation">|</span>          3 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">2 rows in set (0.00 sec)</span></code></pre>
  </div>
  <p>
   It is also possible to create tables partitioned by
   <code class="literal">
    RANGE COLUMNS
   </code>
   where limiting values for one
          or more columns are repeated in successive partition
          definitions. You can do this as long as the tuples of column
          values used to define the partitions are strictly increasing.
          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="sa18956382"><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> rc2 <span class="token punctuation">(</span>
    a <span class="token datatype">INT</span><span class="token punctuation">,</span>
    b <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 keyword">COLUMNS</span><span class="token punctuation">(</span>a<span class="token punctuation">,</span>b<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">0</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 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">10</span><span class="token punctuation">,</span><span class="token number">20</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">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">10</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> 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 keyword">MAXVALUE</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> rc3 <span class="token punctuation">(</span>
    a <span class="token datatype">INT</span><span class="token punctuation">,</span>
    b <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 keyword">COLUMNS</span><span class="token punctuation">(</span>a<span class="token punctuation">,</span>b<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">0</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 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">10</span><span class="token punctuation">,</span><span class="token number">20</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">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">10</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> 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 number">35</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">PARTITION</span> p4 <span class="token keyword">VALUES</span> <span class="token keyword">LESS</span> <span class="token keyword">THAN</span> <span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">,</span><span class="token number">40</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 keyword">MAXVALUE</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>
   The following statement also succeeds, even though it might
          appear at first glance that it would not, since the limiting
          value of column
   <code class="literal">
    b
   </code>
   is 25 for partition
   <code class="literal">
    p0
   </code>
   and 20 for partition
   <code class="literal">
    p1
   </code>
   , and the limiting value of column
   <code class="literal">
    c
   </code>
   is 100 for partition
   <code class="literal">
    p1
   </code>
   and 50 for partition
   <code class="literal">
    p2
   </code>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa29912216"><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> rc4 <span class="token punctuation">(</span>
    a <span class="token datatype">INT</span><span class="token punctuation">,</span>
    b <span class="token datatype">INT</span><span class="token punctuation">,</span>
    c <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 keyword">COLUMNS</span><span class="token punctuation">(</span>a<span class="token punctuation">,</span>b<span class="token punctuation">,</span>c<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">0</span><span class="token punctuation">,</span><span class="token number">25</span><span class="token punctuation">,</span><span class="token number">50</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">10</span><span class="token punctuation">,</span><span class="token number">20</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> 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">10</span><span class="token punctuation">,</span><span class="token number">30</span><span class="token punctuation">,</span><span class="token number">50</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 keyword">MAXVALUE</span><span class="token punctuation">,</span><span class="token keyword">MAXVALUE</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>
   When designing tables partitioned by
   <code class="literal">
    RANGE
          COLUMNS
   </code>
   , you can always test successive partition
          definitions by comparing the desired tuples using the
   <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
    <span class="command">
     <strong>
      mysql
     </strong>
    </span>
   </a>
   client, like this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa18001430"><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 punctuation">(</span><span class="token number">0</span><span class="token punctuation">,</span><span class="token number">25</span><span class="token punctuation">,</span><span class="token number">50</span><span class="token punctuation">)</span> <span class="token operator">&lt;</span> <span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">,</span><span class="token number">20</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 punctuation">(</span><span class="token number">10</span><span class="token punctuation">,</span><span class="token number">20</span><span class="token punctuation">,</span><span class="token number">100</span><span class="token punctuation">)</span> <span class="token operator">&lt;</span> <span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">,</span><span class="token number">30</span><span class="token punctuation">,</span><span class="token number">50</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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> (0,25,50) &lt; (10,20,100) <span class="token punctuation">|</span> (10,20,100) &lt; (10,30,50) <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>                       1 <span class="token punctuation">|</span>                        1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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></code></pre>
  </div>
  <p>
   If a
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE TABLE
    </code>
   </a>
   statement
          contains partition definitions that are not in strictly
          increasing order, it fails with an error, as shown in this
          example:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa96421213"><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> rcf <span class="token punctuation">(</span>
<span class="token prompt">    -&gt;</span>     a <span class="token datatype">INT</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>     b <span class="token datatype">INT</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>     c <span class="token datatype">INT</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 keyword">COLUMNS</span><span class="token punctuation">(</span>a<span class="token punctuation">,</span>b<span class="token punctuation">,</span>c<span class="token punctuation">)</span> <span class="token punctuation">(</span>
<span class="token prompt">    -&gt;</span>     <span class="token keyword">PARTITION</span> p0 <span class="token keyword">VALUES</span> <span class="token keyword">LESS</span> <span class="token keyword">THAN</span> <span class="token punctuation">(</span><span class="token number">0</span><span class="token punctuation">,</span><span class="token number">25</span><span class="token punctuation">,</span><span class="token number">50</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">20</span><span class="token punctuation">,</span><span class="token number">20</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> 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">10</span><span class="token punctuation">,</span><span class="token number">30</span><span class="token punctuation">,</span><span class="token number">50</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 keyword">MAXVALUE</span><span class="token punctuation">,</span><span class="token keyword">MAXVALUE</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 1493 (HY000)<span class="token punctuation">:</span> VALUES LESS THAN value must be strictly increasing for each partition</span></code></pre>
  </div>
  <p>
   When you get such an error, you can deduce which partition
          definitions are invalid by making
   <span class="quote">
    “
    <span class="quote">
     less than
    </span>
    ”
   </span>
   comparisons between their column lists. In this case, the
          problem is with the definition of partition
   <code class="literal">
    p2
   </code>
   because the tuple used to define it is
          not less than the tuple used to define partition
   <code class="literal">
    p3
   </code>
   , as shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa15415860"><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 punctuation">(</span><span class="token number">0</span><span class="token punctuation">,</span><span class="token number">25</span><span class="token punctuation">,</span><span class="token number">50</span><span class="token punctuation">)</span> <span class="token operator">&lt;</span> <span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">,</span><span class="token number">20</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 punctuation">(</span><span class="token number">20</span><span class="token punctuation">,</span><span class="token number">20</span><span class="token punctuation">,</span><span class="token number">100</span><span class="token punctuation">)</span> <span class="token operator">&lt;</span> <span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">,</span><span class="token number">30</span><span class="token punctuation">,</span><span class="token number">50</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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> (0,25,50) &lt; (20,20,100) <span class="token punctuation">|</span> (20,20,100) &lt; (10,30,50) <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>                       1 <span class="token punctuation">|</span>                        0 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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></code></pre>
  </div>
  <p>
   It is also possible for
   <code class="literal">
    MAXVALUE
   </code>
   to appear
          for the same column in more than one
   <code class="literal">
    VALUES LESS
          THAN
   </code>
   clause when using
   <code class="literal">
    RANGE
          COLUMNS
   </code>
   . However, the limiting values for individual
          columns in successive partition definitions should otherwise
          be increasing, there should be no more than one partition
          defined where
   <code class="literal">
    MAXVALUE
   </code>
   is used as the upper
          limit for all column values, and this partition definition
          should appear last in the list of
   <code class="literal">
    PARTITION ...
          VALUES LESS THAN
   </code>
   clauses. In addition, you cannot
          use
   <code class="literal">
    MAXVALUE
   </code>
   as the limiting value for the
          first column in more than one partition definition.
  </p>
  <p>
   As stated previously, it is also possible with
   <code class="literal">
    RANGE
          COLUMNS
   </code>
   partitioning to use non-integer columns as
          partitioning columns. (See
   <a class="xref" href="partitioning-columns.html" title="26.2.3 COLUMNS Partitioning">
    Section 26.2.3, “COLUMNS Partitioning”
   </a>
   , for a complete listing
          of these.) Consider a table named
   <code class="literal">
    employees
   </code>
   (which is not partitioned), created using the following
          statement:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa88747337"><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>
  <p>
   Using
   <code class="literal">
    RANGE COLUMNS
   </code>
   partitioning, you can
          create a version of this table that stores each row in one of
          four partitions based on the employee's last name, like
          this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa31239207"><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_by_lname <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 keyword">COLUMNS</span> <span class="token punctuation">(</span>lname<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">'g'</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">'m'</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">'t'</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 keyword">MAXVALUE</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Alternatively, you could cause the
   <code class="literal">
    employees
   </code>
   table as created previously to be
          partitioned using this scheme by executing the following
   <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:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa16080756"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> employees <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>lname<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">'g'</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">'m'</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">'t'</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 keyword">MAXVALUE</span><span class="token punctuation">)</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>
    Because different character sets and collations have
            different sort orders, the character sets and collations in
            use may effect which partition of a table partitioned by
    <code class="literal">
     RANGE COLUMNS
    </code>
    a given row is stored in
            when using string columns as partitioning columns. In
            addition, changing the character set or collation for a
            given database, table, or column after such a table is
            created may cause changes in how rows are distributed. For
            example, when using a case-sensitive collation,
    <code class="literal">
     'and'
    </code>
    sorts before
    <code class="literal">
     'Andersen'
    </code>
    , but when using a collation
            that is case-insensitive, the reverse is true.
   </p>
   <p>
    For information about how MySQL handles character sets and
            collations, see
    <a class="xref" href="charset.html" title="Chapter 12 Character Sets, Collations, Unicode">
     Chapter 12,
     <i>
      Character Sets, Collations, Unicode
     </i>
    </a>
    .
   </p>
  </div>
  <p>
   Similarly, you can cause the
   <code class="literal">
    employees
   </code>
   table to be partitioned in such a way that each row is stored
          in one of several partitions based on the decade in which the
          corresponding employee was hired using the
   <a class="link" href="alter-table-partition-operations.html" title="15.1.9.1 ALTER TABLE Partition Operations">
    <code class="literal">
     ALTER
          TABLE
    </code>
   </a>
   statement shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa61166859"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> employees <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>hired<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">'1970-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">'1980-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">'1990-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">'2000-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 string">'2010-01-01'</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 keyword">MAXVALUE</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   See
   <a class="xref" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    Section 15.1.20, “CREATE TABLE Statement”
   </a>
   , for additional information
          about
   <code class="literal">
    PARTITION BY RANGE COLUMNS
   </code>
   syntax.
  </p>
 </div>
 <br/>
</div>
