<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="partitioning-columns-list">
      </a>
      26.2.3.2 LIST COLUMNS partitioning
     </h4>
    </div>
   </div>
  </div>
  <p>
   MySQL 8.4 provides support for
   <code class="literal">
    LIST
          COLUMNS
   </code>
   partitioning. This is a variant of
   <code class="literal">
    LIST
   </code>
   partitioning that enables the use of
          multiple columns as partition keys, and for columns of data
          types other than integer types to be used as partitioning
          columns; you can use string types,
   <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
    <code class="literal">
     DATE
    </code>
   </a>
   , and
   <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
    <code class="literal">
     DATETIME
    </code>
   </a>
   columns. (For more
          information about permitted data types for
   <code class="literal">
    COLUMNS
   </code>
   partitioning columns, see
   <a class="xref" href="partitioning-columns.html" title="26.2.3 COLUMNS Partitioning">
    Section 26.2.3, “COLUMNS Partitioning”
   </a>
   .)
  </p>
  <p>
   Suppose that you have a business that has customers in 12
          cities which, for sales and marketing purposes, you organize
          into 4 regions of 3 cities each as shown in the following
          table:
  </p>
  <div class="informaltable">
   <table summary="The example described in the preceding text of a business with four sales and marketing regions, with each region having three cities.">
    <colgroup>
     <col style="width: 50%"/>
     <col style="width: 50%"/>
    </colgroup>
    <thead>
     <tr>
      <th>
       Region
      </th>
      <th>
       Cities
      </th>
     </tr>
    </thead>
    <tbody>
     <tr>
      <td>
       1
      </td>
      <td>
       Oskarshamn, Högsby, Mönsterås
      </td>
     </tr>
     <tr>
      <td>
       2
      </td>
      <td>
       Vimmerby, Hultsfred, Västervik
      </td>
     </tr>
     <tr>
      <td>
       3
      </td>
      <td>
       Nässjö, Eksjö, Vetlanda
      </td>
     </tr>
     <tr>
      <td>
       4
      </td>
      <td>
       Uppvidinge, Alvesta, Växjo
      </td>
     </tr>
    </tbody>
   </table>
  </div>
  <p>
   With
   <code class="literal">
    LIST COLUMNS
   </code>
   partitioning, you can
          create a table for customer data that assigns a row to any of
          4 partitions corresponding to these regions based on the name
          of the city where a customer resides, as shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa48447647"><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> customers_1 <span class="token punctuation">(</span>
    first_name <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">25</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    last_name <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">25</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    street_1 <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>
    street_2 <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>
    city <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">15</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    renewal <span class="token datatype">DATE</span>
<span class="token punctuation">)</span>
<span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> <span class="token keyword">LIST</span> <span class="token keyword">COLUMNS</span><span class="token punctuation">(</span>city<span class="token punctuation">)</span> <span class="token punctuation">(</span>
    <span class="token keyword">PARTITION</span> pRegion_1 <span class="token keyword">VALUES</span> <span class="token keyword">IN</span><span class="token punctuation">(</span><span class="token string">'Oskarshamn'</span><span class="token punctuation">,</span> <span class="token string">'Högsby'</span><span class="token punctuation">,</span> <span class="token string">'Mönsterås'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">PARTITION</span> pRegion_2 <span class="token keyword">VALUES</span> <span class="token keyword">IN</span><span class="token punctuation">(</span><span class="token string">'Vimmerby'</span><span class="token punctuation">,</span> <span class="token string">'Hultsfred'</span><span class="token punctuation">,</span> <span class="token string">'Västervik'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">PARTITION</span> pRegion_3 <span class="token keyword">VALUES</span> <span class="token keyword">IN</span><span class="token punctuation">(</span><span class="token string">'Nässjö'</span><span class="token punctuation">,</span> <span class="token string">'Eksjö'</span><span class="token punctuation">,</span> <span class="token string">'Vetlanda'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">PARTITION</span> pRegion_4 <span class="token keyword">VALUES</span> <span class="token keyword">IN</span><span class="token punctuation">(</span><span class="token string">'Uppvidinge'</span><span class="token punctuation">,</span> <span class="token string">'Alvesta'</span><span class="token punctuation">,</span> <span class="token string">'Växjo'</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   As with partitioning by
   <code class="literal">
    RANGE COLUMNS
   </code>
   , you
          do not need to use expressions in the
   <code class="literal">
    COLUMNS()
   </code>
   clause to convert column values
          into integers. (In fact, the use of expressions other than
          column names is not permitted with
   <code class="literal">
    COLUMNS()
   </code>
   .)
  </p>
  <p>
   It is also possible to use
   <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, as shown
          in the following example that uses the same name and columns
          as the
   <code class="literal">
    customers_1
   </code>
   table shown previously,
          but employs
   <code class="literal">
    LIST COLUMNS
   </code>
   partitioning based
          on the
   <code class="literal">
    renewal
   </code>
   column to store rows in one
          of 4 partitions depending on the week in February 2010 the
          customer's account is scheduled to renew:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa4644030"><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> customers_2 <span class="token punctuation">(</span>
    first_name <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">25</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    last_name <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">25</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    street_1 <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>
    street_2 <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>
    city <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">15</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    renewal <span class="token datatype">DATE</span>
<span class="token punctuation">)</span>
<span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> <span class="token keyword">LIST</span> <span class="token keyword">COLUMNS</span><span class="token punctuation">(</span>renewal<span class="token punctuation">)</span> <span class="token punctuation">(</span>
    <span class="token keyword">PARTITION</span> pWeek_1 <span class="token keyword">VALUES</span> <span class="token keyword">IN</span><span class="token punctuation">(</span><span class="token string">'2010-02-01'</span><span class="token punctuation">,</span> <span class="token string">'2010-02-02'</span><span class="token punctuation">,</span> <span class="token string">'2010-02-03'</span><span class="token punctuation">,</span>
        <span class="token string">'2010-02-04'</span><span class="token punctuation">,</span> <span class="token string">'2010-02-05'</span><span class="token punctuation">,</span> <span class="token string">'2010-02-06'</span><span class="token punctuation">,</span> <span class="token string">'2010-02-07'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">PARTITION</span> pWeek_2 <span class="token keyword">VALUES</span> <span class="token keyword">IN</span><span class="token punctuation">(</span><span class="token string">'2010-02-08'</span><span class="token punctuation">,</span> <span class="token string">'2010-02-09'</span><span class="token punctuation">,</span> <span class="token string">'2010-02-10'</span><span class="token punctuation">,</span>
        <span class="token string">'2010-02-11'</span><span class="token punctuation">,</span> <span class="token string">'2010-02-12'</span><span class="token punctuation">,</span> <span class="token string">'2010-02-13'</span><span class="token punctuation">,</span> <span class="token string">'2010-02-14'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">PARTITION</span> pWeek_3 <span class="token keyword">VALUES</span> <span class="token keyword">IN</span><span class="token punctuation">(</span><span class="token string">'2010-02-15'</span><span class="token punctuation">,</span> <span class="token string">'2010-02-16'</span><span class="token punctuation">,</span> <span class="token string">'2010-02-17'</span><span class="token punctuation">,</span>
        <span class="token string">'2010-02-18'</span><span class="token punctuation">,</span> <span class="token string">'2010-02-19'</span><span class="token punctuation">,</span> <span class="token string">'2010-02-20'</span><span class="token punctuation">,</span> <span class="token string">'2010-02-21'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">PARTITION</span> pWeek_4 <span class="token keyword">VALUES</span> <span class="token keyword">IN</span><span class="token punctuation">(</span><span class="token string">'2010-02-22'</span><span class="token punctuation">,</span> <span class="token string">'2010-02-23'</span><span class="token punctuation">,</span> <span class="token string">'2010-02-24'</span><span class="token punctuation">,</span>
        <span class="token string">'2010-02-25'</span><span class="token punctuation">,</span> <span class="token string">'2010-02-26'</span><span class="token punctuation">,</span> <span class="token string">'2010-02-27'</span><span class="token punctuation">,</span> <span class="token string">'2010-02-28'</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   This works, but becomes cumbersome to define and maintain if
          the number of dates involved grows very large; in such cases,
          it is usually more practical to employ
   <code class="literal">
    RANGE
   </code>
   or
   <code class="literal">
    RANGE COLUMNS
   </code>
   partitioning instead. In this case, since the column we wish
          to use as the partitioning key is a
   <a class="link" href="datetime.html" title="13.2.2 The DATE, DATETIME, and TIMESTAMP Types">
    <code class="literal">
     DATE
    </code>
   </a>
   column, we use
   <code class="literal">
    RANGE COLUMNS
   </code>
   partitioning, as shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa68416689"><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> customers_3 <span class="token punctuation">(</span>
    first_name <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">25</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    last_name <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">25</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    street_1 <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>
    street_2 <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>
    city <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">15</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    renewal <span class="token datatype">DATE</span>
<span class="token punctuation">)</span>
<span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> <span class="token keyword">RANGE</span> <span class="token keyword">COLUMNS</span><span class="token punctuation">(</span>renewal<span class="token punctuation">)</span> <span class="token punctuation">(</span>
    <span class="token keyword">PARTITION</span> pWeek_1 <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-02-09'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">PARTITION</span> pWeek_2 <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-02-15'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">PARTITION</span> pWeek_3 <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-02-22'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">PARTITION</span> pWeek_4 <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-03-01'</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="partitioning-columns-range.html" title="26.2.3.1 RANGE COLUMNS partitioning">
    Section 26.2.3.1, “RANGE COLUMNS partitioning”
   </a>
   , for more
          information.
  </p>
  <p>
   In addition (as with
   <code class="literal">
    RANGE COLUMNS
   </code>
   partitioning), you can use multiple columns in the
   <code class="literal">
    COLUMNS()
   </code>
   clause.
  </p>
  <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 LIST COLUMNS()
   </code>
   syntax.
  </p>
 </div>
 <br/>
</div>
