<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="partitioning-key">
      </a>
      26.2.5 KEY Partitioning
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045084229888">
  </a>
  <a class="indexterm" name="idm46045084228400">
  </a>
  <p>
   Partitioning by key is similar to partitioning by hash, except
        that where hash partitioning employs a user-defined expression,
        the hashing function for key partitioning is supplied by the
        MySQL server. NDB Cluster uses
   <a class="link" href="encryption-functions.html#function_md5">
    <code class="literal">
     MD5()
    </code>
   </a>
   for this purpose; for
        tables using other storage engines, the server employs its own
        internal hashing function.
  </p>
  <p>
   The syntax rules for
   <code class="literal">
    CREATE TABLE ... PARTITION BY
        KEY
   </code>
   are similar to those for creating a table that is
        partitioned by hash. The major differences are listed here:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       KEY
      </code>
      is used rather than
      <code class="literal">
       HASH
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       KEY
      </code>
      takes only a list of zero or more
            column names. Any columns used as the partitioning key must
            comprise part or all of the table's primary key, if the
            table has one. Where no column name is specified as the
            partitioning key, the table's primary key is used, if
            there is one. For example, the following
      <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
       <code class="literal">
        CREATE TABLE
       </code>
      </a>
      statement is
            valid in MySQL 8.4:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa60316687"><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> k1 <span class="token punctuation">(</span>
    id <span class="token datatype">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">,</span>
    <span class="token keyword">name</span> <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span>
<span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> <span class="token keyword">KEY</span><span class="token punctuation">(</span><span class="token punctuation">)</span>
<span class="token keyword">PARTITIONS</span> <span class="token number">2</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      If there is no primary key but there is a unique key, then
            the unique key is used for the partitioning key:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa66020192"><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> k1 <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>
    <span class="token keyword">name</span> <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">UNIQUE</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span>id<span class="token punctuation">)</span>
<span class="token punctuation">)</span>
<span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> <span class="token keyword">KEY</span><span class="token punctuation">(</span><span class="token punctuation">)</span>
<span class="token keyword">PARTITIONS</span> <span class="token number">2</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      However, if the unique key column were not defined as
      <code class="literal">
       NOT NULL
      </code>
      , then the previous statement
            would fail.
     </p>
     <p>
      In both of these cases, the partitioning key is the
      <code class="literal">
       id
      </code>
      column, even though it is not shown in
            the output of
      <a class="link" href="show-create-table.html" title="15.7.7.11 SHOW CREATE TABLE Statement">
       <code class="literal">
        SHOW CREATE
            TABLE
       </code>
      </a>
      or in the
      <code class="literal">
       PARTITION_EXPRESSION
      </code>
      column of 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>
     <p>
      Unlike the case with other partitioning types, columns used
            for partitioning by
      <code class="literal">
       KEY
      </code>
      are not
            restricted to integer or
      <code class="literal">
       NULL
      </code>
      values. For
            example, the following
      <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
       <code class="literal">
        CREATE
            TABLE
       </code>
      </a>
      statement is valid:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa22081734"><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> tm1 <span class="token punctuation">(</span>
    s1 <span class="token datatype">CHAR</span><span class="token punctuation">(</span><span class="token number">32</span><span class="token punctuation">)</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span>
<span class="token punctuation">)</span>
<span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> <span class="token keyword">KEY</span><span class="token punctuation">(</span>s1<span class="token punctuation">)</span>
<span class="token keyword">PARTITIONS</span> <span class="token number">10</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      The preceding statement would
      <span class="emphasis">
       <em>
        not
       </em>
      </span>
      be
            valid, were a different partitioning type to be specified.
            (In this case, simply using
      <code class="literal">
       PARTITION BY
            KEY()
      </code>
      would also be valid and have the same effect
            as
      <code class="literal">
       PARTITION BY KEY(s1)
      </code>
      , since
      <code class="literal">
       s1
      </code>
      is the table's primary key.)
     </p>
     <p>
      For additional information about this issue, see
      <a class="xref" href="partitioning-limitations.html" title="26.6 Restrictions and Limitations on Partitioning">
       Section 26.6, “Restrictions and Limitations on Partitioning”
      </a>
      .
     </p>
     <p>
      Columns with index prefixes are not supported in
            partitioning keys. This means that
      <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
       <code class="literal">
        CHAR
       </code>
      </a>
      ,
      <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
       <code class="literal">
        VARCHAR
       </code>
      </a>
      ,
      <a class="link" href="binary-varbinary.html" title="13.3.3 The BINARY and VARBINARY Types">
       <code class="literal">
        BINARY
       </code>
      </a>
      , and
      <a class="link" href="binary-varbinary.html" title="13.3.3 The BINARY and VARBINARY Types">
       <code class="literal">
        VARBINARY
       </code>
      </a>
      columns can be used
            in a partitioning key, as long as they do not employ
            prefixes; because a prefix must be specified for
      <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
       <code class="literal">
        BLOB
       </code>
      </a>
      and
      <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
       <code class="literal">
        TEXT
       </code>
      </a>
      columns in index
            definitions, it is not possible to use columns of these two
            types in partitioning keys. In older versions of MySQL,
            columns using prefixes were permitted when creating,
            altering, or upgrading a partitioned table, even though they
            were not included in the table's partitioning key; in
            MySQL 8.4, this is no longer allowed
            deprecated, and the server rejects any
      <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
       <code class="literal">
        CREATE TABLE
       </code>
      </a>
      or
      <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
       <code class="literal">
        ALTER TABLE
       </code>
      </a>
      statement
            affecting a partitioned table in which one or more columns
            using prefixes occur with an error. See
      <a class="xref" href="partitioning-limitations.html#partitioning-limitations-prefixes" title="Column index prefixes not supported for key partitioning">
       Column index prefixes not supported for key partitioning
      </a>
      .
     </p>
     <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
      <div class="admon-title">
       Note
      </div>
      <p>
       Tables using the
       <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
        <code class="literal">
         NDB
        </code>
       </a>
       storage
              engine are implicitly partitioned by
       <code class="literal">
        KEY
       </code>
       , using the table's primary key
              as the partitioning key (as with other MySQL storage
              engines). In the event that the NDB Cluster table has no
              explicit primary key, the
       <span class="quote">
        “
        <span class="quote">
         hidden
        </span>
        ”
       </span>
       primary
              key generated by the
       <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
        <code class="literal">
         NDB
        </code>
       </a>
       storage engine for each NDB Cluster table is used as the
              partitioning key.
      </p>
      <p>
       If you define an explicit partitioning scheme for an
       <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
        <code class="literal">
         NDB
        </code>
       </a>
       table, the table must
              have an explicit primary key, and any columns used in the
              partitioning expression must be part of this key. However,
              if the table uses an
       <span class="quote">
        “
        <span class="quote">
         empty
        </span>
        ”
       </span>
       partitioning
              expression—that is,
       <code class="literal">
        PARTITION BY
              KEY()
       </code>
       with no column references—then no
              explicit primary key is required.
      </p>
      <p>
       You can observe this partitioning using the
       <a class="link" href="mysql-cluster-programs-ndb-desc.html" title="25.5.9 ndb_desc — Describe NDB Tables">
        <span class="command">
         <strong>
          ndb_desc
         </strong>
        </span>
       </a>
       utility (with the
       <code class="option">
        -p
       </code>
       option).
      </p>
     </div>
     <div class="important" style="margin-left: 0.5in; margin-right: 0.5in;">
      <div class="admon-title">
       Important
      </div>
      <p>
       For a key-partitioned table, you cannot execute an
       <code class="literal">
        ALTER TABLE DROP PRIMARY KEY
       </code>
       , as doing
              so generates the error
       <span class="errortext">
        ERROR 1466 (HY000):
              Field in list of fields for partition function not found
              in table
       </span>
       . This is not an issue for NDB Cluster
              tables which are partitioned by
       <code class="literal">
        KEY
       </code>
       ; in
              such cases, the table is reorganized using the
       <span class="quote">
        “
        <span class="quote">
         hidden
        </span>
        ”
       </span>
       primary key as the table's new
              partitioning key. See
       <a class="xref" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
        Chapter 25,
        <i>
         MySQL NDB Cluster 8.4
        </i>
       </a>
       .
      </p>
     </div>
    </li>
   </ul>
  </div>
  <a class="indexterm" name="idm46045084175216">
  </a>
  <a class="indexterm" name="idm46045084173728">
  </a>
  <p>
   It is also possible to partition a table by linear key. Here is
        a simple example:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa90109736"><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> tk <span class="token punctuation">(</span>
    col1 <span class="token datatype">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    col2 <span class="token datatype">CHAR</span><span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    col3 <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">LINEAR</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span>col1<span class="token punctuation">)</span>
<span class="token keyword">PARTITIONS</span> <span class="token number">3</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   The
   <code class="literal">
    LINEAR
   </code>
   keyword has the same effect on
   <code class="literal">
    KEY
   </code>
   partitioning as it does on
   <code class="literal">
    HASH
   </code>
   partitioning, with the partition number
        being derived using a powers-of-two algorithm rather than modulo
        arithmetic. See
   <a class="xref" href="partitioning-linear-hash.html" title="26.2.4.1 LINEAR HASH Partitioning">
    Section 26.2.4.1, “LINEAR HASH Partitioning”
   </a>
   , for
        a description of this algorithm and its implications.
  </p>
 </div>
 <br/>
</div>
