<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="create-table">
      </a>
      15.1.20 CREATE TABLE Statement
     </h3>
    </div>
   </div>
  </div>
  <div class="toc">
   <dl class="toc">
    <dt>
     <span class="section">
      <a href="create-table-files.html">
       15.1.20.1 Files Created by CREATE TABLE
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="create-temporary-table.html">
       15.1.20.2 CREATE TEMPORARY TABLE Statement
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="create-table-like.html">
       15.1.20.3 CREATE TABLE ... LIKE Statement
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="create-table-select.html">
       15.1.20.4 CREATE TABLE ... SELECT Statement
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="create-table-foreign-keys.html">
       15.1.20.5 FOREIGN KEY Constraints
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="create-table-check-constraints.html">
       15.1.20.6 CHECK Constraints
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="silent-column-changes.html">
       15.1.20.7 Silent Column Specification Changes
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="create-table-generated-columns.html">
       15.1.20.8 CREATE TABLE and Generated Columns
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="create-table-secondary-indexes.html">
       15.1.20.9 Secondary Indexes and Generated Columns
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="invisible-columns.html">
       15.1.20.10 Invisible Columns
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="create-table-gipks.html">
       15.1.20.11 Generated Invisible Primary Keys
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="create-table-ndb-comment-options.html">
       15.1.20.12 Setting NDB Comment Options
      </a>
     </span>
    </dt>
   </dl>
  </div>
  <a class="indexterm" name="idm46045186637392">
  </a>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa92168710"><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 punctuation">[</span><span class="token keyword">TEMPORARY</span><span class="token punctuation">]</span> <span class="token keyword">TABLE</span> <span class="token punctuation">[</span><span class="token keyword">IF</span> <span class="token operator">NOT</span> <span class="token keyword">EXISTS</span><span class="token punctuation">]</span> <em class="replaceable">tbl_name</em>
    <span class="token punctuation">(</span><em class="replaceable">create_definition</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><em class="replaceable">table_options</em><span class="token punctuation">]</span>
    <span class="token punctuation">[</span><em class="replaceable">partition_options</em><span class="token punctuation">]</span>

<span class="token keyword">CREATE</span> <span class="token punctuation">[</span><span class="token keyword">TEMPORARY</span><span class="token punctuation">]</span> <span class="token keyword">TABLE</span> <span class="token punctuation">[</span><span class="token keyword">IF</span> <span class="token operator">NOT</span> <span class="token keyword">EXISTS</span><span class="token punctuation">]</span> <em class="replaceable">tbl_name</em>
    <span class="token punctuation">[</span><span class="token punctuation">(</span><em class="replaceable">create_definition</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><em class="replaceable">table_options</em><span class="token punctuation">]</span>
    <span class="token punctuation">[</span><em class="replaceable">partition_options</em><span class="token punctuation">]</span>
    <span class="token punctuation">[</span><span class="token keyword">IGNORE</span> <span class="token operator">|</span> <span class="token keyword">REPLACE</span><span class="token punctuation">]</span>
    <span class="token punctuation">[</span><span class="token keyword">AS</span><span class="token punctuation">]</span> <em class="replaceable">query_expression</em>

<span class="token keyword">CREATE</span> <span class="token punctuation">[</span><span class="token keyword">TEMPORARY</span><span class="token punctuation">]</span> <span class="token keyword">TABLE</span> <span class="token punctuation">[</span><span class="token keyword">IF</span> <span class="token operator">NOT</span> <span class="token keyword">EXISTS</span><span class="token punctuation">]</span> <em class="replaceable">tbl_name</em>
    { <span class="token operator">LIKE</span> <em class="replaceable">old_tbl_name</em> <span class="token operator">|</span> <span class="token punctuation">(</span><span class="token operator">LIKE</span> <em class="replaceable">old_tbl_name</em><span class="token punctuation">)</span> }

<em class="replaceable">create_definition</em>: {
    <em class="replaceable">col_name</em> <em class="replaceable">column_definition</em>
  <span class="token operator">|</span> {<span class="token keyword">INDEX</span> <span class="token operator">|</span> <span class="token keyword">KEY</span>} <span class="token punctuation">[</span><em class="replaceable">index_name</em><span class="token punctuation">]</span> <span class="token punctuation">[</span><em class="replaceable">index_type</em><span class="token punctuation">]</span> <span class="token punctuation">(</span><em class="replaceable">key_part</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><em class="replaceable">index_option</em><span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
  <span class="token operator">|</span> {<span class="token keyword">FULLTEXT</span> <span class="token operator">|</span> <span class="token keyword">SPATIAL</span>} <span class="token punctuation">[</span><span class="token keyword">INDEX</span> <span class="token operator">|</span> <span class="token keyword">KEY</span><span class="token punctuation">]</span> <span class="token punctuation">[</span><em class="replaceable">index_name</em><span class="token punctuation">]</span> <span class="token punctuation">(</span><em class="replaceable">key_part</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><em class="replaceable">index_option</em><span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
  <span class="token operator">|</span> <span class="token punctuation">[</span><span class="token keyword">CONSTRAINT</span> <span class="token punctuation">[</span><em class="replaceable">symbol</em><span class="token punctuation">]</span><span class="token punctuation">]</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span>
      <span class="token punctuation">[</span><em class="replaceable">index_type</em><span class="token punctuation">]</span> <span class="token punctuation">(</span><em class="replaceable">key_part</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><em class="replaceable">index_option</em><span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
  <span class="token operator">|</span> <span class="token punctuation">[</span><span class="token keyword">CONSTRAINT</span> <span class="token punctuation">[</span><em class="replaceable">symbol</em><span class="token punctuation">]</span><span class="token punctuation">]</span> <span class="token keyword">UNIQUE</span> <span class="token punctuation">[</span><span class="token keyword">INDEX</span> <span class="token operator">|</span> <span class="token keyword">KEY</span><span class="token punctuation">]</span>
      <span class="token punctuation">[</span><em class="replaceable">index_name</em><span class="token punctuation">]</span> <span class="token punctuation">[</span><em class="replaceable">index_type</em><span class="token punctuation">]</span> <span class="token punctuation">(</span><em class="replaceable">key_part</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><em class="replaceable">index_option</em><span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
  <span class="token operator">|</span> <span class="token punctuation">[</span><span class="token keyword">CONSTRAINT</span> <span class="token punctuation">[</span><em class="replaceable">symbol</em><span class="token punctuation">]</span><span class="token punctuation">]</span> <span class="token keyword">FOREIGN</span> <span class="token keyword">KEY</span>
      <span class="token punctuation">[</span><em class="replaceable">index_name</em><span class="token punctuation">]</span> <span class="token punctuation">(</span><em class="replaceable">col_name</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>
      <em class="replaceable">reference_definition</em>
  <span class="token operator">|</span> <em class="replaceable">check_constraint_definition</em>
}

<em class="replaceable">column_definition</em>: {
    <em class="replaceable">data_type</em> <span class="token punctuation">[</span><span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token operator">|</span> <span class="token boolean">NULL</span><span class="token punctuation">]</span> <span class="token punctuation">[</span><span class="token keyword">DEFAULT</span> {<em class="replaceable">literal</em> <span class="token operator">|</span> <span class="token punctuation">(</span><em class="replaceable">expr</em><span class="token punctuation">)</span>} <span class="token punctuation">]</span>
      <span class="token punctuation">[</span><span class="token keyword">VISIBLE</span> <span class="token operator">|</span> <span class="token keyword">INVISIBLE</span><span class="token punctuation">]</span>
      <span class="token punctuation">[</span><span class="token keyword">AUTO_INCREMENT</span><span class="token punctuation">]</span> <span class="token punctuation">[</span><span class="token keyword">UNIQUE</span> <span class="token punctuation">[</span><span class="token keyword">KEY</span><span class="token punctuation">]</span><span class="token punctuation">]</span> <span class="token punctuation">[</span><span class="token punctuation">[</span><span class="token keyword">PRIMARY</span><span class="token punctuation">]</span> <span class="token keyword">KEY</span><span class="token punctuation">]</span>
      <span class="token punctuation">[</span><span class="token keyword">COMMENT</span> <span class="token string">'<em class="replaceable">string</em>'</span><span class="token punctuation">]</span>
      <span class="token punctuation">[</span><span class="token keyword">COLLATE</span> <em class="replaceable">collation_name</em><span class="token punctuation">]</span>
      <span class="token punctuation">[</span><span class="token keyword">COLUMN_FORMAT</span> {<span class="token keyword">FIXED</span> <span class="token operator">|</span> <span class="token keyword">DYNAMIC</span> <span class="token operator">|</span> <span class="token keyword">DEFAULT</span>}<span class="token punctuation">]</span>
      <span class="token punctuation">[</span><span class="token keyword">ENGINE_ATTRIBUTE</span> <span class="token punctuation">[</span><span class="token operator">=</span><span class="token punctuation">]</span> <span class="token string">'<em class="replaceable">string</em>'</span><span class="token punctuation">]</span>
      <span class="token punctuation">[</span><span class="token keyword">SECONDARY_ENGINE_ATTRIBUTE</span> <span class="token punctuation">[</span><span class="token operator">=</span><span class="token punctuation">]</span> <span class="token string">'<em class="replaceable">string</em>'</span><span class="token punctuation">]</span>
      <span class="token punctuation">[</span><span class="token keyword">STORAGE</span> {<span class="token keyword">DISK</span> <span class="token operator">|</span> <span class="token keyword">MEMORY</span>}<span class="token punctuation">]</span>
      <span class="token punctuation">[</span><em class="replaceable">reference_definition</em><span class="token punctuation">]</span>
      <span class="token punctuation">[</span><em class="replaceable">check_constraint_definition</em><span class="token punctuation">]</span>
  <span class="token operator">|</span> <em class="replaceable">data_type</em>
      <span class="token punctuation">[</span><span class="token keyword">COLLATE</span> <em class="replaceable">collation_name</em><span class="token punctuation">]</span>
      <span class="token punctuation">[</span><span class="token keyword">GENERATED</span> <span class="token keyword">ALWAYS</span><span class="token punctuation">]</span> <span class="token keyword">AS</span> <span class="token punctuation">(</span><em class="replaceable">expr</em><span class="token punctuation">)</span>
      <span class="token punctuation">[</span><span class="token keyword">VIRTUAL</span> <span class="token operator">|</span> <span class="token keyword">STORED</span><span class="token punctuation">]</span> <span class="token punctuation">[</span><span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token operator">|</span> <span class="token boolean">NULL</span><span class="token punctuation">]</span>
      <span class="token punctuation">[</span><span class="token keyword">VISIBLE</span> <span class="token operator">|</span> <span class="token keyword">INVISIBLE</span><span class="token punctuation">]</span>
      <span class="token punctuation">[</span><span class="token keyword">UNIQUE</span> <span class="token punctuation">[</span><span class="token keyword">KEY</span><span class="token punctuation">]</span><span class="token punctuation">]</span> <span class="token punctuation">[</span><span class="token punctuation">[</span><span class="token keyword">PRIMARY</span><span class="token punctuation">]</span> <span class="token keyword">KEY</span><span class="token punctuation">]</span>
      <span class="token punctuation">[</span><span class="token keyword">COMMENT</span> <span class="token string">'<em class="replaceable">string</em>'</span><span class="token punctuation">]</span>
      <span class="token punctuation">[</span><em class="replaceable">reference_definition</em><span class="token punctuation">]</span>
      <span class="token punctuation">[</span><em class="replaceable">check_constraint_definition</em><span class="token punctuation">]</span>
}

<em class="replaceable">data_type</em>:
    <span class="token punctuation">(</span>see Chapter <span class="token number">13</span><span class="token punctuation">,</span> <span class="token keyword">Data</span> <span class="token keyword">Types</span><span class="token punctuation">)</span>

<em class="replaceable">key_part</em>: {<em class="replaceable">col_name</em> <span class="token punctuation">[</span><span class="token punctuation">(</span><em class="replaceable">length</em><span class="token punctuation">)</span><span class="token punctuation">]</span> <span class="token operator">|</span> <span class="token punctuation">(</span><em class="replaceable">expr</em><span class="token punctuation">)</span>} <span class="token punctuation">[</span><span class="token keyword">ASC</span> <span class="token operator">|</span> <span class="token keyword">DESC</span><span class="token punctuation">]</span>

<em class="replaceable">index_type</em>:
    <span class="token keyword">USING</span> {<span class="token keyword">BTREE</span> <span class="token operator">|</span> <span class="token keyword">HASH</span>}

<em class="replaceable">index_option</em>: {
    <span class="token keyword">KEY_BLOCK_SIZE</span> <span class="token punctuation">[</span><span class="token operator">=</span><span class="token punctuation">]</span> <span class="token keyword"><em class="replaceable">value</em></span>
  <span class="token operator">|</span> <em class="replaceable">index_type</em>
  <span class="token operator">|</span> <span class="token keyword">WITH</span> <span class="token keyword">PARSER</span> <em class="replaceable">parser_name</em>
  <span class="token operator">|</span> <span class="token keyword">COMMENT</span> <span class="token string">'<em class="replaceable">string</em>'</span>
  <span class="token operator">|</span> {<span class="token keyword">VISIBLE</span> <span class="token operator">|</span> <span class="token keyword">INVISIBLE</span>}
  <span class="token operator">|</span><span class="token keyword">ENGINE_ATTRIBUTE</span> <span class="token punctuation">[</span><span class="token operator">=</span><span class="token punctuation">]</span> <span class="token string">'<em class="replaceable">string</em>'</span>
  <span class="token operator">|</span><span class="token keyword">SECONDARY_ENGINE_ATTRIBUTE</span> <span class="token punctuation">[</span><span class="token operator">=</span><span class="token punctuation">]</span> <span class="token string">'<em class="replaceable">string</em>'</span>
}

<em class="replaceable">check_constraint_definition</em>:
    <span class="token punctuation">[</span><span class="token keyword">CONSTRAINT</span> <span class="token punctuation">[</span><em class="replaceable">symbol</em><span class="token punctuation">]</span><span class="token punctuation">]</span> <span class="token keyword">CHECK</span> <span class="token punctuation">(</span><em class="replaceable">expr</em><span class="token punctuation">)</span> <span class="token punctuation">[</span><span class="token punctuation">[</span><span class="token operator">NOT</span><span class="token punctuation">]</span> <span class="token keyword">ENFORCED</span><span class="token punctuation">]</span>

<em class="replaceable">reference_definition</em>:
    <span class="token keyword">REFERENCES</span> <em class="replaceable">tbl_name</em> <span class="token punctuation">(</span><em class="replaceable">key_part</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 operator">MATCH</span> <span class="token keyword">FULL</span> <span class="token operator">|</span> <span class="token operator">MATCH</span> <span class="token keyword">PARTIAL</span> <span class="token operator">|</span> <span class="token operator">MATCH</span> <span class="token keyword">SIMPLE</span><span class="token punctuation">]</span>
      <span class="token punctuation">[</span><span class="token keyword">ON</span> <span class="token keyword">DELETE</span> <em class="replaceable">reference_option</em><span class="token punctuation">]</span>
      <span class="token punctuation">[</span><span class="token keyword">ON</span> <span class="token keyword">UPDATE</span> <em class="replaceable">reference_option</em><span class="token punctuation">]</span>

<em class="replaceable">reference_option</em>:
    <span class="token keyword">RESTRICT</span> <span class="token operator">|</span> <span class="token keyword">CASCADE</span> <span class="token operator">|</span> <span class="token keyword">SET</span> <span class="token boolean">NULL</span> <span class="token operator">|</span> <span class="token keyword">NO</span> <span class="token keyword">ACTION</span> <span class="token operator">|</span> <span class="token keyword">SET</span> <span class="token keyword">DEFAULT</span>

<em class="replaceable">table_options</em>:
    <em class="replaceable">table_option</em> <span class="token punctuation">[</span><span class="token punctuation">[</span><span class="token punctuation">,</span><span class="token punctuation">]</span> <em class="replaceable">table_option</em><span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>

<em class="replaceable">table_option</em>: {
    <span class="token keyword">AUTOEXTEND_SIZE</span> <span class="token punctuation">[</span><span class="token operator">=</span><span class="token punctuation">]</span> <span class="token keyword"><em class="replaceable">value</em></span>
  <span class="token operator">|</span> <span class="token keyword">AUTO_INCREMENT</span> <span class="token punctuation">[</span><span class="token operator">=</span><span class="token punctuation">]</span> <span class="token keyword"><em class="replaceable">value</em></span>
  <span class="token operator">|</span> <span class="token keyword">AVG_ROW_LENGTH</span> <span class="token punctuation">[</span><span class="token operator">=</span><span class="token punctuation">]</span> <span class="token keyword"><em class="replaceable">value</em></span>
  <span class="token operator">|</span> <span class="token punctuation">[</span><span class="token keyword">DEFAULT</span><span class="token punctuation">]</span> <span class="token keyword">CHARACTER</span> <span class="token keyword">SET</span> <span class="token punctuation">[</span><span class="token operator">=</span><span class="token punctuation">]</span> <em class="replaceable">charset_name</em>
  <span class="token operator">|</span> <span class="token keyword">CHECKSUM</span> <span class="token punctuation">[</span><span class="token operator">=</span><span class="token punctuation">]</span> {<span class="token number">0</span> <span class="token operator">|</span> <span class="token number">1</span>}
  <span class="token operator">|</span> <span class="token punctuation">[</span><span class="token keyword">DEFAULT</span><span class="token punctuation">]</span> <span class="token keyword">COLLATE</span> <span class="token punctuation">[</span><span class="token operator">=</span><span class="token punctuation">]</span> <em class="replaceable">collation_name</em>
  <span class="token operator">|</span> <span class="token keyword">COMMENT</span> <span class="token punctuation">[</span><span class="token operator">=</span><span class="token punctuation">]</span> <span class="token string">'<em class="replaceable">string</em>'</span>
  <span class="token operator">|</span> <span class="token keyword">COMPRESSION</span> <span class="token punctuation">[</span><span class="token operator">=</span><span class="token punctuation">]</span> {<span class="token string">'ZLIB'</span> <span class="token operator">|</span> <span class="token string">'LZ4'</span> <span class="token operator">|</span> <span class="token string">'NONE'</span>}
  <span class="token operator">|</span> <span class="token keyword">CONNECTION</span> <span class="token punctuation">[</span><span class="token operator">=</span><span class="token punctuation">]</span> <span class="token string">'<em class="replaceable">connect_string</em>'</span>
  <span class="token operator">|</span> {<span class="token keyword">DATA</span> <span class="token operator">|</span> <span class="token keyword">INDEX</span>} <span class="token keyword">DIRECTORY</span> <span class="token punctuation">[</span><span class="token operator">=</span><span class="token punctuation">]</span> <span class="token string">'<em class="replaceable">absolute path to directory</em>'</span>
  <span class="token operator">|</span> <span class="token keyword">DELAY_KEY_WRITE</span> <span class="token punctuation">[</span><span class="token operator">=</span><span class="token punctuation">]</span> {<span class="token number">0</span> <span class="token operator">|</span> <span class="token number">1</span>}
  <span class="token operator">|</span> <span class="token keyword">ENCRYPTION</span> <span class="token punctuation">[</span><span class="token operator">=</span><span class="token punctuation">]</span> {<span class="token string">'Y'</span> <span class="token operator">|</span> <span class="token string">'N'</span>}
  <span class="token operator">|</span> <span class="token keyword">ENGINE</span> <span class="token punctuation">[</span><span class="token operator">=</span><span class="token punctuation">]</span> <em class="replaceable">engine_name</em>
  <span class="token operator">|</span> <span class="token keyword">ENGINE_ATTRIBUTE</span> <span class="token punctuation">[</span><span class="token operator">=</span><span class="token punctuation">]</span> <span class="token string">'<em class="replaceable">string</em>'</span>
  <span class="token operator">|</span> <span class="token keyword">INSERT_METHOD</span> <span class="token punctuation">[</span><span class="token operator">=</span><span class="token punctuation">]</span> { <span class="token keyword">NO</span> <span class="token operator">|</span> <span class="token keyword">FIRST</span> <span class="token operator">|</span> <span class="token keyword">LAST</span> }
  <span class="token operator">|</span> <span class="token keyword">KEY_BLOCK_SIZE</span> <span class="token punctuation">[</span><span class="token operator">=</span><span class="token punctuation">]</span> <span class="token keyword"><em class="replaceable">value</em></span>
  <span class="token operator">|</span> <span class="token keyword">MAX_ROWS</span> <span class="token punctuation">[</span><span class="token operator">=</span><span class="token punctuation">]</span> <span class="token keyword"><em class="replaceable">value</em></span>
  <span class="token operator">|</span> <span class="token keyword">MIN_ROWS</span> <span class="token punctuation">[</span><span class="token operator">=</span><span class="token punctuation">]</span> <span class="token keyword"><em class="replaceable">value</em></span>
  <span class="token operator">|</span> <span class="token keyword">PACK_KEYS</span> <span class="token punctuation">[</span><span class="token operator">=</span><span class="token punctuation">]</span> {<span class="token number">0</span> <span class="token operator">|</span> <span class="token number">1</span> <span class="token operator">|</span> <span class="token keyword">DEFAULT</span>}
  <span class="token operator">|</span> <span class="token keyword">PASSWORD</span> <span class="token punctuation">[</span><span class="token operator">=</span><span class="token punctuation">]</span> <span class="token string">'<em class="replaceable">string</em>'</span>
  <span class="token operator">|</span> <span class="token keyword">ROW_FORMAT</span> <span class="token punctuation">[</span><span class="token operator">=</span><span class="token punctuation">]</span> {<span class="token keyword">DEFAULT</span> <span class="token operator">|</span> <span class="token keyword">DYNAMIC</span> <span class="token operator">|</span> <span class="token keyword">FIXED</span> <span class="token operator">|</span> <span class="token keyword">COMPRESSED</span> <span class="token operator">|</span> <span class="token keyword">REDUNDANT</span> <span class="token operator">|</span> <span class="token keyword">COMPACT</span>}
  <span class="token operator">|</span> <span class="token keyword">START</span> <span class="token keyword">TRANSACTION</span> 
  <span class="token operator">|</span> <span class="token keyword">SECONDARY_ENGINE_ATTRIBUTE</span> <span class="token punctuation">[</span><span class="token operator">=</span><span class="token punctuation">]</span> <span class="token string">'<em class="replaceable">string</em>'</span>
  <span class="token operator">|</span> <span class="token keyword">STATS_AUTO_RECALC</span> <span class="token punctuation">[</span><span class="token operator">=</span><span class="token punctuation">]</span> {<span class="token keyword">DEFAULT</span> <span class="token operator">|</span> <span class="token number">0</span> <span class="token operator">|</span> <span class="token number">1</span>}
  <span class="token operator">|</span> <span class="token keyword">STATS_PERSISTENT</span> <span class="token punctuation">[</span><span class="token operator">=</span><span class="token punctuation">]</span> {<span class="token keyword">DEFAULT</span> <span class="token operator">|</span> <span class="token number">0</span> <span class="token operator">|</span> <span class="token number">1</span>}
  <span class="token operator">|</span> <span class="token keyword">STATS_SAMPLE_PAGES</span> <span class="token punctuation">[</span><span class="token operator">=</span><span class="token punctuation">]</span> <span class="token keyword"><em class="replaceable">value</em></span>
  <span class="token operator">|</span> <em class="replaceable">tablespace_option</em>
  <span class="token operator">|</span> <span class="token keyword">UNION</span> <span class="token punctuation">[</span><span class="token operator">=</span><span class="token punctuation">]</span> <span class="token punctuation">(</span><em class="replaceable">tbl_name</em><span class="token punctuation">[</span><span class="token punctuation">,</span><em class="replaceable">tbl_name</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>
}

<em class="replaceable">partition_options</em>:
    <span class="token keyword">PARTITION</span> <span class="token keyword">BY</span>
        { <span class="token punctuation">[</span><span class="token keyword">LINEAR</span><span class="token punctuation">]</span> <span class="token keyword">HASH</span><span class="token punctuation">(</span><em class="replaceable">expr</em><span class="token punctuation">)</span>
        <span class="token operator">|</span> <span class="token punctuation">[</span><span class="token keyword">LINEAR</span><span class="token punctuation">]</span> <span class="token keyword">KEY</span> <span class="token punctuation">[</span><span class="token keyword">ALGORITHM</span><span class="token operator">=</span>{<span class="token number">1</span> <span class="token operator">|</span> <span class="token number">2</span>}<span class="token punctuation">]</span> <span class="token punctuation">(</span><em class="replaceable">column_list</em><span class="token punctuation">)</span>
        <span class="token operator">|</span> <span class="token keyword">RANGE</span>{<span class="token punctuation">(</span><em class="replaceable">expr</em><span class="token punctuation">)</span> <span class="token operator">|</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 operator">|</span> <span class="token keyword">LIST</span>{<span class="token punctuation">(</span><em class="replaceable">expr</em><span class="token punctuation">)</span> <span class="token operator">|</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">PARTITIONS</span> <em class="replaceable">num</em><span class="token punctuation">]</span>
    <span class="token punctuation">[</span><span class="token keyword">SUBPARTITION</span> <span class="token keyword">BY</span>
        { <span class="token punctuation">[</span><span class="token keyword">LINEAR</span><span class="token punctuation">]</span> <span class="token keyword">HASH</span><span class="token punctuation">(</span><em class="replaceable">expr</em><span class="token punctuation">)</span>
        <span class="token operator">|</span> <span class="token punctuation">[</span><span class="token keyword">LINEAR</span><span class="token punctuation">]</span> <span class="token keyword">KEY</span> <span class="token punctuation">[</span><span class="token keyword">ALGORITHM</span><span class="token operator">=</span>{<span class="token number">1</span> <span class="token operator">|</span> <span class="token number">2</span>}<span class="token punctuation">]</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">SUBPARTITIONS</span> <em class="replaceable">num</em><span class="token punctuation">]</span>
    <span class="token punctuation">]</span>
    <span class="token punctuation">[</span><span class="token punctuation">(</span><em class="replaceable">partition_definition</em> <span class="token punctuation">[</span><span class="token punctuation">,</span> <em class="replaceable">partition_definition</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>

<em class="replaceable">partition_definition</em>:
    <span class="token keyword">PARTITION</span> <em class="replaceable">partition_name</em>
        <span class="token punctuation">[</span><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">expr</em> <span class="token operator">|</span> <em class="replaceable">value_list</em><span class="token punctuation">)</span> <span class="token operator">|</span> <span class="token keyword">MAXVALUE</span>}
            <span class="token operator">|</span>
            <span class="token keyword">IN</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 keyword">STORAGE</span><span class="token punctuation">]</span> <span class="token keyword">ENGINE</span> <span class="token punctuation">[</span><span class="token operator">=</span><span class="token punctuation">]</span> <em class="replaceable">engine_name</em><span class="token punctuation">]</span>
        <span class="token punctuation">[</span><span class="token keyword">COMMENT</span> <span class="token punctuation">[</span><span class="token operator">=</span><span class="token punctuation">]</span> <span class="token string">'<em class="replaceable">string</em>'</span> <span class="token punctuation">]</span>
        <span class="token punctuation">[</span><span class="token keyword">DATA</span> <span class="token keyword">DIRECTORY</span> <span class="token punctuation">[</span><span class="token operator">=</span><span class="token punctuation">]</span> <span class="token string">'<em class="replaceable">data_dir</em>'</span><span class="token punctuation">]</span>
        <span class="token punctuation">[</span><span class="token keyword">INDEX</span> <span class="token keyword">DIRECTORY</span> <span class="token punctuation">[</span><span class="token operator">=</span><span class="token punctuation">]</span> <span class="token string">'<em class="replaceable">index_dir</em>'</span><span class="token punctuation">]</span>
        <span class="token punctuation">[</span><span class="token keyword">MAX_ROWS</span> <span class="token punctuation">[</span><span class="token operator">=</span><span class="token punctuation">]</span> <em class="replaceable">max_number_of_rows</em><span class="token punctuation">]</span>
        <span class="token punctuation">[</span><span class="token keyword">MIN_ROWS</span> <span class="token punctuation">[</span><span class="token operator">=</span><span class="token punctuation">]</span> <em class="replaceable">min_number_of_rows</em><span class="token punctuation">]</span>
        <span class="token punctuation">[</span><span class="token keyword">TABLESPACE</span> <span class="token punctuation">[</span><span class="token operator">=</span><span class="token punctuation">]</span> tablespace_name<span class="token punctuation">]</span>
        <span class="token punctuation">[</span><span class="token punctuation">(</span><em class="replaceable">subpartition_definition</em> <span class="token punctuation">[</span><span class="token punctuation">,</span> <em class="replaceable">subpartition_definition</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>

<em class="replaceable">subpartition_definition</em>:
    <span class="token keyword">SUBPARTITION</span> <em class="replaceable">logical_name</em>
        <span class="token punctuation">[</span><span class="token punctuation">[</span><span class="token keyword">STORAGE</span><span class="token punctuation">]</span> <span class="token keyword">ENGINE</span> <span class="token punctuation">[</span><span class="token operator">=</span><span class="token punctuation">]</span> <em class="replaceable">engine_name</em><span class="token punctuation">]</span>
        <span class="token punctuation">[</span><span class="token keyword">COMMENT</span> <span class="token punctuation">[</span><span class="token operator">=</span><span class="token punctuation">]</span> <span class="token string">'<em class="replaceable">string</em>'</span> <span class="token punctuation">]</span>
        <span class="token punctuation">[</span><span class="token keyword">DATA</span> <span class="token keyword">DIRECTORY</span> <span class="token punctuation">[</span><span class="token operator">=</span><span class="token punctuation">]</span> <span class="token string">'<em class="replaceable">data_dir</em>'</span><span class="token punctuation">]</span>
        <span class="token punctuation">[</span><span class="token keyword">INDEX</span> <span class="token keyword">DIRECTORY</span> <span class="token punctuation">[</span><span class="token operator">=</span><span class="token punctuation">]</span> <span class="token string">'<em class="replaceable">index_dir</em>'</span><span class="token punctuation">]</span>
        <span class="token punctuation">[</span><span class="token keyword">MAX_ROWS</span> <span class="token punctuation">[</span><span class="token operator">=</span><span class="token punctuation">]</span> <em class="replaceable">max_number_of_rows</em><span class="token punctuation">]</span>
        <span class="token punctuation">[</span><span class="token keyword">MIN_ROWS</span> <span class="token punctuation">[</span><span class="token operator">=</span><span class="token punctuation">]</span> <em class="replaceable">min_number_of_rows</em><span class="token punctuation">]</span>
        <span class="token punctuation">[</span><span class="token keyword">TABLESPACE</span> <span class="token punctuation">[</span><span class="token operator">=</span><span class="token punctuation">]</span> tablespace_name<span class="token punctuation">]</span>

<em class="replaceable">tablespace_option</em>:
    <span class="token keyword">TABLESPACE</span> <em class="replaceable">tablespace_name</em> <span class="token punctuation">[</span><span class="token keyword">STORAGE</span> <span class="token keyword">DISK</span><span class="token punctuation">]</span>
  <span class="token operator">|</span> <span class="token punctuation">[</span><span class="token keyword">TABLESPACE</span> <em class="replaceable">tablespace_name</em><span class="token punctuation">]</span> <span class="token keyword">STORAGE</span> <span class="token keyword">MEMORY</span>

<em class="replaceable">query_expression:</em>
    <span class="token keyword">SELECT</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>   <span class="token punctuation">(</span><span class="token keyword"></span><em class="replaceable"><span class="token keyword">Some</span> valid <span class="token keyword">select</span> <span class="token operator">or</span> <span class="token keyword">union</span> statement</em><span class="token punctuation">)</span></code></pre>
  </div>
  <p>
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE TABLE
    </code>
   </a>
   creates a table with
      the given name. You must have the
   <a class="link" href="privileges-provided.html#priv_create">
    <code class="literal">
     CREATE
    </code>
   </a>
   privilege for the table.
  </p>
  <p>
   By default, tables are created in the default database, using the
   <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
    <code class="literal">
     InnoDB
    </code>
   </a>
   storage engine. An error
      occurs if the table exists, if there is no default database, or if
      the database does not exist.
  </p>
  <a class="indexterm" name="idm46045186561072">
  </a>
  <a class="indexterm" name="idm46045186559568">
  </a>
  <p>
   MySQL has no limit on the number of tables. The underlying file
      system may have a limit on the number of files that represent
      tables. Individual storage engines may impose engine-specific
      constraints.
   <code class="literal">
    InnoDB
   </code>
   permits up to 4 billion
      tables.
  </p>
  <p>
   For information about the physical representation of a table, see
   <a class="xref" href="create-table-files.html" title="15.1.20.1 Files Created by CREATE TABLE">
    Section 15.1.20.1, “Files Created by CREATE TABLE”
   </a>
   .
  </p>
  <p>
   There are several aspects to the
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE
      TABLE
    </code>
   </a>
   statement, described under the following topics in
      this section:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="xref" href="create-table.html#create-table-name" title="Table Name">
       Table Name
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="create-table.html#create-table-temporary-tables" title="Temporary Tables">
       Temporary Tables
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="create-table.html#create-table-clone-copy" title="Table Cloning and Copying">
       Table Cloning and Copying
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="create-table.html#create-table-types-attributes" title="Column Data Types and Attributes">
       Column Data Types and Attributes
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="create-table.html#create-table-indexes-keys" title="Indexes, Foreign Keys, and CHECK Constraints">
       Indexes, Foreign Keys, and CHECK Constraints
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="create-table.html#create-table-options" title="Table Options">
       Table Options
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="create-table.html#create-table-partitioning" title="Table Partitioning">
       Table Partitioning
      </a>
     </p>
    </li>
   </ul>
  </div>
  <h4>
   <a name="create-table-name">
   </a>
   Table Name
  </h4>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       <em class="replaceable">
        <code>
         tbl_name
        </code>
       </em>
      </code>
     </p>
     <p>
      The table name can be specified as
      <em class="replaceable">
       <code>
        db_name.tbl_name
       </code>
      </em>
      to create the
          table in a specific database. This works regardless of whether
          there is a default database, assuming that the database
          exists. If you use quoted identifiers, quote the database and
          table names separately. For example, write
      <code class="literal">
       `mydb`.`mytbl`
      </code>
      , not
      <code class="literal">
       `mydb.mytbl`
      </code>
      .
     </p>
     <p>
      Rules for permissible table names are given in
      <a class="xref" href="identifiers.html" title="11.2 Schema Object Names">
       Section 11.2, “Schema Object Names”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       IF NOT EXISTS
      </code>
     </p>
     <p>
      Prevents an error from occurring if the table exists. However,
          there is no verification that the existing table has a
          structure identical to that indicated by the
      <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
       <code class="literal">
        CREATE TABLE
       </code>
      </a>
      statement.
     </p>
    </li>
   </ul>
  </div>
  <h4>
   <a name="create-table-temporary-tables">
   </a>
   Temporary Tables
  </h4>
  <p>
   You can use the
   <code class="literal">
    TEMPORARY
   </code>
   keyword when creating
      a table. A
   <code class="literal">
    TEMPORARY
   </code>
   table is visible only
      within the current session, and is dropped automatically when the
      session is closed. For more information, see
   <a class="xref" href="create-temporary-table.html" title="15.1.20.2 CREATE TEMPORARY TABLE Statement">
    Section 15.1.20.2, “CREATE TEMPORARY TABLE Statement”
   </a>
   .
  </p>
  <h4>
   <a name="create-table-clone-copy">
   </a>
   Table Cloning and Copying
  </h4>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       LIKE
      </code>
     </p>
     <p>
      Use
      <code class="literal">
       CREATE TABLE ... LIKE
      </code>
      to create an
          empty table based on the definition of another table,
          including any column attributes and indexes defined in the
          original table:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa12918098"><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> <em class="replaceable">new_tbl</em> <span class="token operator">LIKE</span> <em class="replaceable">orig_tbl</em><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      For more information, see
      <a class="xref" href="create-table-like.html" title="15.1.20.3 CREATE TABLE ... LIKE Statement">
       Section 15.1.20.3, “CREATE TABLE ... LIKE Statement”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       [AS]
       <em class="replaceable">
        <code>
         query_expression
        </code>
       </em>
      </code>
     </p>
     <p>
      To create one table from another, add a
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      statement at the end of
          the
      <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
       <code class="literal">
        CREATE TABLE
       </code>
      </a>
      statement:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa11989747"><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> <em class="replaceable">new_tbl</em> <span class="token keyword">AS</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> <em class="replaceable">orig_tbl</em><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      For more information, see
      <a class="xref" href="create-table-select.html" title="15.1.20.4 CREATE TABLE ... SELECT Statement">
       Section 15.1.20.4, “CREATE TABLE ... SELECT Statement”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       IGNORE | REPLACE
      </code>
     </p>
     <p>
      The
      <code class="literal">
       IGNORE
      </code>
      and
      <code class="literal">
       REPLACE
      </code>
      options indicate how to handle rows that duplicate unique key
          values when copying a table using a
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      statement.
     </p>
     <p>
      For more information, see
      <a class="xref" href="create-table-select.html" title="15.1.20.4 CREATE TABLE ... SELECT Statement">
       Section 15.1.20.4, “CREATE TABLE ... SELECT Statement”
      </a>
      .
     </p>
    </li>
   </ul>
  </div>
  <h4>
   <a name="create-table-types-attributes">
   </a>
   Column Data Types and Attributes
  </h4>
  <p>
   There is a hard limit of 4096 columns per table, but the effective
      maximum may be less for a given table and depends on the factors
      discussed in
   <a class="xref" href="column-count-limit.html" title="10.4.7 Limits on Table Column Count and Row Size">
    Section 10.4.7, “Limits on Table Column Count and Row Size”
   </a>
   .
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       <em class="replaceable">
        <code>
         data_type
        </code>
       </em>
      </code>
     </p>
     <p>
      <em class="replaceable">
       <code>
        data_type
       </code>
      </em>
      represents the data type
          in a column definition. For a full description of the syntax
          available for specifying column data types, as well as
          information about the properties of each type, see
      <a class="xref" href="data-types.html" title="Chapter 13 Data Types">
       Chapter 13,
       <i>
        Data Types
       </i>
      </a>
      .
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         <code class="literal">
          AUTO_INCREMENT
         </code>
         applies only to integer
              types.
        </p>
       </li>
       <li class="listitem">
        <p>
         Character data types (
         <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>
         , the
         <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
          <code class="literal">
           TEXT
          </code>
         </a>
         types,
         <a class="link" href="enum.html" title="13.3.5 The ENUM Type">
          <code class="literal">
           ENUM
          </code>
         </a>
         ,
         <a class="link" href="set.html" title="13.3.6 The SET Type">
          <code class="literal">
           SET
          </code>
         </a>
         , and any synonyms) can
              include
         <code class="literal">
          CHARACTER SET
         </code>
         to specify the
              character set for the column.
         <code class="literal">
          CHARSET
         </code>
         is a synonym for
         <code class="literal">
          CHARACTER SET
         </code>
         . A
              collation for the character set can be specified with the
         <code class="literal">
          COLLATE
         </code>
         attribute, along with any other
              attributes. For details, see
         <a class="xref" href="charset.html" title="Chapter 12 Character Sets, Collations, Unicode">
          Chapter 12,
          <i>
           Character Sets, Collations, Unicode
          </i>
         </a>
         .
              Example:
        </p>
        <div class="copytoclipboard-wrapper">
         <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa84979021"><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> t <span class="token punctuation">(</span>c <span class="token datatype">CHAR</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span> <span class="token keyword">CHARACTER</span> <span class="token keyword">SET</span> utf8mb4 <span class="token keyword">COLLATE</span> utf8mb4_bin<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
        </div>
        <p>
         MySQL 8.4 interprets length specifications in
              character column definitions in characters. Lengths for
         <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>
         are in bytes.
        </p>
       </li>
       <li class="listitem">
        <p>
         For
         <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, indexes
              can be created that use only the leading part of column
              values, using
         <code class="literal">
          <em class="replaceable">
           <code>
            col_name
           </code>
          </em>
          (
          <em class="replaceable">
           <code>
            length
           </code>
          </em>
          )
         </code>
         syntax to specify an index prefix length.
         <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 also can be
              indexed, but a prefix length
         <span class="emphasis">
          <em>
           must
          </em>
         </span>
         be
              given. Prefix lengths are given in characters for
              nonbinary string types and in bytes for binary string
              types. That is, index entries consist of the first
         <em class="replaceable">
          <code>
           length
          </code>
         </em>
         characters of each
              column value for
         <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>
         , and
         <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
          <code class="literal">
           TEXT
          </code>
         </a>
         columns, and the first
         <em class="replaceable">
          <code>
           length
          </code>
         </em>
         bytes of each column
              value for
         <a class="link" href="binary-varbinary.html" title="13.3.3 The BINARY and VARBINARY Types">
          <code class="literal">
           BINARY
          </code>
         </a>
         ,
         <a class="link" href="binary-varbinary.html" title="13.3.3 The BINARY and VARBINARY Types">
          <code class="literal">
           VARBINARY
          </code>
         </a>
         , and
         <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
          <code class="literal">
           BLOB
          </code>
         </a>
         columns. Indexing only
              a prefix of column values like this can make the index
              file much smaller. For additional information about index
              prefixes, see
         <a class="xref" href="create-index.html" title="15.1.15 CREATE INDEX Statement">
          Section 15.1.15, “CREATE INDEX Statement”
         </a>
         .
        </p>
        <a class="indexterm" name="idm46045186468928">
        </a>
        <a class="indexterm" name="idm46045186467440">
        </a>
        <a class="indexterm" name="idm46045186465952">
        </a>
        <a class="indexterm" name="idm46045186464464">
        </a>
        <p>
         Only the
         <code class="literal">
          InnoDB
         </code>
         and
         <code class="literal">
          MyISAM
         </code>
         storage engines support indexing
              on
         <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. For example:
        </p>
        <div class="copytoclipboard-wrapper">
         <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa22760630"><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> test <span class="token punctuation">(</span>blob_col <span class="token datatype">BLOB</span><span class="token punctuation">,</span> <span class="token keyword">INDEX</span><span class="token punctuation">(</span>blob_col<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 punctuation">;</span></code></pre>
        </div>
        <p>
         If a specified index prefix exceeds the maximum column
              data type size,
         <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
          <code class="literal">
           CREATE
              TABLE
          </code>
         </a>
         handles the index as follows:
        </p>
        <div class="itemizedlist">
         <ul class="itemizedlist" style="list-style-type: square; ">
          <li class="listitem">
           <p>
            For a nonunique index, either an error occurs (if
                  strict SQL mode is enabled), or the index length is
                  reduced to lie within the maximum column data type
                  size and a warning is produced (if strict SQL mode is
                  not enabled).
           </p>
          </li>
          <li class="listitem">
           <p>
            For a unique index, an error occurs regardless of SQL
                  mode because reducing the index length might enable
                  insertion of nonunique entries that do not meet the
                  specified uniqueness requirement.
           </p>
          </li>
         </ul>
        </div>
       </li>
       <li class="listitem">
        <p>
         <a class="link" href="json.html" title="13.5 The JSON Data Type">
          <code class="literal">
           JSON
          </code>
         </a>
         columns cannot be
              indexed. You can work around this restriction by creating
              an index on a generated column that extracts a scalar
              value from the
         <code class="literal">
          JSON
         </code>
         column. See
         <a class="xref" href="create-table-secondary-indexes.html#json-column-indirect-index" title="Indexing a Generated Column to Provide a JSON Column Index">
          Indexing a Generated Column to Provide a JSON Column Index
         </a>
         , for a
              detailed example.
        </p>
       </li>
      </ul>
     </div>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       NOT NULL | NULL
      </code>
     </p>
     <a class="indexterm" name="idm46045186447856">
     </a>
     <a class="indexterm" name="idm46045186446368">
     </a>
     <p>
      If neither
      <code class="literal">
       NULL
      </code>
      nor
      <code class="literal">
       NOT
          NULL
      </code>
      is specified, the column is treated as though
      <code class="literal">
       NULL
      </code>
      had been specified.
     </p>
     <p>
      In MySQL 8.4, only the
      <code class="literal">
       InnoDB
      </code>
      ,
      <code class="literal">
       MyISAM
      </code>
      , and
      <code class="literal">
       MEMORY
      </code>
      storage engines support indexes on columns that can have
      <code class="literal">
       NULL
      </code>
      values. In other cases, you must
          declare indexed columns as
      <code class="literal">
       NOT NULL
      </code>
      or an
          error results.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       DEFAULT
      </code>
     </p>
     <a class="indexterm" name="idm46045186436656">
     </a>
     <a class="indexterm" name="idm46045186435584">
     </a>
     <p>
      Specifies a default value for a column. For more information
          about default value handling, including the case that a column
          definition includes no explicit
      <code class="literal">
       DEFAULT
      </code>
      value, see
      <a class="xref" href="data-type-defaults.html" title="13.6 Data Type Default Values">
       Section 13.6, “Data Type Default Values”
      </a>
      .
     </p>
     <p>
      If the
      <a class="link" href="sql-mode.html#sqlmode_no_zero_date">
       <code class="literal">
        NO_ZERO_DATE
       </code>
      </a>
      or
      <a class="link" href="sql-mode.html#sqlmode_no_zero_in_date">
       <code class="literal">
        NO_ZERO_IN_DATE
       </code>
      </a>
      SQL mode is
          enabled and a date-valued default is not correct according to
          that mode,
      <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
       <code class="literal">
        CREATE TABLE
       </code>
      </a>
      produces a warning if strict SQL mode is not enabled and an
          error if strict mode is enabled. For example, with
      <a class="link" href="sql-mode.html#sqlmode_no_zero_in_date">
       <code class="literal">
        NO_ZERO_IN_DATE
       </code>
      </a>
      enabled,
      <code class="literal">
       c1 DATE DEFAULT '2010-00-00'
      </code>
      produces a
          warning.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       VISIBLE
      </code>
      ,
      <code class="literal">
       INVISIBLE
      </code>
     </p>
     <p>
      Specify column visibility. The default is
      <code class="literal">
       VISIBLE
      </code>
      if neither keyword is present. A
          table must have at least one visible column. Attempting to
          make all columns invisible produces an error. For more
          information, see
      <a class="xref" href="invisible-columns.html" title="15.1.20.10 Invisible Columns">
       Section 15.1.20.10, “Invisible Columns”
      </a>
      .
     </p>
     <a class="indexterm" name="idm46045186422032">
     </a>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       AUTO_INCREMENT
      </code>
     </p>
     <p>
      An integer column can have the additional attribute
      <code class="literal">
       AUTO_INCREMENT
      </code>
      . When you insert a value of
      <code class="literal">
       NULL
      </code>
      (recommended) or
      <code class="literal">
       0
      </code>
      into an indexed
      <code class="literal">
       AUTO_INCREMENT
      </code>
      column, the
          column is set to the next sequence value. Typically this is
      <code class="literal">
       <em class="replaceable">
        <code>
         value
        </code>
       </em>
       +1
      </code>
      , where
      <em class="replaceable">
       <code>
        value
       </code>
      </em>
      is the largest value for the
          column currently in the table.
      <code class="literal">
       AUTO_INCREMENT
      </code>
      sequences begin with
      <code class="literal">
       1
      </code>
      .
     </p>
     <p>
      To retrieve an
      <code class="literal">
       AUTO_INCREMENT
      </code>
      value after
          inserting a row, use the
      <a class="link" href="information-functions.html#function_last-insert-id">
       <code class="literal">
        LAST_INSERT_ID()
       </code>
      </a>
      SQL function
          or the
      <a class="ulink" href="/doc/c-api/8.4/en/mysql-insert-id.html" target="_top">
       <code class="literal">
        mysql_insert_id()
       </code>
      </a>
      C API
          function. See
      <a class="xref" href="information-functions.html" title="14.15 Information Functions">
       Section 14.15, “Information Functions”
      </a>
      , and
      <a class="ulink" href="/doc/c-api/8.4/en/mysql-insert-id.html" target="_top">
       mysql_insert_id()
      </a>
      .
     </p>
     <p>
      If the
      <a class="link" href="sql-mode.html#sqlmode_no_auto_value_on_zero">
       <code class="literal">
        NO_AUTO_VALUE_ON_ZERO
       </code>
      </a>
      SQL mode is enabled, you can store
      <code class="literal">
       0
      </code>
      in
      <code class="literal">
       AUTO_INCREMENT
      </code>
      columns as
      <code class="literal">
       0
      </code>
      without generating a new sequence value.
          See
      <a class="xref" href="sql-mode.html" title="7.1.11 Server SQL Modes">
       Section 7.1.11, “Server SQL Modes”
      </a>
      .
     </p>
     <p>
      There can be only one
      <code class="literal">
       AUTO_INCREMENT
      </code>
      column
          per table, it must be indexed, and it cannot have a
      <code class="literal">
       DEFAULT
      </code>
      value. An
      <code class="literal">
       AUTO_INCREMENT
      </code>
      column works properly only
          if it contains only positive values. Inserting a negative
          number is regarded as inserting a very large positive number.
          This is done to avoid precision problems when numbers
      <span class="quote">
       “
       <span class="quote">
        wrap
       </span>
       ”
      </span>
      over from positive to negative and also to
          ensure that you do not accidentally get an
      <code class="literal">
       AUTO_INCREMENT
      </code>
      column that contains
      <code class="literal">
       0
      </code>
      .
     </p>
     <p>
      For
      <code class="literal">
       MyISAM
      </code>
      tables, you can specify an
      <code class="literal">
       AUTO_INCREMENT
      </code>
      secondary column in a
          multiple-column key. See
      <a class="xref" href="example-auto-increment.html" title="5.6.9 Using AUTO_INCREMENT">
       Section 5.6.9, “Using AUTO_INCREMENT”
      </a>
      .
     </p>
     <a class="indexterm" name="idm46045186395984">
     </a>
     <a class="indexterm" name="idm46045186394912">
     </a>
     <p>
      To make MySQL compatible with some ODBC applications, you can
          find the
      <code class="literal">
       AUTO_INCREMENT
      </code>
      value for the last
          inserted row with the following query:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa81083193"><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 operator">*</span> <span class="token keyword">FROM</span> <em class="replaceable">tbl_name</em> <span class="token keyword">WHERE</span> <em class="replaceable">auto_col</em> <span class="token operator">IS</span> <span class="token boolean">NULL</span></code></pre>
     </div>
     <p>
      This method requires that
      <a class="link" href="server-system-variables.html#sysvar_sql_auto_is_null">
       <code class="literal">
        sql_auto_is_null
       </code>
      </a>
      variable is
          not set to 0. See
      <a class="xref" href="server-system-variables.html" title="7.1.8 Server System Variables">
       Section 7.1.8, “Server System Variables”
      </a>
      .
     </p>
     <p>
      For information about
      <code class="literal">
       InnoDB
      </code>
      and
      <code class="literal">
       AUTO_INCREMENT
      </code>
      , see
      <a class="xref" href="innodb-auto-increment-handling.html" title="17.6.1.6 AUTO_INCREMENT Handling in InnoDB">
       Section 17.6.1.6, “AUTO_INCREMENT Handling in InnoDB”
      </a>
      . For
          information about
      <code class="literal">
       AUTO_INCREMENT
      </code>
      and MySQL
          Replication, see
      <a class="xref" href="replication-features-auto-increment.html" title="19.5.1.1 Replication and AUTO_INCREMENT">
       Section 19.5.1.1, “Replication and AUTO_INCREMENT”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       COMMENT
      </code>
     </p>
     <a class="indexterm" name="idm46045186382384">
     </a>
     <p>
      A comment for a column can be specified with the
      <code class="literal">
       COMMENT
      </code>
      option, up to 1024 characters long.
          The comment is displayed by the
      <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>
      and
      <a class="link" href="show-columns.html" title="15.7.7.6 SHOW COLUMNS Statement">
       <code class="literal">
        SHOW FULL
          COLUMNS
       </code>
      </a>
      statements. It is also shown in the
      <code class="literal">
       COLUMN_COMMENT
      </code>
      column of the Information
          Schema
      <a class="link" href="information-schema-columns-table.html" title="28.3.8 The INFORMATION_SCHEMA COLUMNS Table">
       <code class="literal">
        COLUMNS
       </code>
      </a>
      table.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       COLUMN_FORMAT
      </code>
     </p>
     <a class="indexterm" name="idm46045186374064">
     </a>
     <p>
      In NDB Cluster, it is also possible to specify a data storage
          format for individual columns of
      <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
       <code class="literal">
        NDB
       </code>
      </a>
      tables using
      <code class="literal">
       COLUMN_FORMAT
      </code>
      . Permissible column formats
          are
      <code class="literal">
       FIXED
      </code>
      ,
      <code class="literal">
       DYNAMIC
      </code>
      , and
      <code class="literal">
       DEFAULT
      </code>
      .
      <code class="literal">
       FIXED
      </code>
      is used
          to specify fixed-width storage,
      <code class="literal">
       DYNAMIC
      </code>
      permits the column to be variable-width, and
      <code class="literal">
       DEFAULT
      </code>
      causes the column to use
          fixed-width or variable-width storage as determined by the
          column's data type (possibly overridden by a
      <code class="literal">
       ROW_FORMAT
      </code>
      specifier).
     </p>
     <p>
      For
      <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
       <code class="literal">
        NDB
       </code>
      </a>
      tables, the default value
          for
      <code class="literal">
       COLUMN_FORMAT
      </code>
      is
      <code class="literal">
       FIXED
      </code>
      .
     </p>
     <p>
      In NDB Cluster, the maximum possible offset for a column
          defined with
      <code class="literal">
       COLUMN_FORMAT=FIXED
      </code>
      is 8188
          bytes. For more information and possible workarounds, see
      <a class="xref" href="mysql-cluster-limitations-database-objects.html" title="25.2.7.5 Limits Associated with Database Objects in NDB Cluster">
       Section 25.2.7.5, “Limits Associated with Database Objects in NDB Cluster”
      </a>
      .
     </p>
     <p>
      <code class="literal">
       COLUMN_FORMAT
      </code>
      currently has no effect on
          columns of tables using storage engines other than
      <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
       <code class="literal">
        NDB
       </code>
      </a>
      . MySQL 8.4
          silently ignores
      <code class="literal">
       COLUMN_FORMAT
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       ENGINE_ATTRIBUTE
      </code>
      and
      <code class="literal">
       SECONDARY_ENGINE_ATTRIBUTE
      </code>
      options are used
          to specify column attributes for primary and secondary storage
          engines. The options are reserved for future use.
     </p>
     <p>
      The value assigned to this option is a string literal
          containing a valid JSON document or an empty string ('').
          Invalid JSON is rejected.
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa16901957"><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> t1 <span class="token punctuation">(</span>c1 <span class="token datatype">INT</span> <span class="token keyword">ENGINE_ATTRIBUTE</span><span class="token operator">=</span><span class="token string">'{"<em class="replaceable">key</em>":"<em class="replaceable">value</em>"}'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      <code class="literal">
       ENGINE_ATTRIBUTE
      </code>
      and
      <code class="literal">
       SECONDARY_ENGINE_ATTRIBUTE
      </code>
      values can be
          repeated without error. In this case, the last specified value
          is used.
     </p>
     <p>
      <code class="literal">
       ENGINE_ATTRIBUTE
      </code>
      and
      <code class="literal">
       SECONDARY_ENGINE_ATTRIBUTE
      </code>
      values are not
          checked by the server, nor are they cleared when the
          table's storage engine is changed.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       STORAGE
      </code>
     </p>
     <a class="indexterm" name="idm46045186346864">
     </a>
     <p>
      For
      <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
       <code class="literal">
        NDB
       </code>
      </a>
      tables, it is possible to
          specify whether the column is stored on disk or in memory by
          using a
      <code class="literal">
       STORAGE
      </code>
      clause.
      <code class="literal">
       STORAGE
          DISK
      </code>
      causes the column to be stored on disk, and
      <code class="literal">
       STORAGE MEMORY
      </code>
      causes in-memory storage to
          be used. The
      <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
       <code class="literal">
        CREATE TABLE
       </code>
      </a>
      statement used must still include a
      <code class="literal">
       TABLESPACE
      </code>
      clause:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa83009651"><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> t1 <span class="token punctuation">(</span>
<span class="token prompt">    -&gt;</span>     c1 <span class="token datatype">INT</span> <span class="token keyword">STORAGE</span> <span class="token keyword">DISK</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>     c2 <span class="token datatype">INT</span> <span class="token keyword">STORAGE</span> <span class="token keyword">MEMORY</span>
<span class="token prompt">    -&gt;</span> <span class="token punctuation">)</span> <span class="token keyword">ENGINE</span> <span class="token keyword">NDB</span><span class="token punctuation">;</span>
<span class="token output">ERROR 1005 (HY000)<span class="token punctuation">:</span> Can't create table 'c.t1' (errno<span class="token punctuation">:</span> 140)
</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t1 <span class="token punctuation">(</span>
<span class="token prompt">    -&gt;</span>     c1 <span class="token datatype">INT</span> <span class="token keyword">STORAGE</span> <span class="token keyword">DISK</span><span class="token punctuation">,</span>
<span class="token prompt">    -&gt;</span>     c2 <span class="token datatype">INT</span> <span class="token keyword">STORAGE</span> <span class="token keyword">MEMORY</span>
<span class="token prompt">    -&gt;</span> <span class="token punctuation">)</span> <span class="token keyword">TABLESPACE</span> ts_1 <span class="token keyword">ENGINE</span> <span class="token keyword">NDB</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (1.06 sec)</span></code></pre>
     </div>
     <p>
      For
      <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
       <code class="literal">
        NDB
       </code>
      </a>
      tables,
      <code class="literal">
       STORAGE
          DEFAULT
      </code>
      is equivalent to
      <code class="literal">
       STORAGE
          MEMORY
      </code>
      .
     </p>
     <p>
      The
      <code class="literal">
       STORAGE
      </code>
      clause has no effect on tables
          using storage engines other than
      <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
       <code class="literal">
        NDB
       </code>
      </a>
      . The
      <code class="literal">
       STORAGE
      </code>
      keyword is supported only in the
          build of
      <a class="link" href="mysqld.html" title="6.3.1 mysqld — The MySQL Server">
       <span class="command">
        <strong>
         mysqld
        </strong>
       </span>
      </a>
      that is supplied with NDB
          Cluster; it is not recognized in any other version of MySQL,
          where any attempt to use the
      <code class="literal">
       STORAGE
      </code>
      keyword causes a syntax error.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       GENERATED ALWAYS
      </code>
     </p>
     <p>
      Used to specify a generated column expression. For information
          about
      <a class="link" href="glossary.html#glos_generated_column" title="generated column">
       generated
          columns
      </a>
      , see
      <a class="xref" href="create-table-generated-columns.html" title="15.1.20.8 CREATE TABLE and Generated Columns">
       Section 15.1.20.8, “CREATE TABLE and Generated Columns”
      </a>
      .
     </p>
     <p>
      <a class="link" href="glossary.html#glos_stored_generated_column" title="stored generated column">
       Stored generated
          columns
      </a>
      can be indexed.
      <code class="literal">
       InnoDB
      </code>
      supports secondary indexes on
      <a class="link" href="glossary.html#glos_virtual_generated_column" title="virtual generated column">
       virtual
          generated columns
      </a>
      . See
      <a class="xref" href="create-table-secondary-indexes.html" title="15.1.20.9 Secondary Indexes and Generated Columns">
       Section 15.1.20.9, “Secondary Indexes and Generated Columns”
      </a>
      .
     </p>
    </li>
   </ul>
  </div>
  <h4>
   <a name="create-table-indexes-keys">
   </a>
   Indexes, Foreign Keys, and CHECK Constraints
  </h4>
  <p>
   Several keywords apply to creation of indexes, foreign keys, and
   <code class="literal">
    CHECK
   </code>
   constraints. For general background in
      addition to the following descriptions, see
   <a class="xref" href="create-index.html" title="15.1.15 CREATE INDEX Statement">
    Section 15.1.15, “CREATE INDEX Statement”
   </a>
   ,
   <a class="xref" href="create-table-foreign-keys.html" title="15.1.20.5 FOREIGN KEY Constraints">
    Section 15.1.20.5, “FOREIGN KEY Constraints”
   </a>
   , and
   <a class="xref" href="create-table-check-constraints.html" title="15.1.20.6 CHECK Constraints">
    Section 15.1.20.6, “CHECK Constraints”
   </a>
   .
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       CONSTRAINT
       <em class="replaceable">
        <code>
         symbol
        </code>
       </em>
      </code>
     </p>
     <p>
      The
      <code class="literal">
       CONSTRAINT
       <em class="replaceable">
        <code>
         symbol
        </code>
       </em>
      </code>
      clause may be
          given to name a constraint. If the clause is not given, or a
      <em class="replaceable">
       <code>
        symbol
       </code>
      </em>
      is not included following
          the
      <code class="literal">
       CONSTRAINT
      </code>
      keyword, MySQL automatically
          generates a constraint name, with the exception noted below.
          The
      <em class="replaceable">
       <code>
        symbol
       </code>
      </em>
      value, if used, must be
          unique per schema (database), per constraint type. A duplicate
      <em class="replaceable">
       <code>
        symbol
       </code>
      </em>
      results in an error. See
          also the discussion about length limits of generated
          constraint identifiers at
      <a class="xref" href="identifier-length.html" title="11.2.1 Identifier Length Limits">
       Section 11.2.1, “Identifier Length Limits”
      </a>
      .
     </p>
     <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
      <div class="admon-title">
       Note
      </div>
      <p>
       If the
       <code class="literal">
        CONSTRAINT
        <em class="replaceable">
         <code>
          symbol
         </code>
        </em>
       </code>
       clause is not
            given in a foreign key definition, or a
       <em class="replaceable">
        <code>
         symbol
        </code>
       </em>
       is not included following
            the
       <code class="literal">
        CONSTRAINT
       </code>
       keyword, MySQL
            automatically generates a constraint name.
      </p>
     </div>
     <p>
      The SQL standard specifies that all types of constraints
          (primary key, unique index, foreign key, check) belong to the
          same namespace. In MySQL, each constraint type has its own
          namespace per schema. Consequently, names for each type of
          constraint must be unique per schema, but constraints of
          different types can have the same name.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       PRIMARY KEY
      </code>
     </p>
     <a class="indexterm" name="idm46045186300288">
     </a>
     <p>
      A unique index where all key columns must be defined as
      <code class="literal">
       NOT NULL
      </code>
      . If they are not explicitly
          declared as
      <code class="literal">
       NOT NULL
      </code>
      , MySQL declares them
          so implicitly (and silently). A table can have only one
      <code class="literal">
       PRIMARY KEY
      </code>
      . The name of a
      <code class="literal">
       PRIMARY
          KEY
      </code>
      is always
      <code class="literal">
       PRIMARY
      </code>
      , which thus
          cannot be used as the name for any other kind of index.
     </p>
     <p>
      If you do not have a
      <code class="literal">
       PRIMARY KEY
      </code>
      and an
          application asks for the
      <code class="literal">
       PRIMARY KEY
      </code>
      in
          your tables, MySQL returns the first
      <code class="literal">
       UNIQUE
      </code>
      index that has no
      <code class="literal">
       NULL
      </code>
      columns as the
      <code class="literal">
       PRIMARY KEY
      </code>
      .
     </p>
     <p>
      In
      <code class="literal">
       InnoDB
      </code>
      tables, keep the
      <code class="literal">
       PRIMARY
          KEY
      </code>
      short to minimize storage overhead for secondary
          indexes. Each secondary index entry contains a copy of the
          primary key columns for the corresponding row. (See
      <a class="xref" href="innodb-index-types.html" title="17.6.2.1 Clustered and Secondary Indexes">
       Section 17.6.2.1, “Clustered and Secondary Indexes”
      </a>
      .)
     </p>
     <p>
      In the created table, a
      <code class="literal">
       PRIMARY KEY
      </code>
      is
          placed first, followed by all
      <code class="literal">
       UNIQUE
      </code>
      indexes, and then the nonunique indexes. This helps the MySQL
          optimizer to prioritize which index to use and also more
          quickly to detect duplicated
      <code class="literal">
       UNIQUE
      </code>
      keys.
     </p>
     <p>
      A
      <code class="literal">
       PRIMARY KEY
      </code>
      can be a multiple-column
          index. However, you cannot create a multiple-column index
          using the
      <code class="literal">
       PRIMARY KEY
      </code>
      key attribute in a
          column specification. Doing so only marks that single column
          as primary. You must use a separate
      <code class="literal">
       PRIMARY
          KEY(
       <em class="replaceable">
        <code>
         key_part
        </code>
       </em>
       , ...)
      </code>
      clause.
     </p>
     <a class="indexterm" name="idm46045186282080">
     </a>
     <p>
      If a table has a
      <code class="literal">
       PRIMARY KEY
      </code>
      or
      <code class="literal">
       UNIQUE NOT NULL
      </code>
      index that consists of a
          single column that has an integer type, you can use
      <code class="literal">
       _rowid
      </code>
      to refer to the indexed column in
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      statements, as described
          in
      <a class="xref" href="create-index.html#create-index-unique" title="Unique Indexes">
       Unique Indexes
      </a>
      .
     </p>
     <p>
      In MySQL, the name of a
      <code class="literal">
       PRIMARY KEY
      </code>
      is
      <code class="literal">
       PRIMARY
      </code>
      . For other indexes, if you do not
          assign a name, the index is assigned the same name as the
          first indexed column, with an optional suffix
          (
      <code class="literal">
       _2
      </code>
      ,
      <code class="literal">
       _3
      </code>
      ,
      <code class="literal">
       ...
      </code>
      ) to make it unique. You can see index
          names for a table using
      <code class="literal">
       SHOW INDEX FROM
       <em class="replaceable">
        <code>
         tbl_name
        </code>
       </em>
      </code>
      . See
      <a class="xref" href="show-index.html" title="15.7.7.23 SHOW INDEX Statement">
       Section 15.7.7.23, “SHOW INDEX Statement”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       KEY | INDEX
      </code>
     </p>
     <p>
      <code class="literal">
       KEY
      </code>
      is normally a synonym for
      <code class="literal">
       INDEX
      </code>
      . The key attribute
      <code class="literal">
       PRIMARY
          KEY
      </code>
      can also be specified as just
      <code class="literal">
       KEY
      </code>
      when given in a column definition. This
          was implemented for compatibility with other database systems.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       UNIQUE
      </code>
     </p>
     <p>
      A
      <code class="literal">
       UNIQUE
      </code>
      index creates a constraint such
          that all values in the index must be distinct. An error occurs
          if you try to add a new row with a key value that matches an
          existing row. For all engines, a
      <code class="literal">
       UNIQUE
      </code>
      index permits multiple
      <code class="literal">
       NULL
      </code>
      values for
          columns that can contain
      <code class="literal">
       NULL
      </code>
      . If you
          specify a prefix value for a column in a
      <code class="literal">
       UNIQUE
      </code>
      index, the column values must be
          unique within the prefix length.
     </p>
     <a class="indexterm" name="idm46045186259392">
     </a>
     <p>
      If a table has a
      <code class="literal">
       PRIMARY KEY
      </code>
      or
      <code class="literal">
       UNIQUE NOT NULL
      </code>
      index that consists of a
          single column that has an integer type, you can use
      <code class="literal">
       _rowid
      </code>
      to refer to the indexed column in
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      statements, as described
          in
      <a class="xref" href="create-index.html#create-index-unique" title="Unique Indexes">
       Unique Indexes
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       FULLTEXT
      </code>
     </p>
     <p>
      A
      <code class="literal">
       FULLTEXT
      </code>
      index is a special type of index
          used for full-text searches. Only the
      <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
       <code class="literal">
        InnoDB
       </code>
      </a>
      and
      <a class="link" href="myisam-storage-engine.html" title="18.2 The MyISAM Storage Engine">
       <code class="literal">
        MyISAM
       </code>
      </a>
      storage engines support
      <code class="literal">
       FULLTEXT
      </code>
      indexes. They can be created only
          from
      <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>
      , and
      <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
       <code class="literal">
        TEXT
       </code>
      </a>
      columns. Indexing always
          happens over the entire column; column prefix indexing is not
          supported and any prefix length is ignored if specified. See
      <a class="xref" href="fulltext-search.html" title="14.9 Full-Text Search Functions">
       Section 14.9, “Full-Text Search Functions”
      </a>
      , for details of operation. A
      <code class="literal">
       WITH PARSER
      </code>
      clause can be specified as an
      <em class="replaceable">
       <code>
        index_option
       </code>
      </em>
      value to associate a
          parser plugin with the index if full-text indexing and
          searching operations need special handling. This clause is
          valid only for
      <code class="literal">
       FULLTEXT
      </code>
      indexes.
      <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
       <code class="literal">
        InnoDB
       </code>
      </a>
      and
      <a class="link" href="myisam-storage-engine.html" title="18.2 The MyISAM Storage Engine">
       <code class="literal">
        MyISAM
       </code>
      </a>
      support full-text parser
          plugins. See
      <a class="ulink" href="/doc/extending-mysql/8.4/en/plugin-types.html#full-text-plugin-type" target="_top">
       Full-Text Parser Plugins
      </a>
      and
      <a class="ulink" href="/doc/extending-mysql/8.4/en/writing-full-text-plugins.html" target="_top">
       Writing Full-Text Parser Plugins
      </a>
      for more
          information.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       SPATIAL
      </code>
     </p>
     <p>
      You can create
      <code class="literal">
       SPATIAL
      </code>
      indexes on spatial
          data types. Spatial types are supported only for
      <code class="literal">
       InnoDB
      </code>
      and
      <code class="literal">
       MyISAM
      </code>
      tables, and indexed columns must be declared as
      <code class="literal">
       NOT
          NULL
      </code>
      . See
      <a class="xref" href="spatial-types.html" title="13.4 Spatial Data Types">
       Section 13.4, “Spatial Data Types”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       FOREIGN KEY
      </code>
     </p>
     <p>
      MySQL supports foreign keys, which let you cross-reference
          related data across tables, and foreign key constraints, which
          help keep this spread-out data consistent. For definition and
          option information, see
      <a class="link" href="create-table.html#create-table-reference-definition">
       <em class="replaceable">
        <code>
         reference_definition
        </code>
       </em>
      </a>
      ,
          and
      <a class="link" href="create-table.html#create-table-reference-option">
       <em class="replaceable">
        <code>
         reference_option
        </code>
       </em>
      </a>
      .
     </p>
     <p>
      Partitioned tables employing the
      <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
       <code class="literal">
        InnoDB
       </code>
      </a>
      storage engine do not
          support foreign keys. 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>
      , for more
          information.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       CHECK
      </code>
     </p>
     <p>
      The
      <code class="literal">
       CHECK
      </code>
      clause enables the creation of
          constraints to be checked for data values in table rows. See
      <a class="xref" href="create-table-check-constraints.html" title="15.1.20.6 CHECK Constraints">
       Section 15.1.20.6, “CHECK Constraints”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       <em class="replaceable">
        <code>
         key_part
        </code>
       </em>
      </code>
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         A
         <em class="replaceable">
          <code>
           key_part
          </code>
         </em>
         specification can
              end with
         <code class="literal">
          ASC
         </code>
         or
         <code class="literal">
          DESC
         </code>
         to specify whether index values are stored in ascending or
              descending order. The default is ascending if no order
              specifier is given.
        </p>
       </li>
       <li class="listitem">
        <p>
         Prefixes, defined by the
         <em class="replaceable">
          <code>
           length
          </code>
         </em>
         attribute, can be up to 767 bytes long for
         <code class="literal">
          InnoDB
         </code>
         tables that use the
         <code class="literal">
          <a class="link" href="glossary.html#glos_redundant_row_format" title="redundant row format">
           REDUNDANT
          </a>
         </code>
         or
         <code class="literal">
          <a class="link" href="glossary.html#glos_compact_row_format" title="compact row format">
           COMPACT
          </a>
         </code>
         row format. The prefix length limit is 3072 bytes for
         <code class="literal">
          InnoDB
         </code>
         tables that use the
         <code class="literal">
          <a class="link" href="glossary.html#glos_dynamic_row_format" title="dynamic row format">
           DYNAMIC
          </a>
         </code>
         or
         <code class="literal">
          <a class="link" href="glossary.html#glos_compressed_row_format" title="compressed row format">
           COMPRESSED
          </a>
         </code>
         row format. For
         <code class="literal">
          MyISAM
         </code>
         tables, the
              prefix length limit is 1000 bytes.
        </p>
        <p>
         Prefix
         <span class="emphasis">
          <em>
           limits
          </em>
         </span>
         are measured in bytes.
              However, prefix
         <span class="emphasis">
          <em>
           lengths
          </em>
         </span>
         for index
              specifications in
         <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
          <code class="literal">
           CREATE
              TABLE
          </code>
         </a>
         ,
         <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
          <code class="literal">
           ALTER
              TABLE
          </code>
         </a>
         , and
         <a class="link" href="create-index.html" title="15.1.15 CREATE INDEX Statement">
          <code class="literal">
           CREATE
              INDEX
          </code>
         </a>
         statements are interpreted as number of
              characters for nonbinary string types
              (
         <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="blob.html" title="13.3.4 The BLOB and TEXT Types">
          <code class="literal">
           TEXT
          </code>
         </a>
         ) and number of bytes
              for binary string types
              (
         <a class="link" href="binary-varbinary.html" title="13.3.3 The BINARY and VARBINARY Types">
          <code class="literal">
           BINARY
          </code>
         </a>
         ,
         <a class="link" href="binary-varbinary.html" title="13.3.3 The BINARY and VARBINARY Types">
          <code class="literal">
           VARBINARY
          </code>
         </a>
         ,
         <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
          <code class="literal">
           BLOB
          </code>
         </a>
         ). Take this into
              account when specifying a prefix length for a nonbinary
              string column that uses a multibyte character set.
        </p>
       </li>
       <li class="listitem">
        <p>
         The
         <em class="replaceable">
          <code>
           expr
          </code>
         </em>
         for a
         <em class="replaceable">
          <code>
           key_part
          </code>
         </em>
         specification can take
              the form
         <code class="literal">
          (CAST
          <em class="replaceable">
           <code>
            json_path
           </code>
          </em>
          AS
          <em class="replaceable">
           <code>
            type
           </code>
          </em>
          ARRAY)
         </code>
         to create
              a multi-valued index on a
         <a class="link" href="json.html" title="13.5 The JSON Data Type">
          <code class="literal">
           JSON
          </code>
         </a>
         column.
         <a class="xref" href="create-index.html#create-index-multi-valued" title="Multi-Valued Indexes">
          Multi-Valued Indexes
         </a>
         , provides
              detailed information regarding creation of, usage of, and
              restrictions and limitations on multi-valued indexes.
        </p>
       </li>
      </ul>
     </div>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       <em class="replaceable">
        <code>
         index_type
        </code>
       </em>
      </code>
     </p>
     <p>
      Some storage engines permit you to specify an index type when
          creating an index. The syntax for the
      <em class="replaceable">
       <code>
        index_type
       </code>
      </em>
      specifier is
      <code class="literal">
       USING
       <em class="replaceable">
        <code>
         type_name
        </code>
       </em>
      </code>
      .
     </p>
     <p>
      Example:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa38036211"><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> lookup
  <span class="token punctuation">(</span>id <span class="token datatype">INT</span><span class="token punctuation">,</span> <span class="token keyword">INDEX</span> <span class="token keyword">USING</span> <span class="token keyword">BTREE</span> <span class="token punctuation">(</span>id<span class="token punctuation">)</span>
<span class="token punctuation">)</span> <span class="token keyword">ENGINE</span> <span class="token operator">=</span> <span class="token keyword">MEMORY</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      The preferred position for
      <code class="literal">
       USING
      </code>
      is after
          the index column list. It can be given before the column list,
          but support for use of the option in that position is
          deprecated and you should expect it to be removed in a future
          MySQL release.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       <em class="replaceable">
        <code>
         index_option
        </code>
       </em>
      </code>
     </p>
     <p>
      <em class="replaceable">
       <code>
        index_option
       </code>
      </em>
      values specify
          additional options for an index.
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         <code class="literal">
          KEY_BLOCK_SIZE
         </code>
        </p>
        <p>
         For
         <a class="link" href="myisam-storage-engine.html" title="18.2 The MyISAM Storage Engine">
          <code class="literal">
           MyISAM
          </code>
         </a>
         tables,
         <code class="literal">
          KEY_BLOCK_SIZE
         </code>
         optionally specifies the
              size in bytes to use for index key blocks. The value is
              treated as a hint; a different size could be used if
              necessary. A
         <code class="literal">
          KEY_BLOCK_SIZE
         </code>
         value
              specified for an individual index definition overrides the
              table-level
         <code class="literal">
          KEY_BLOCK_SIZE
         </code>
         value.
        </p>
        <p>
         For information about the table-level
         <code class="literal">
          KEY_BLOCK_SIZE
         </code>
         attribute, see
         <a class="xref" href="create-table.html#create-table-options" title="Table Options">
          Table Options
         </a>
         .
        </p>
       </li>
       <li class="listitem">
        <p>
         <code class="literal">
          WITH PARSER
         </code>
        </p>
        <p>
         The
         <code class="literal">
          WITH PARSER
         </code>
         option can be used only
              with
         <code class="literal">
          FULLTEXT
         </code>
         indexes. It associates a
              parser plugin with the index if full-text indexing and
              searching operations need special handling.
         <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
          <code class="literal">
           InnoDB
          </code>
         </a>
         and
         <a class="link" href="myisam-storage-engine.html" title="18.2 The MyISAM Storage Engine">
          <code class="literal">
           MyISAM
          </code>
         </a>
         support full-text
              parser plugins. If you have a
         <a class="link" href="myisam-storage-engine.html" title="18.2 The MyISAM Storage Engine">
          <code class="literal">
           MyISAM
          </code>
         </a>
         table with an
              associated full-text parser plugin, you can convert the
              table to
         <code class="literal">
          InnoDB
         </code>
         using
         <code class="literal">
          ALTER
              TABLE
         </code>
         .
        </p>
       </li>
       <li class="listitem">
        <p>
         <code class="literal">
          COMMENT
         </code>
        </p>
        <p>
         Index definitions can include an optional comment of up to
              1024 characters.
        </p>
        <p>
         You can set the
         <code class="literal">
          InnoDB
         </code>
         <code class="literal">
          MERGE_THRESHOLD
         </code>
         value for an individual
              index using the
         <code class="literal">
          <em class="replaceable">
           <code>
            index_option
           </code>
          </em>
         </code>
         <code class="literal">
          COMMENT
         </code>
         clause. See
         <a class="xref" href="index-page-merge-threshold.html" title="17.8.11 Configuring the Merge Threshold for Index Pages">
          Section 17.8.11, “Configuring the Merge Threshold for Index Pages”
         </a>
         .
        </p>
       </li>
       <li class="listitem">
        <p>
         <code class="literal">
          VISIBLE
         </code>
         ,
         <code class="literal">
          INVISIBLE
         </code>
        </p>
        <p>
         Specify index visibility. Indexes are visible by default.
              An invisible index is not used by the optimizer.
              Specification of index visibility applies to indexes other
              than primary keys (either explicit or implicit). For more
              information, see
         <a class="xref" href="invisible-indexes.html" title="10.3.12 Invisible Indexes">
          Section 10.3.12, “Invisible Indexes”
         </a>
         .
        </p>
        <a class="indexterm" name="idm46045186154064">
        </a>
       </li>
       <li class="listitem">
        <p>
         <code class="literal">
          ENGINE_ATTRIBUTE
         </code>
         and
         <code class="literal">
          SECONDARY_ENGINE_ATTRIBUTE
         </code>
         options are
              used to specify index attributes for primary and secondary
              storage engines. The options are reserved for future use.
        </p>
       </li>
      </ul>
     </div>
     <p>
      For more information about permissible
      <em class="replaceable">
       <code>
        index_option
       </code>
      </em>
      values, see
      <a class="xref" href="create-index.html" title="15.1.15 CREATE INDEX Statement">
       Section 15.1.15, “CREATE INDEX Statement”
      </a>
      . For more information about
          indexes, see
      <a class="xref" href="mysql-indexes.html" title="10.3.1 How MySQL Uses Indexes">
       Section 10.3.1, “How MySQL Uses Indexes”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <a name="create-table-reference-definition">
      </a>
      <code class="literal">
       <em class="replaceable">
        <code>
         reference_definition
        </code>
       </em>
      </code>
     </p>
     <p>
      For
      <em class="replaceable">
       <code>
        reference_definition
       </code>
      </em>
      syntax
          details and examples, see
      <a class="xref" href="create-table-foreign-keys.html" title="15.1.20.5 FOREIGN KEY Constraints">
       Section 15.1.20.5, “FOREIGN KEY Constraints”
      </a>
      .
     </p>
     <p>
      <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
       <code class="literal">
        InnoDB
       </code>
      </a>
      and
      <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
       <code class="literal">
        NDB
       </code>
      </a>
      tables support checking of
          foreign key constraints. The columns of the referenced table
          must always be explicitly named. Both
      <code class="literal">
       ON
          DELETE
      </code>
      and
      <code class="literal">
       ON UPDATE
      </code>
      actions on
          foreign keys are supported. For more detailed information and
          examples, see
      <a class="xref" href="create-table-foreign-keys.html" title="15.1.20.5 FOREIGN KEY Constraints">
       Section 15.1.20.5, “FOREIGN KEY Constraints”
      </a>
      .
     </p>
     <p>
      For other storage engines, MySQL Server parses and ignores the
      <code class="literal">
       FOREIGN KEY
      </code>
      syntax in
      <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
       <code class="literal">
        CREATE TABLE
       </code>
      </a>
      statements.
     </p>
     <div class="important" style="margin-left: 0.5in; margin-right: 0.5in;">
      <div class="admon-title">
       Important
      </div>
      <p>
       For users familiar with the ANSI/ISO SQL Standard, please
            note that no storage engine, including
       <code class="literal">
        InnoDB
       </code>
       , recognizes or enforces the
       <code class="literal">
        MATCH
       </code>
       clause used in referential
            integrity constraint definitions. Use of an explicit
       <code class="literal">
        MATCH
       </code>
       clause does not have the specified
            effect, and also causes
       <code class="literal">
        ON DELETE
       </code>
       and
       <code class="literal">
        ON UPDATE
       </code>
       clauses to be ignored. For
            these reasons, specifying
       <code class="literal">
        MATCH
       </code>
       should be
            avoided.
      </p>
      <p>
       The
       <code class="literal">
        MATCH
       </code>
       clause in the SQL standard
            controls how
       <code class="literal">
        NULL
       </code>
       values in a composite
            (multiple-column) foreign key are handled when comparing to
            a primary key.
       <code class="literal">
        InnoDB
       </code>
       essentially
            implements the semantics defined by
       <code class="literal">
        MATCH
            SIMPLE
       </code>
       , which permit a foreign key to be all or
            partially
       <code class="literal">
        NULL
       </code>
       . In that case, the (child
            table) row containing such a foreign key is permitted to be
            inserted, and does not match any row in the referenced
            (parent) table. It is possible to implement other semantics
            using triggers.
      </p>
      <p>
       Additionally, MySQL requires that the referenced columns be
            indexed for performance. However,
       <code class="literal">
        InnoDB
       </code>
       does not enforce any requirement that the referenced columns
            be declared
       <code class="literal">
        UNIQUE
       </code>
       or
       <code class="literal">
        NOT
            NULL
       </code>
       . The handling of foreign key references to
            nonunique keys or keys that contain
       <code class="literal">
        NULL
       </code>
       values is not well defined for operations such as
       <code class="literal">
        UPDATE
       </code>
       or
       <code class="literal">
        DELETE
            CASCADE
       </code>
       . You are advised to use foreign keys that
            reference only keys that are both
       <code class="literal">
        UNIQUE
       </code>
       (or
       <code class="literal">
        PRIMARY
       </code>
       ) and
       <code class="literal">
        NOT
            NULL
       </code>
       .
      </p>
      <p>
       MySQL parses but ignores
       <span class="quote">
        “
        <span class="quote">
         inline
         <code class="literal">
          REFERENCES
         </code>
         specifications
        </span>
        ”
       </span>
       (as
            defined in the SQL standard) where the references are
            defined as part of the column specification. MySQL accepts
       <code class="literal">
        REFERENCES
       </code>
       clauses only when specified as
            part of a separate
       <code class="literal">
        FOREIGN KEY
       </code>
       specification. For more information, see
       <a class="xref" href="ansi-diff-foreign-keys.html" title="1.7.2.3 FOREIGN KEY Constraint Differences">
        Section 1.7.2.3, “FOREIGN KEY Constraint Differences”
       </a>
       .
      </p>
     </div>
    </li>
    <li class="listitem">
     <p>
      <a name="create-table-reference-option">
      </a>
      <code class="literal">
       <em class="replaceable">
        <code>
         reference_option
        </code>
       </em>
      </code>
     </p>
     <p>
      For information about the
      <code class="literal">
       RESTRICT
      </code>
      ,
      <code class="literal">
       CASCADE
      </code>
      ,
      <code class="literal">
       SET NULL
      </code>
      ,
      <code class="literal">
       NO ACTION
      </code>
      , and
      <code class="literal">
       SET
          DEFAULT
      </code>
      options, see
      <a class="xref" href="create-table-foreign-keys.html" title="15.1.20.5 FOREIGN KEY Constraints">
       Section 15.1.20.5, “FOREIGN KEY Constraints”
      </a>
      .
     </p>
    </li>
   </ul>
  </div>
  <h4>
   <a name="create-table-options">
   </a>
   Table Options
  </h4>
  <p>
   Table options are used to optimize the behavior of the table. In
      most cases, you do not have to specify any of them. These options
      apply to all storage engines unless otherwise indicated. Options
      that do not apply to a given storage engine may be accepted and
      remembered as part of the table definition. Such options then
      apply if you later use
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
    </code>
   </a>
   to convert the table to use a different storage engine.
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       ENGINE
      </code>
     </p>
     <p>
      Specifies the storage engine for the table, using one of the
          names shown in the following table. The engine name can be
          unquoted or quoted. The quoted name
      <code class="literal">
       'DEFAULT'
      </code>
      is recognized but ignored.
     </p>
     <div class="informaltable">
      <table summary="Storage engine names permitted for the ENGINE table option and a description of each engine.">
       <colgroup>
        <col style="width: 25%"/>
        <col style="width: 70%"/>
       </colgroup>
       <thead>
        <tr>
         <th>
          Storage Engine
         </th>
         <th>
          Description
         </th>
        </tr>
       </thead>
       <tbody>
        <tr>
         <td>
          <code class="literal">
           InnoDB
          </code>
         </td>
         <td>
          Transaction-safe tables with row locking and foreign keys. The default
                storage engine for new tables. See
          <a class="xref" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
           Chapter 17,
           <i>
            The InnoDB Storage Engine
           </i>
          </a>
          , and in
                particular
          <a class="xref" href="innodb-introduction.html" title="17.1 Introduction to InnoDB">
           Section 17.1, “Introduction to InnoDB”
          </a>
          if you
                have MySQL experience but are new to
          <code class="literal">
           InnoDB
          </code>
          .
         </td>
        </tr>
        <tr>
         <td>
          <code class="literal">
           MyISAM
          </code>
         </td>
         <td>
          The binary portable storage engine that is primarily used for read-only
                or read-mostly workloads. See
          <a class="xref" href="myisam-storage-engine.html" title="18.2 The MyISAM Storage Engine">
           Section 18.2, “The MyISAM Storage Engine”
          </a>
          .
         </td>
        </tr>
        <tr>
         <td>
          <code class="literal">
           MEMORY
          </code>
         </td>
         <td>
          The data for this storage engine is stored only in memory. See
          <a class="xref" href="memory-storage-engine.html" title="18.3 The MEMORY Storage Engine">
           Section 18.3, “The MEMORY Storage Engine”
          </a>
          .
         </td>
        </tr>
        <tr>
         <td>
          <code class="literal">
           CSV
          </code>
         </td>
         <td>
          Tables that store rows in comma-separated values format. See
          <a class="xref" href="csv-storage-engine.html" title="18.4 The CSV Storage Engine">
           Section 18.4, “The CSV Storage Engine”
          </a>
          .
         </td>
        </tr>
        <tr>
         <td>
          <code class="literal">
           ARCHIVE
          </code>
         </td>
         <td>
          The archiving storage engine. See
          <a class="xref" href="archive-storage-engine.html" title="18.5 The ARCHIVE Storage Engine">
           Section 18.5, “The ARCHIVE Storage Engine”
          </a>
          .
         </td>
        </tr>
        <tr>
         <td>
          <code class="literal">
           EXAMPLE
          </code>
         </td>
         <td>
          An example engine. See
          <a class="xref" href="example-storage-engine.html" title="18.9 The EXAMPLE Storage Engine">
           Section 18.9, “The EXAMPLE Storage Engine”
          </a>
          .
         </td>
        </tr>
        <tr>
         <td>
          <code class="literal">
           FEDERATED
          </code>
         </td>
         <td>
          Storage engine that accesses remote tables. See
          <a class="xref" href="federated-storage-engine.html" title="18.8 The FEDERATED Storage Engine">
           Section 18.8, “The FEDERATED Storage Engine”
          </a>
          .
         </td>
        </tr>
        <tr>
         <td>
          <code class="literal">
           HEAP
          </code>
         </td>
         <td>
          This is a synonym for
          <code class="literal">
           MEMORY
          </code>
          .
         </td>
        </tr>
        <tr>
         <td>
          <code class="literal">
           MERGE
          </code>
         </td>
         <td>
          A collection of
          <code class="literal">
           MyISAM
          </code>
          tables used as one table. Also
                known as
          <code class="literal">
           MRG_MyISAM
          </code>
          . See
          <a class="xref" href="merge-storage-engine.html" title="18.7 The MERGE Storage Engine">
           Section 18.7, “The MERGE Storage Engine”
          </a>
          .
         </td>
        </tr>
        <tr>
         <td>
          <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
           <code class="literal">
            NDB
           </code>
          </a>
         </td>
         <td>
          Clustered, fault-tolerant, memory-based tables, supporting transactions
                and foreign keys. Also known as
          <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
           <code class="literal">
            NDBCLUSTER
           </code>
          </a>
          . 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>
          .
         </td>
        </tr>
       </tbody>
      </table>
     </div>
     <div class="informaltable">
      <table cellpadding="0" cellspacing="0" style="position: fixed; top: 0px; display: none; left: 431px; width: 709px;">
       <thead>
        <tr>
         <th style="width: 186.312px;">
          Storage Engine
         </th>
         <th style="width: 521.688px;">
          Description
         </th>
        </tr>
       </thead>
      </table>
     </div>
     <p>
      By default, if a storage engine is specified that is not
          available, the statement fails with an error. You can override
          this behavior by removing
      <a class="link" href="sql-mode.html#sqlmode_no_engine_substitution">
       <code class="literal">
        NO_ENGINE_SUBSTITUTION
       </code>
      </a>
      from
          the server SQL mode (see
      <a class="xref" href="sql-mode.html" title="7.1.11 Server SQL Modes">
       Section 7.1.11, “Server SQL Modes”
      </a>
      ) so that
          MySQL allows substitution of the specified engine with the
          default storage engine instead. Normally in such cases, this
          is
      <code class="literal">
       InnoDB
      </code>
      , which is the default value for
          the
      <a class="link" href="server-system-variables.html#sysvar_default_storage_engine">
       <code class="literal">
        default_storage_engine
       </code>
      </a>
      system variable. When
      <code class="literal">
       NO_ENGINE_SUBSTITUTION
      </code>
      is disabled, a
          warning occurs if the storage engine specification is not
          honored.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       AUTOEXTEND_SIZE
      </code>
     </p>
     <p>
      Defines the amount by which
      <code class="literal">
       InnoDB
      </code>
      extends
          the size of the tablespace when it becomes full. The setting
          must be a multiple of 4MB. The default setting is 0, which
          causes the tablespace to be extended according to the implicit
          default behavior. For more information, see
      <a class="xref" href="innodb-tablespace-autoextend-size.html" title="17.6.3.9 Tablespace AUTOEXTEND_SIZE Configuration">
       Section 17.6.3.9, “Tablespace AUTOEXTEND_SIZE Configuration”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       AUTO_INCREMENT
      </code>
     </p>
     <p>
      The initial
      <code class="literal">
       AUTO_INCREMENT
      </code>
      value for the
          table. In MySQL 8.4, this works for
      <code class="literal">
       MyISAM
      </code>
      ,
      <code class="literal">
       MEMORY
      </code>
      ,
      <code class="literal">
       InnoDB
      </code>
      , and
      <code class="literal">
       ARCHIVE
      </code>
      tables. To set the first auto-increment value for engines that
          do not support the
      <code class="literal">
       AUTO_INCREMENT
      </code>
      table
          option, insert a
      <span class="quote">
       “
       <span class="quote">
        dummy
       </span>
       ”
      </span>
      row with a value one
          less than the desired value after creating the table, and then
          delete the dummy row.
     </p>
     <p>
      For engines that support the
      <code class="literal">
       AUTO_INCREMENT
      </code>
      table option in
      <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
       <code class="literal">
        CREATE TABLE
       </code>
      </a>
      statements, you can also use
      <code class="literal">
       ALTER TABLE
       <em class="replaceable">
        <code>
         tbl_name
        </code>
       </em>
       AUTO_INCREMENT =
       <em class="replaceable">
        <code>
         N
        </code>
       </em>
      </code>
      to reset the
      <code class="literal">
       AUTO_INCREMENT
      </code>
      value. The value cannot be
          set lower than the maximum value currently in the column.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       AVG_ROW_LENGTH
      </code>
     </p>
     <p>
      An approximation of the average row length for your table. You
          need to set this only for large tables with variable-size
          rows.
     </p>
     <p>
      When you create a
      <code class="literal">
       MyISAM
      </code>
      table, MySQL uses
          the product of the
      <code class="literal">
       MAX_ROWS
      </code>
      and
      <code class="literal">
       AVG_ROW_LENGTH
      </code>
      options to decide how big
          the resulting table is. If you don't specify either option,
          the maximum size for
      <code class="literal">
       MyISAM
      </code>
      data and index
          files is 256TB by default. (If your operating system does not
          support files that large, table sizes are constrained by the
          file size limit.) If you want to keep down the pointer sizes
          to make the index smaller and faster and you don't really need
          big files, you can decrease the default pointer size by
          setting the
      <a class="link" href="server-system-variables.html#sysvar_myisam_data_pointer_size">
       <code class="literal">
        myisam_data_pointer_size
       </code>
      </a>
      system variable. (See
      <a class="xref" href="server-system-variables.html" title="7.1.8 Server System Variables">
       Section 7.1.8, “Server System Variables”
      </a>
      .) If you want all
          your tables to be able to grow above the default limit and are
          willing to have your tables slightly slower and larger than
          necessary, you can increase the default pointer size by
          setting this variable. Setting the value to 7 permits table
          sizes up to 65,536TB.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       [DEFAULT] CHARACTER SET
      </code>
     </p>
     <p>
      Specifies a default character set for the table.
      <code class="literal">
       CHARSET
      </code>
      is a synonym for
      <code class="literal">
       CHARACTER
          SET
      </code>
      . If the character set name is
      <code class="literal">
       DEFAULT
      </code>
      , the database character set is
          used.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       CHECKSUM
      </code>
     </p>
     <p>
      Set this to 1 if you want MySQL to maintain a live checksum
          for all rows (that is, a checksum that MySQL updates
          automatically as the table changes). This makes the table a
          little slower to update, but also makes it easier to find
          corrupted tables. The
      <a class="link" href="checksum-table.html" title="15.7.3.3 CHECKSUM TABLE Statement">
       <code class="literal">
        CHECKSUM
          TABLE
       </code>
      </a>
      statement reports the checksum.
          (
      <code class="literal">
       MyISAM
      </code>
      only.)
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       [DEFAULT] COLLATE
      </code>
     </p>
     <p>
      Specifies a default collation for the table.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       COMMENT
      </code>
     </p>
     <p>
      A comment for the table, up to 2048 characters long.
     </p>
     <p>
      You can set the
      <code class="literal">
       InnoDB
      </code>
      <code class="literal">
       MERGE_THRESHOLD
      </code>
      value for a table using the
      <code class="literal">
       <em class="replaceable">
        <code>
         table_option
        </code>
       </em>
      </code>
      <code class="literal">
       COMMENT
      </code>
      clause. See
      <a class="xref" href="index-page-merge-threshold.html" title="17.8.11 Configuring the Merge Threshold for Index Pages">
       Section 17.8.11, “Configuring the Merge Threshold for Index Pages”
      </a>
      .
     </p>
     <p>
      <a name="create-table-comment-ndb-table-options">
      </a>
      <b>
       Setting NDB_TABLE options.
      </b>
      <a class="indexterm" name="idm46045186012144">
      </a>
      The table comment in a
      <code class="literal">
       CREATE TABLE
      </code>
      that
            creates an
      <code class="literal">
       NDB
      </code>
      table or an
      <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
       <code class="literal">
        ALTER TABLE
       </code>
      </a>
      statement which
            alters one can also be used to specify one to four of the
      <code class="literal">
       NDB_TABLE
      </code>
      options
      <code class="literal">
       NOLOGGING
      </code>
      ,
      <code class="literal">
       READ_BACKUP
      </code>
      ,
      <code class="literal">
       PARTITION_BALANCE
      </code>
      , or
      <code class="literal">
       FULLY_REPLICATED
      </code>
      as a set of name-value
            pairs, separated by commas if need be, immediately following
            the string
      <code class="literal">
       NDB_TABLE=
      </code>
      that begins the
            quoted comment text. An example statement using this syntax
            is shown here (emphasized text):
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa76274175"><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> t1 <span class="token punctuation">(</span>
    c1 <span class="token datatype">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">AUTO_INCREMENT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">,</span>
    c2 <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">100</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    c3 <span class="token datatype">VARCHAR</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">ENGINE</span><span class="token operator">=</span><span class="token keyword">NDB</span>
<span class="token keyword"></span><em><span class="token keyword">COMMENT</span><span class="token operator">=</span><span class="token string">"NDB_TABLE=READ_BACKUP=0,PARTITION_BALANCE=FOR_RP_BY_NODE"</span></em><span class="token string"></span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      Spaces are not permitted within the quoted string. The string
          is case-insensitive.
     </p>
     <p>
      The comment is displayed as part of 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>
      . The text of
          the comment is also available as the TABLE_COMMENT column of
          the MySQL Information Schema
      <a class="link" href="information-schema-tables-table.html" title="28.3.38 The INFORMATION_SCHEMA TABLES Table">
       <code class="literal">
        TABLES
       </code>
      </a>
      table.
     </p>
     <p>
      This comment syntax is also supported with
      <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
       <code class="literal">
        ALTER TABLE
       </code>
      </a>
      statements for
      <code class="literal">
       NDB
      </code>
      tables. Keep in mind that a table
          comment used with
      <code class="literal">
       ALTER TABLE
      </code>
      replaces any
          existing comment which the table might have had previously.
     </p>
     <p>
      Setting the
      <code class="literal">
       MERGE_THRESHOLD
      </code>
      option in table
          comments is not supported for
      <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
       <code class="literal">
        NDB
       </code>
      </a>
      tables (it is ignored).
     </p>
     <p>
      For complete syntax information and examples, see
      <a class="xref" href="create-table-ndb-comment-options.html" title="15.1.20.12 Setting NDB Comment Options">
       Section 15.1.20.12, “Setting NDB Comment Options”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       COMPRESSION
      </code>
     </p>
     <p>
      The compression algorithm used for page level compression for
      <code class="literal">
       InnoDB
      </code>
      tables. Supported values include
      <code class="literal">
       Zlib
      </code>
      ,
      <code class="literal">
       LZ4
      </code>
      , and
      <code class="literal">
       None
      </code>
      . The
      <code class="literal">
       COMPRESSION
      </code>
      attribute was introduced with the transparent page compression
          feature. Page compression is only supported with
      <code class="literal">
       InnoDB
      </code>
      tables that reside in
      <a class="link" href="glossary.html#glos_file_per_table" title="file-per-table">
       file-per-table
      </a>
      tablespaces, and is only available on Linux and Windows
          platforms that support sparse files and hole punching. For
          more information, see
      <a class="xref" href="innodb-page-compression.html" title="17.9.2 InnoDB Page Compression">
       Section 17.9.2, “InnoDB Page Compression”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       CONNECTION
      </code>
     </p>
     <p>
      The connection string for a
      <code class="literal">
       FEDERATED
      </code>
      table.
     </p>
     <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
      <div class="admon-title">
       Note
      </div>
      <p>
       Older versions of MySQL used a
       <code class="literal">
        COMMENT
       </code>
       option for the connection string.
      </p>
     </div>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       DATA DIRECTORY
      </code>
      ,
      <code class="literal">
       INDEX
          DIRECTORY
      </code>
     </p>
     <p>
      For
      <code class="literal">
       InnoDB
      </code>
      , the
      <code class="literal">
       DATA
          DIRECTORY='
       <em class="replaceable">
        <code>
         directory
        </code>
       </em>
       '
      </code>
      clause permits creating tables outside of the data directory.
          The
      <a class="link" href="innodb-parameters.html#sysvar_innodb_file_per_table">
       <code class="literal">
        innodb_file_per_table
       </code>
      </a>
      variable must be enabled to use the
      <code class="literal">
       DATA
          DIRECTORY
      </code>
      clause. The full directory path must be
          specified, and known to
      <code class="literal">
       InnoDB
      </code>
      . For more
          information, see
      <a class="xref" href="innodb-create-table-external.html" title="17.6.1.2 Creating Tables Externally">
       Section 17.6.1.2, “Creating Tables Externally”
      </a>
      .
     </p>
     <p>
      When creating
      <code class="literal">
       MyISAM
      </code>
      tables, you can use
          the
      <code class="literal">
       DATA
          DIRECTORY='
       <em class="replaceable">
        <code>
         directory
        </code>
       </em>
       '
      </code>
      clause, the
      <code class="literal">
       INDEX
          DIRECTORY='
       <em class="replaceable">
        <code>
         directory
        </code>
       </em>
       '
      </code>
      clause, or both. They specify where to put a
      <code class="literal">
       MyISAM
      </code>
      table's data file and index file,
          respectively. Unlike
      <code class="literal">
       InnoDB
      </code>
      tables, MySQL
          does not create subdirectories that correspond to the database
          name when creating a
      <code class="literal">
       MyISAM
      </code>
      table with a
      <code class="literal">
       DATA DIRECTORY
      </code>
      or
      <code class="literal">
       INDEX
          DIRECTORY
      </code>
      option. Files are created in the directory
          that is specified.
     </p>
     <p>
      You must have the
      <a class="link" href="privileges-provided.html#priv_file">
       <code class="literal">
        FILE
       </code>
      </a>
      privilege to use the
      <code class="literal">
       DATA DIRECTORY
      </code>
      or
      <code class="literal">
       INDEX DIRECTORY
      </code>
      table option.
     </p>
     <div class="important" style="margin-left: 0.5in; margin-right: 0.5in;">
      <div class="admon-title">
       Important
      </div>
      <p>
       Table-level
       <code class="literal">
        DATA DIRECTORY
       </code>
       and
       <code class="literal">
        INDEX DIRECTORY
       </code>
       options are ignored for
            partitioned tables. (Bug #32091)
      </p>
     </div>
     <p>
      These options work only when you are not using the
      <a class="link" href="server-options.html#option_mysqld_symbolic-links">
       <code class="option">
        --skip-symbolic-links
       </code>
      </a>
      option. Your operating system must also have a working,
          thread-safe
      <code class="literal">
       realpath()
      </code>
      call. See
      <a class="xref" href="symbolic-links-to-tables.html" title="10.12.2.2 Using Symbolic Links for MyISAM Tables on Unix">
       Section 10.12.2.2, “Using Symbolic Links for MyISAM Tables on Unix”
      </a>
      , for more complete
          information.
     </p>
     <p>
      If a
      <code class="literal">
       MyISAM
      </code>
      table is created with no
      <code class="literal">
       DATA DIRECTORY
      </code>
      option, the
      <code class="filename">
       .MYD
      </code>
      file is created in the database
          directory. By default, if
      <code class="literal">
       MyISAM
      </code>
      finds an
          existing
      <code class="filename">
       .MYD
      </code>
      file in this case, it
          overwrites it. The same applies to
      <code class="filename">
       .MYI
      </code>
      files for tables created with no
      <code class="literal">
       INDEX
          DIRECTORY
      </code>
      option. To suppress this behavior, start
          the server with the
      <a class="link" href="server-system-variables.html#sysvar_keep_files_on_create">
       <code class="option">
        --keep_files_on_create
       </code>
      </a>
      option,
          in which case
      <code class="literal">
       MyISAM
      </code>
      does not overwrite
          existing files and returns an error instead.
     </p>
     <p>
      If a
      <code class="literal">
       MyISAM
      </code>
      table is created with a
      <code class="literal">
       DATA DIRECTORY
      </code>
      or
      <code class="literal">
       INDEX
          DIRECTORY
      </code>
      option and an existing
      <code class="filename">
       .MYD
      </code>
      or
      <code class="filename">
       .MYI
      </code>
      file is
          found,
      <code class="literal">
       MyISAM
      </code>
      always returns an error, and
          does not overwrite a file in the specified directory.
     </p>
     <div class="important" style="margin-left: 0.5in; margin-right: 0.5in;">
      <div class="admon-title">
       Important
      </div>
      <p>
       You cannot use path names that contain the MySQL data
            directory with
       <code class="literal">
        DATA DIRECTORY
       </code>
       or
       <code class="literal">
        INDEX DIRECTORY
       </code>
       . This includes
            partitioned tables and individual table partitions. (See Bug
            #32167.)
      </p>
     </div>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       DELAY_KEY_WRITE
      </code>
     </p>
     <p>
      Set this to 1 if you want to delay key updates for the table
          until the table is closed. See the description of the
      <a class="link" href="server-system-variables.html#sysvar_delay_key_write">
       <code class="literal">
        delay_key_write
       </code>
      </a>
      system
          variable in
      <a class="xref" href="server-system-variables.html" title="7.1.8 Server System Variables">
       Section 7.1.8, “Server System Variables”
      </a>
      .
          (
      <code class="literal">
       MyISAM
      </code>
      only.)
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       ENCRYPTION
      </code>
     </p>
     <p>
      The
      <code class="literal">
       ENCRYPTION
      </code>
      clause enables or disables
          page-level data encryption for an
      <code class="literal">
       InnoDB
      </code>
      table. A keyring plugin must be installed and configured
          before encryption can be enabled. The
      <code class="literal">
       ENCRYPTION
      </code>
      clause can be specified when
          creating a table in an a file-per-table tablespace, or when
          creating a table in a general tablespace.
     </p>
     <p>
      The
      <code class="literal">
       ENCRYPTION
      </code>
      option is supported only by
          the
      <code class="literal">
       InnoDB
      </code>
      storage engine; thus it works
          only if the default storage engine is
      <code class="literal">
       InnoDB
      </code>
      , or if the
      <code class="literal">
       CREATE
          TABLE
      </code>
      statement also specifies
      <code class="literal">
       ENGINE=InnoDB
      </code>
      . Otherwise the statement is
          rejected with
      <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_check_not_implemented" target="_top">
       <code class="literal">
        ER_CHECK_NOT_IMPLEMENTED
       </code>
      </a>
      .
     </p>
     <p>
      A table inherits the default schema encryption if an
      <code class="literal">
       ENCRYPTION
      </code>
      clause is not specified. If the
      <a class="link" href="server-system-variables.html#sysvar_table_encryption_privilege_check">
       <code class="literal">
        table_encryption_privilege_check
       </code>
      </a>
      variable is enabled, the
      <a class="link" href="privileges-provided.html#priv_table-encryption-admin">
       <code class="literal">
        TABLE_ENCRYPTION_ADMIN
       </code>
      </a>
      privilege is required to create a table with an
      <code class="literal">
       ENCRYPTION
      </code>
      clause setting that differs from
          the default schema encryption. When creating a table in a
          general tablespace, table and tablespace encryption must
          match.
     </p>
     <p>
      Specifying an
      <code class="literal">
       ENCRYPTION
      </code>
      clause with a
          value other than
      <code class="literal">
       'N'
      </code>
      or
      <code class="literal">
       ''
      </code>
      is not permitted when using a storage
          engine that does not support encryption.
     </p>
     <p>
      For more information, see
      <a class="xref" href="innodb-data-encryption.html" title="17.13 InnoDB Data-at-Rest Encryption">
       Section 17.13, “InnoDB Data-at-Rest Encryption”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      The
      <code class="literal">
       ENGINE_ATTRIBUTE
      </code>
      and
      <code class="literal">
       SECONDARY_ENGINE_ATTRIBUTE
      </code>
      options are used
          to specify table attributes for primary and secondary storage
          engines. The options are reserved for future use.
     </p>
     <p>
      The value assigned to either of these options must be a string
          literal containing a valid JSON document or an empty string
          (''). Invalid JSON is rejected.
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa60629812"><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> t1 <span class="token punctuation">(</span>c1 <span class="token datatype">INT</span><span class="token punctuation">)</span> <span class="token keyword">ENGINE_ATTRIBUTE</span><span class="token operator">=</span><span class="token string">'{"<em class="replaceable">key</em>":"<em class="replaceable">value</em>"}'</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      <code class="literal">
       ENGINE_ATTRIBUTE
      </code>
      and
      <code class="literal">
       SECONDARY_ENGINE_ATTRIBUTE
      </code>
      values can be
          repeated without error. In this case, the last specified value
          is used.
     </p>
     <p>
      <code class="literal">
       ENGINE_ATTRIBUTE
      </code>
      and
      <code class="literal">
       SECONDARY_ENGINE_ATTRIBUTE
      </code>
      values are not
          checked by the server, nor are they cleared when the
          table's storage engine is changed.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       INSERT_METHOD
      </code>
     </p>
     <p>
      If you want to insert data into a
      <code class="literal">
       MERGE
      </code>
      table, you must specify with
      <code class="literal">
       INSERT_METHOD
      </code>
      the table into which the row should be inserted.
      <code class="literal">
       INSERT_METHOD
      </code>
      is an option useful for
      <code class="literal">
       MERGE
      </code>
      tables only. Use a value of
      <code class="literal">
       FIRST
      </code>
      or
      <code class="literal">
       LAST
      </code>
      to have
          inserts go to the first or last table, or a value of
      <code class="literal">
       NO
      </code>
      to prevent inserts. See
      <a class="xref" href="merge-storage-engine.html" title="18.7 The MERGE Storage Engine">
       Section 18.7, “The MERGE Storage Engine”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       KEY_BLOCK_SIZE
      </code>
     </p>
     <p>
      For
      <a class="link" href="myisam-storage-engine.html" title="18.2 The MyISAM Storage Engine">
       <code class="literal">
        MyISAM
       </code>
      </a>
      tables,
      <code class="literal">
       KEY_BLOCK_SIZE
      </code>
      optionally specifies the
          size in bytes to use for index key blocks. The value is
          treated as a hint; a different size could be used if
          necessary. A
      <code class="literal">
       KEY_BLOCK_SIZE
      </code>
      value specified
          for an individual index definition overrides the table-level
      <code class="literal">
       KEY_BLOCK_SIZE
      </code>
      value.
     </p>
     <p>
      For
      <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
       <code class="literal">
        InnoDB
       </code>
      </a>
      tables,
      <code class="literal">
       KEY_BLOCK_SIZE
      </code>
      specifies the
      <a class="link" href="glossary.html#glos_page" title="page">
       page
      </a>
      size in kilobytes to use
          for
      <a class="link" href="glossary.html#glos_compression" title="compression">
       compressed
      </a>
      <code class="literal">
       InnoDB
      </code>
      tables. The
      <code class="literal">
       KEY_BLOCK_SIZE
      </code>
      value is treated as a hint;
          a different size could be used by
      <code class="literal">
       InnoDB
      </code>
      if
          necessary.
      <code class="literal">
       KEY_BLOCK_SIZE
      </code>
      can only be less
          than or equal to the
      <a class="link" href="innodb-parameters.html#sysvar_innodb_page_size">
       <code class="literal">
        innodb_page_size
       </code>
      </a>
      value. A
          value of 0 represents the default compressed page size, which
          is half of the
      <a class="link" href="innodb-parameters.html#sysvar_innodb_page_size">
       <code class="literal">
        innodb_page_size
       </code>
      </a>
      value.
          Depending on
      <a class="link" href="innodb-parameters.html#sysvar_innodb_page_size">
       <code class="literal">
        innodb_page_size
       </code>
      </a>
      , possible
      <code class="literal">
       KEY_BLOCK_SIZE
      </code>
      values include 0, 1, 2, 4,
          8, and 16. See
      <a class="xref" href="innodb-table-compression.html" title="17.9.1 InnoDB Table Compression">
       Section 17.9.1, “InnoDB Table Compression”
      </a>
      for
          more information.
     </p>
     <p>
      Oracle recommends enabling
      <a class="link" href="innodb-parameters.html#sysvar_innodb_strict_mode">
       <code class="literal">
        innodb_strict_mode
       </code>
      </a>
      when
          specifying
      <code class="literal">
       KEY_BLOCK_SIZE
      </code>
      for
      <code class="literal">
       InnoDB
      </code>
      tables. When
      <a class="link" href="innodb-parameters.html#sysvar_innodb_strict_mode">
       <code class="literal">
        innodb_strict_mode
       </code>
      </a>
      is
          enabled, specifying an invalid
      <code class="literal">
       KEY_BLOCK_SIZE
      </code>
      value returns an error. If
      <a class="link" href="innodb-parameters.html#sysvar_innodb_strict_mode">
       <code class="literal">
        innodb_strict_mode
       </code>
      </a>
      is
          disabled, an invalid
      <code class="literal">
       KEY_BLOCK_SIZE
      </code>
      value
          results in a warning, and the
      <code class="literal">
       KEY_BLOCK_SIZE
      </code>
      option is ignored.
     </p>
     <p>
      The
      <code class="literal">
       Create_options
      </code>
      column in response to
      <a class="link" href="show-table-status.html" title="15.7.7.38 SHOW TABLE STATUS Statement">
       <code class="literal">
        SHOW TABLE STATUS
       </code>
      </a>
      reports the
          actual
      <code class="literal">
       KEY_BLOCK_SIZE
      </code>
      used by the table, as
          does
      <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>
      .
     </p>
     <p>
      <code class="literal">
       InnoDB
      </code>
      only supports
      <code class="literal">
       KEY_BLOCK_SIZE
      </code>
      at the table level.
     </p>
     <p>
      <code class="literal">
       KEY_BLOCK_SIZE
      </code>
      is not supported with 32KB
          and 64KB
      <a class="link" href="innodb-parameters.html#sysvar_innodb_page_size">
       <code class="literal">
        innodb_page_size
       </code>
      </a>
      values.
      <code class="literal">
       InnoDB
      </code>
      table compression does not
          support these pages sizes.
     </p>
     <p>
      <code class="literal">
       InnoDB
      </code>
      does not support the
      <code class="literal">
       KEY_BLOCK_SIZE
      </code>
      option when creating
          temporary tables.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       MAX_ROWS
      </code>
     </p>
     <p>
      The maximum number of rows you plan to store in the table.
          This is not a hard limit, but rather a hint to the storage
          engine that the table must be able to store at least this many
          rows.
     </p>
     <a class="indexterm" name="idm46045185861600">
     </a>
     <a class="indexterm" name="idm46045185860112">
     </a>
     <a class="indexterm" name="idm46045185858624">
     </a>
     <a class="indexterm" name="idm46045185857136">
     </a>
     <div class="important" style="margin-left: 0.5in; margin-right: 0.5in;">
      <div class="admon-title">
       Important
      </div>
      <p>
       The use of
       <code class="literal">
        MAX_ROWS
       </code>
       with
       <code class="literal">
        NDB
       </code>
       tables to control the number of table
            partitions is deprecated. It remains supported in later
            versions for backward compatibility, but is subject to
            removal in a future release. Use PARTITION_BALANCE instead;
            see
       <a class="xref" href="create-table.html#create-table-comment-ndb-table-options" title="Setting NDB_TABLE options">
        Setting NDB_TABLE options
       </a>
       .
      </p>
     </div>
     <p>
      The
      <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
       <code class="literal">
        NDB
       </code>
      </a>
      storage engine treats
          this value as a maximum. If you plan to create very large NDB
          Cluster tables (containing millions of rows), you should use
          this option to insure that
      <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
       <code class="literal">
        NDB
       </code>
      </a>
      allocates sufficient number of index slots in the hash table
          used for storing hashes of the table's primary keys by
          setting
      <code class="literal">
       MAX_ROWS = 2 *
       <em class="replaceable">
        <code>
         rows
        </code>
       </em>
      </code>
      , where
      <em class="replaceable">
       <code>
        rows
       </code>
      </em>
      is the number of rows that you
          expect to insert into the table.
     </p>
     <p>
      The maximum
      <code class="literal">
       MAX_ROWS
      </code>
      value is 4294967295;
          larger values are truncated to this limit.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       MIN_ROWS
      </code>
     </p>
     <p>
      The minimum number of rows you plan to store in the table. The
      <a class="link" href="memory-storage-engine.html" title="18.3 The MEMORY Storage Engine">
       <code class="literal">
        MEMORY
       </code>
      </a>
      storage engine uses this
          option as a hint about memory use.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       PACK_KEYS
      </code>
     </p>
     <p>
      Takes effect only with
      <code class="literal">
       MyISAM
      </code>
      tables. Set
          this option to 1 if you want to have smaller indexes. This
          usually makes updates slower and reads faster. Setting the
          option to 0 disables all packing of keys. Setting it to
      <code class="literal">
       DEFAULT
      </code>
      tells the storage engine to pack
          only long
      <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>
      , or
      <a class="link" href="binary-varbinary.html" title="13.3.3 The BINARY and VARBINARY Types">
       <code class="literal">
        VARBINARY
       </code>
      </a>
      columns.
     </p>
     <p>
      If you do not use
      <code class="literal">
       PACK_KEYS
      </code>
      , the default is
          to pack strings, but not numbers. If you use
      <code class="literal">
       PACK_KEYS=1
      </code>
      , numbers are packed as well.
     </p>
     <p>
      When packing binary number keys, MySQL uses prefix
          compression:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         Every key needs one extra byte to indicate how many bytes
              of the previous key are the same for the next key.
        </p>
       </li>
       <li class="listitem">
        <p>
         The pointer to the row is stored in high-byte-first order
              directly after the key, to improve compression.
        </p>
       </li>
      </ul>
     </div>
     <p>
      This means that if you have many equal keys on two consecutive
          rows, all following
      <span class="quote">
       “
       <span class="quote">
        same
       </span>
       ”
      </span>
      keys usually only take
          two bytes (including the pointer to the row). Compare this to
          the ordinary case where the following keys takes
      <code class="literal">
       storage_size_for_key + pointer_size
      </code>
      (where
          the pointer size is usually 4). Conversely, you get a
          significant benefit from prefix compression only if you have
          many numbers that are the same. If all keys are totally
          different, you use one byte more per key, if the key is not a
          key that can have
      <code class="literal">
       NULL
      </code>
      values. (In this
          case, the packed key length is stored in the same byte that is
          used to mark if a key is
      <code class="literal">
       NULL
      </code>
      .)
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       PASSWORD
      </code>
     </p>
     <p>
      This option is unused.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       ROW_FORMAT
      </code>
     </p>
     <p>
      Defines the physical format in which the rows are stored.
     </p>
     <p>
      When creating a table with
      <a class="link" href="glossary.html#glos_strict_mode" title="strict mode">
       strict mode
      </a>
      disabled,
          the storage engine's default row format is used if the
          specified row format is not supported. The actual row format
          of the table is reported in the
      <code class="literal">
       Row_format
      </code>
      column in response to
      <a class="link" href="show-table-status.html" title="15.7.7.38 SHOW TABLE STATUS Statement">
       <code class="literal">
        SHOW TABLE
          STATUS
       </code>
      </a>
      . The
      <code class="literal">
       Create_options
      </code>
      column
          shows the row format that was specified in the
      <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
       <code class="literal">
        CREATE TABLE
       </code>
      </a>
      statement, as does
      <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>
      .
     </p>
     <p>
      Row format choices differ depending on the storage engine used
          for the table.
     </p>
     <p>
      For
      <code class="literal">
       InnoDB
      </code>
      tables:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         The default row format is defined by
         <a class="link" href="innodb-parameters.html#sysvar_innodb_default_row_format">
          <code class="literal">
           innodb_default_row_format
          </code>
         </a>
         ,
              which has a default setting of
         <code class="literal">
          DYNAMIC
         </code>
         .
              The default row format is used when the
         <code class="literal">
          ROW_FORMAT
         </code>
         option is not defined or
              when
         <code class="literal">
          ROW_FORMAT=DEFAULT
         </code>
         is used.
        </p>
        <p>
         If the
         <code class="literal">
          ROW_FORMAT
         </code>
         option is not
              defined, or if
         <code class="literal">
          ROW_FORMAT=DEFAULT
         </code>
         is
              used, operations that rebuild a table also silently change
              the row format of the table to the default defined by
         <a class="link" href="innodb-parameters.html#sysvar_innodb_default_row_format">
          <code class="literal">
           innodb_default_row_format
          </code>
         </a>
         .
              For more information, see
         <a class="xref" href="innodb-row-format.html#innodb-row-format-defining" title="Defining the Row Format of a Table">
          Defining the Row Format of a Table
         </a>
         .
        </p>
       </li>
       <li class="listitem">
        <p>
         For more efficient
         <code class="literal">
          InnoDB
         </code>
         storage of
              data types, especially
         <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
          <code class="literal">
           BLOB
          </code>
         </a>
         types, use the
         <code class="literal">
          DYNAMIC
         </code>
         . See
         <a class="xref" href="innodb-row-format.html#innodb-row-format-dynamic" title="DYNAMIC Row Format">
          DYNAMIC Row Format
         </a>
         for
              requirements associated with the
         <code class="literal">
          DYNAMIC
         </code>
         row format.
        </p>
       </li>
       <li class="listitem">
        <p>
         To enable compression for
         <code class="literal">
          InnoDB
         </code>
         tables, specify
         <code class="literal">
          ROW_FORMAT=COMPRESSED
         </code>
         .
              The
         <code class="literal">
          ROW_FORMAT=COMPRESSED
         </code>
         option is not
              supported when creating temporary tables. See
         <a class="xref" href="innodb-compression.html" title="17.9 InnoDB Table and Page Compression">
          Section 17.9, “InnoDB Table and Page Compression”
         </a>
         for requirements
              associated with the
         <code class="literal">
          COMPRESSED
         </code>
         row
              format.
        </p>
       </li>
       <li class="listitem">
        <p>
         The row format used in older versions of MySQL can still
              be requested by specifying the
         <code class="literal">
          REDUNDANT
         </code>
         row format.
        </p>
       </li>
       <li class="listitem">
        <p>
         When you specify a non-default
         <code class="literal">
          ROW_FORMAT
         </code>
         clause, consider also
              enabling the
         <a class="link" href="innodb-parameters.html#sysvar_innodb_strict_mode">
          <code class="literal">
           innodb_strict_mode
          </code>
         </a>
         configuration option.
        </p>
       </li>
       <li class="listitem">
        <p>
         <code class="literal">
          ROW_FORMAT=FIXED
         </code>
         is not supported. If
         <code class="literal">
          ROW_FORMAT=FIXED
         </code>
         is specified while
         <a class="link" href="innodb-parameters.html#sysvar_innodb_strict_mode">
          <code class="literal">
           innodb_strict_mode
          </code>
         </a>
         is
              disabled,
         <code class="literal">
          InnoDB
         </code>
         issues a warning and
              assumes
         <code class="literal">
          ROW_FORMAT=DYNAMIC
         </code>
         . If
         <code class="literal">
          ROW_FORMAT=FIXED
         </code>
         is specified while
         <a class="link" href="innodb-parameters.html#sysvar_innodb_strict_mode">
          <code class="literal">
           innodb_strict_mode
          </code>
         </a>
         is
              enabled, which is the default,
         <code class="literal">
          InnoDB
         </code>
         returns an error.
        </p>
       </li>
       <li class="listitem">
        <p>
         For additional information about
         <code class="literal">
          InnoDB
         </code>
         row formats, see
         <a class="xref" href="innodb-row-format.html" title="17.10 InnoDB Row Formats">
          Section 17.10, “InnoDB Row Formats”
         </a>
         .
        </p>
       </li>
      </ul>
     </div>
     <p>
      For
      <code class="literal">
       MyISAM
      </code>
      tables, the option value can be
      <code class="literal">
       FIXED
      </code>
      or
      <code class="literal">
       DYNAMIC
      </code>
      for
          static or variable-length row format.
      <a class="link" href="myisampack.html" title="6.6.6 myisampack — Generate Compressed, Read-Only MyISAM Tables">
       <span class="command">
        <strong>
         myisampack
        </strong>
       </span>
      </a>
      sets the type to
      <code class="literal">
       COMPRESSED
      </code>
      . See
      <a class="xref" href="myisam-table-formats.html" title="18.2.3 MyISAM Table Storage Formats">
       Section 18.2.3, “MyISAM Table Storage Formats”
      </a>
      .
     </p>
     <p>
      For
      <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
       <code class="literal">
        NDB
       </code>
      </a>
      tables, the default
      <code class="literal">
       ROW_FORMAT
      </code>
      is
      <code class="literal">
       DYNAMIC
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       START TRANSACTION
      </code>
     </p>
     <p>
      This is an internal-use table option, used to permit
      <code class="literal">
       CREATE TABLE ... SELECT
      </code>
      to be logged as a
          single, atomic transaction in the binary log when using
          row-based replication with a storage engine that supports
          atomic DDL. Only
      <a class="link" href="binlog.html" title="15.7.8.1 BINLOG Statement">
       <code class="literal">
        BINLOG
       </code>
      </a>
      ,
      <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
       <code class="literal">
        COMMIT
       </code>
      </a>
      , and
      <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
       <code class="literal">
        ROLLBACK
       </code>
      </a>
      statements are permitted after
      <code class="literal">
       CREATE TABLE ... START
          TRANSACTION
      </code>
      . For related information, see
      <a class="xref" href="atomic-ddl.html" title="15.1.1 Atomic Data Definition Statement Support">
       Section 15.1.1, “Atomic Data Definition Statement Support”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       STATS_AUTO_RECALC
      </code>
     </p>
     <p>
      Specifies whether to automatically recalculate
      <a class="link" href="glossary.html#glos_persistent_statistics" title="persistent statistics">
       persistent
          statistics
      </a>
      for an
      <code class="literal">
       InnoDB
      </code>
      table. The
          value
      <code class="literal">
       DEFAULT
      </code>
      causes the persistent
          statistics setting for the table to be determined by the
      <a class="link" href="innodb-parameters.html#sysvar_innodb_stats_auto_recalc">
       <code class="literal">
        innodb_stats_auto_recalc
       </code>
      </a>
      configuration option. The value
      <code class="literal">
       1
      </code>
      causes
          statistics to be recalculated when 10% of the data in the
          table has changed. The value
      <code class="literal">
       0
      </code>
      prevents
          automatic recalculation for this table; with this setting,
          issue an
      <a class="link" href="analyze-table.html" title="15.7.3.1 ANALYZE TABLE Statement">
       <code class="literal">
        ANALYZE TABLE
       </code>
      </a>
      statement to recalculate the statistics after making
          substantial changes to the table. For more information about
          the persistent statistics feature, see
      <a class="xref" href="innodb-persistent-stats.html" title="17.8.10.1 Configuring Persistent Optimizer Statistics Parameters">
       Section 17.8.10.1, “Configuring Persistent Optimizer Statistics Parameters”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       STATS_PERSISTENT
      </code>
     </p>
     <p>
      Specifies whether to enable
      <a class="link" href="glossary.html#glos_persistent_statistics" title="persistent statistics">
       persistent
          statistics
      </a>
      for an
      <code class="literal">
       InnoDB
      </code>
      table. The
          value
      <code class="literal">
       DEFAULT
      </code>
      causes the persistent
          statistics setting for the table to be determined by the
      <a class="link" href="innodb-parameters.html#sysvar_innodb_stats_persistent">
       <code class="literal">
        innodb_stats_persistent
       </code>
      </a>
      configuration option. The value
      <code class="literal">
       1
      </code>
      enables
          persistent statistics for the table, while the value
      <code class="literal">
       0
      </code>
      turns off this feature. After enabling
          persistent statistics through a
      <code class="literal">
       CREATE
          TABLE
      </code>
      or
      <code class="literal">
       ALTER TABLE
      </code>
      statement,
          issue an
      <a class="link" href="analyze-table.html" title="15.7.3.1 ANALYZE TABLE Statement">
       <code class="literal">
        ANALYZE TABLE
       </code>
      </a>
      statement to calculate the statistics, after loading
          representative data into the table. For more information about
          the persistent statistics feature, see
      <a class="xref" href="innodb-persistent-stats.html" title="17.8.10.1 Configuring Persistent Optimizer Statistics Parameters">
       Section 17.8.10.1, “Configuring Persistent Optimizer Statistics Parameters”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       STATS_SAMPLE_PAGES
      </code>
     </p>
     <p>
      The number of index pages to sample when estimating
          cardinality and other statistics for an indexed column, such
          as those calculated by
      <a class="link" href="analyze-table.html" title="15.7.3.1 ANALYZE TABLE Statement">
       <code class="literal">
        ANALYZE
          TABLE
       </code>
      </a>
      . For more information, see
      <a class="xref" href="innodb-persistent-stats.html" title="17.8.10.1 Configuring Persistent Optimizer Statistics Parameters">
       Section 17.8.10.1, “Configuring Persistent Optimizer Statistics Parameters”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       TABLESPACE
      </code>
     </p>
     <p>
      The
      <code class="literal">
       TABLESPACE
      </code>
      clause can be used to create
          an
      <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
       <code class="literal">
        InnoDB
       </code>
      </a>
      table in an existing
          general tablespace, a file-per-table tablespace, or the system
          tablespace.
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa19065397"><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> <em class="replaceable">tbl_name</em> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">TABLESPACE</span> <span class="token punctuation">[</span><span class="token operator">=</span><span class="token punctuation">]</span> <em class="replaceable">tablespace_name</em></code></pre>
     </div>
     <p>
      The general tablespace that you specify must exist prior to
          using the
      <code class="literal">
       TABLESPACE
      </code>
      clause. For
          information about general tablespaces, see
      <a class="xref" href="general-tablespaces.html" title="17.6.3.3 General Tablespaces">
       Section 17.6.3.3, “General Tablespaces”
      </a>
      .
     </p>
     <p>
      The
      <code class="literal">
       <em class="replaceable">
        <code>
         tablespace_name
        </code>
       </em>
      </code>
      is a case-sensitive identifier. It may be quoted or unquoted.
          The forward slash character (
      <span class="quote">
       “
       <span class="quote">
        /
       </span>
       ”
      </span>
      ) is not
          permitted. Names beginning with
      <span class="quote">
       “
       <span class="quote">
        innodb_
       </span>
       ”
      </span>
      are
          reserved for special use.
     </p>
     <p>
      To create a table in the system tablespace, specify
      <code class="literal">
       innodb_system
      </code>
      as the tablespace name.
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa13277967"><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> <em class="replaceable">tbl_name</em> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">TABLESPACE</span> <span class="token punctuation">[</span><span class="token operator">=</span><span class="token punctuation">]</span> innodb_system</code></pre>
     </div>
     <p>
      Using
      <code class="literal">
       TABLESPACE [=] innodb_system
      </code>
      , you can
          place a table of any uncompressed row format in the system
          tablespace regardless of the
      <a class="link" href="innodb-parameters.html#sysvar_innodb_file_per_table">
       <code class="literal">
        innodb_file_per_table
       </code>
      </a>
      setting. For example, you can add a table with
      <code class="literal">
       ROW_FORMAT=DYNAMIC
      </code>
      to the system tablespace
          using
      <code class="literal">
       TABLESPACE [=] innodb_system
      </code>
      .
     </p>
     <p>
      To create a table in a file-per-table tablespace, specify
      <code class="literal">
       innodb_file_per_table
      </code>
      as the tablespace
          name.
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa71405264"><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> <em class="replaceable">tbl_name</em> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">TABLESPACE</span> <span class="token punctuation">[</span><span class="token operator">=</span><span class="token punctuation">]</span> innodb_file_per_table</code></pre>
     </div>
     <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
      <div class="admon-title">
       Note
      </div>
      <p>
       If
       <a class="link" href="innodb-parameters.html#sysvar_innodb_file_per_table">
        <code class="literal">
         innodb_file_per_table
        </code>
       </a>
       is
            enabled, you need not specify
       <code class="literal">
        TABLESPACE=innodb_file_per_table
       </code>
       to
            create an
       <code class="literal">
        InnoDB
       </code>
       file-per-table
            tablespace.
       <code class="literal">
        InnoDB
       </code>
       tables are created in
            file-per-table tablespaces by default when
       <a class="link" href="innodb-parameters.html#sysvar_innodb_file_per_table">
        <code class="literal">
         innodb_file_per_table
        </code>
       </a>
       is
            enabled.
      </p>
     </div>
     <p>
      The
      <code class="literal">
       DATA DIRECTORY
      </code>
      clause is permitted with
      <code class="literal">
       CREATE TABLE ...
          TABLESPACE=innodb_file_per_table
      </code>
      but is otherwise
          not supported for use in combination with the
      <code class="literal">
       TABLESPACE
      </code>
      clause. The directory specified
          in a
      <code class="literal">
       DATA DIRECTORY
      </code>
      clause must be known to
      <code class="literal">
       InnoDB
      </code>
      . For more information, see
      <a class="xref" href="innodb-create-table-external.html#innodb-create-table-external-data-directory" title="Using the DATA DIRECTORY Clause">
       Using the DATA DIRECTORY Clause
      </a>
      .
     </p>
     <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
      <div class="admon-title">
       Note
      </div>
      <p>
       Support for
       <code class="literal">
        TABLESPACE =
            innodb_file_per_table
       </code>
       and
       <code class="literal">
        TABLESPACE =
            innodb_temporary
       </code>
       clauses with
       <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
        <code class="literal">
         CREATE
            TEMPORARY TABLE
        </code>
       </a>
       is deprecated; expect it to be
            removed in a future version of MySQL.
      </p>
     </div>
     <p>
      The
      <code class="literal">
       STORAGE
      </code>
      table option is employed only
          with
      <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
       <code class="literal">
        NDB
       </code>
      </a>
      tables.
      <code class="literal">
       STORAGE
      </code>
      determines the type of storage
          used, and can be either of
      <code class="literal">
       DISK
      </code>
      or
      <code class="literal">
       MEMORY
      </code>
      .
     </p>
     <p>
      <code class="literal">
       TABLESPACE ... STORAGE DISK
      </code>
      assigns a table
          to an NDB Cluster Disk Data tablespace.
      <code class="literal">
       STORAGE
          DISK
      </code>
      cannot be used in
      <code class="literal">
       CREATE
          TABLE
      </code>
      unless preceded by
      <code class="literal">
       TABLESPACE
      </code>
      <em class="replaceable">
       <code>
        tablespace_name
       </code>
      </em>
      .
     </p>
     <p>
      For
      <code class="literal">
       STORAGE MEMORY
      </code>
      , the tablespace name is
          optional, thus, you can use
      <code class="literal">
       TABLESPACE
       <em class="replaceable">
        <code>
         tablespace_name
        </code>
       </em>
       STORAGE
          MEMORY
      </code>
      or simply
      <code class="literal">
       STORAGE MEMORY
      </code>
      to specify explicitly that the table is in-memory.
     </p>
     <p>
      See
      <a class="xref" href="mysql-cluster-disk-data.html" title="25.6.11 NDB Cluster Disk Data Tables">
       Section 25.6.11, “NDB Cluster Disk Data Tables”
      </a>
      , for more
          information.
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="union.html" title="15.2.18 UNION Clause">
       <code class="literal">
        UNION
       </code>
      </a>
     </p>
     <p>
      Used to access a collection of identical
      <code class="literal">
       MyISAM
      </code>
      tables as one. This works only with
      <code class="literal">
       MERGE
      </code>
      tables. See
      <a class="xref" href="merge-storage-engine.html" title="18.7 The MERGE Storage Engine">
       Section 18.7, “The MERGE Storage Engine”
      </a>
      .
     </p>
     <p>
      You must have
      <a class="link" href="privileges-provided.html#priv_select">
       <code class="literal">
        SELECT
       </code>
      </a>
      ,
      <a class="link" href="privileges-provided.html#priv_update">
       <code class="literal">
        UPDATE
       </code>
      </a>
      , and
      <a class="link" href="privileges-provided.html#priv_delete">
       <code class="literal">
        DELETE
       </code>
      </a>
      privileges for the
          tables you map to a
      <code class="literal">
       MERGE
      </code>
      table.
     </p>
     <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
      <div class="admon-title">
       Note
      </div>
      <p>
       Formerly, all tables used had to be in the same database as
            the
       <code class="literal">
        MERGE
       </code>
       table itself. This restriction
            no longer applies.
      </p>
     </div>
    </li>
   </ul>
  </div>
  <h4>
   <a name="create-table-partitioning">
   </a>
   Table Partitioning
  </h4>
  <p>
   <em class="replaceable">
    <code>
     partition_options
    </code>
   </em>
   can be used to
      control partitioning of the table created with
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE TABLE
    </code>
   </a>
   .
  </p>
  <p>
   Not all options shown in the syntax for
   <em class="replaceable">
    <code>
     partition_options
    </code>
   </em>
   at the beginning of
      this section are available for all partitioning types. Please see
      the listings for the following individual types for information
      specific to each type, and see
   <a class="xref" href="partitioning.html" title="Chapter 26 Partitioning">
    Chapter 26,
    <i>
     Partitioning
    </i>
   </a>
   , for
      more complete information about the workings of and uses for
      partitioning in MySQL, as well as additional examples of table
      creation and other statements relating to MySQL partitioning.
  </p>
  <p>
   Partitions can be modified, merged, added to tables, and dropped
      from tables. For basic information about the MySQL statements to
      accomplish these tasks, see
   <a class="xref" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    Section 15.1.9, “ALTER TABLE Statement”
   </a>
   . For
      more detailed descriptions and examples, see
   <a class="xref" href="partitioning-management.html" title="26.3 Partition Management">
    Section 26.3, “Partition Management”
   </a>
   .
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       PARTITION BY
      </code>
     </p>
     <p>
      If used, a
      <em class="replaceable">
       <code>
        partition_options
       </code>
      </em>
      clause
          begins with
      <code class="literal">
       PARTITION BY
      </code>
      . This clause
          contains the function that is used to determine the partition;
          the function returns an integer value ranging from 1 to
      <em class="replaceable">
       <code>
        num
       </code>
      </em>
      , where
      <em class="replaceable">
       <code>
        num
       </code>
      </em>
      is the number of partitions.
          (The maximum number of user-defined partitions which a table
          may contain is 1024; the number of
          subpartitions—discussed later in this section—is
          included in this maximum.)
     </p>
     <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
      <div class="admon-title">
       Note
      </div>
      <p>
       The expression (
       <em class="replaceable">
        <code>
         expr
        </code>
       </em>
       ) used in a
       <code class="literal">
        PARTITION BY
       </code>
       clause cannot refer to any
            columns not in the table being created; such references are
            specifically not permitted and cause the statement to fail
            with an error. (Bug #29444)
      </p>
     </div>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       HASH(
       <em class="replaceable">
        <code>
         expr
        </code>
       </em>
       )
      </code>
     </p>
     <p>
      Hashes one or more columns to create a key for placing and
          locating rows.
      <em class="replaceable">
       <code>
        expr
       </code>
      </em>
      is an
          expression using one or more table columns. This can be any
          valid MySQL expression (including MySQL functions) that yields
          a single integer value. For example, these are both valid
      <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
       <code class="literal">
        CREATE TABLE
       </code>
      </a>
      statements using
      <code class="literal">
       PARTITION BY HASH
      </code>
      :
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa91094464"><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> t1 <span class="token punctuation">(</span>col1 <span class="token datatype">INT</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>
    <span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> <span class="token keyword">HASH</span><span class="token punctuation">(</span>col1<span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t1 <span class="token punctuation">(</span>col1 <span class="token datatype">INT</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">DATETIME</span><span class="token punctuation">)</span>
    <span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> <span class="token keyword">HASH</span> <span class="token punctuation">(</span> <span class="token function">YEAR</span><span class="token punctuation">(</span>col3<span class="token punctuation">)</span> <span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      You may not use either
      <code class="literal">
       VALUES LESS THAN
      </code>
      or
      <code class="literal">
       VALUES IN
      </code>
      clauses with
      <code class="literal">
       PARTITION
          BY HASH
      </code>
      .
     </p>
     <p>
      <code class="literal">
       PARTITION BY HASH
      </code>
      uses the remainder of
      <em class="replaceable">
       <code>
        expr
       </code>
      </em>
      divided by the number of
          partitions (that is, the modulus). For examples and additional
          information, see
      <a class="xref" href="partitioning-hash.html" title="26.2.4 HASH Partitioning">
       Section 26.2.4, “HASH Partitioning”
      </a>
      .
     </p>
     <p>
      The
      <code class="literal">
       LINEAR
      </code>
      keyword entails a somewhat
          different algorithm. In this case, the number of the partition
          in which a row is stored is calculated as the result of one or
          more logical
      <a class="link" href="logical-operators.html#operator_and">
       <code class="literal">
        AND
       </code>
      </a>
      operations. For
          discussion and examples of linear hashing, see
      <a class="xref" href="partitioning-linear-hash.html" title="26.2.4.1 LINEAR HASH Partitioning">
       Section 26.2.4.1, “LINEAR HASH Partitioning”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       KEY(
       <em class="replaceable">
        <code>
         column_list
        </code>
       </em>
       )
      </code>
     </p>
     <p>
      This is similar to
      <code class="literal">
       HASH
      </code>
      , except that MySQL
          supplies the hashing function so as to guarantee an even data
          distribution. The
      <em class="replaceable">
       <code>
        column_list
       </code>
      </em>
      argument is simply a list of 1 or more table columns (maximum:
          16). This example shows a simple table partitioned by key,
          with 4 partitions:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa51188384"><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 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">KEY</span><span class="token punctuation">(</span>col3<span class="token punctuation">)</span>
    <span class="token keyword">PARTITIONS</span> <span class="token number">4</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      For tables that are partitioned by key, you can employ linear
          partitioning by using the
      <code class="literal">
       LINEAR
      </code>
      keyword.
          This has the same effect as with tables that are partitioned
          by
      <code class="literal">
       HASH
      </code>
      . That is, the partition number is
          found using the
      <a class="link" href="bit-functions.html#operator_bitwise-and">
       <code class="literal">
        &amp;
       </code>
      </a>
      operator rather than the modulus (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>
      , and
      <a class="xref" href="partitioning-key.html" title="26.2.5 KEY Partitioning">
       Section 26.2.5, “KEY Partitioning”
      </a>
      , for details). This example
          uses linear partitioning by key to distribute data between 5
          partitions:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa7230228"><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 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>col3<span class="token punctuation">)</span>
    <span class="token keyword">PARTITIONS</span> <span class="token number">5</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      The
      <code class="literal">
       ALGORITHM={1 | 2}
      </code>
      option is supported
          with
      <code class="literal">
       [SUB]PARTITION BY [LINEAR] KEY
      </code>
      .
      <code class="literal">
       ALGORITHM=1
      </code>
      causes the server to use the
          same key-hashing functions as MySQL 5.1;
      <code class="literal">
       ALGORITHM=2
      </code>
      means that the server employs
          the key-hashing functions implemented and used by default for
          new
      <code class="literal">
       KEY
      </code>
      partitioned tables in MySQL 5.5 and
          later. (Partitioned tables created with the key-hashing
          functions employed in MySQL 5.5 and later cannot be used by a
          MySQL 5.1 server.) Not specifying the option has the same
          effect as using
      <code class="literal">
       ALGORITHM=2
      </code>
      . This option is
          intended for use chiefly when upgrading or downgrading
      <code class="literal">
       [LINEAR] KEY
      </code>
      partitioned tables between
          MySQL 5.1 and later MySQL versions, or for creating tables
          partitioned by
      <code class="literal">
       KEY
      </code>
      or
      <code class="literal">
       LINEAR
          KEY
      </code>
      on a MySQL 5.5 or later server which can be used
          on a MySQL 5.1 server. For more information, see
      <a class="xref" href="alter-table-partition-operations.html" title="15.1.9.1 ALTER TABLE Partition Operations">
       Section 15.1.9.1, “ALTER TABLE Partition Operations”
      </a>
      .
     </p>
     <p>
      <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
       <span class="command">
        <strong>
         mysqldump
        </strong>
       </span>
      </a>
      writes this option encased in
          versioned comments.
     </p>
     <p>
      <code class="literal">
       ALGORITHM=1
      </code>
      is shown when necessary 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>
      using versioned comments in the same manner as
      <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
       <span class="command">
        <strong>
         mysqldump
        </strong>
       </span>
      </a>
      .
      <code class="literal">
       ALGORITHM=2
      </code>
      is always omitted from
      <code class="literal">
       SHOW CREATE TABLE
      </code>
      output, even if this option was specified when creating the
          original table.
     </p>
     <p>
      You may not use either
      <code class="literal">
       VALUES LESS THAN
      </code>
      or
      <code class="literal">
       VALUES IN
      </code>
      clauses with
      <code class="literal">
       PARTITION
          BY KEY
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       RANGE(
       <em class="replaceable">
        <code>
         expr
        </code>
       </em>
       )
      </code>
     </p>
     <p>
      In this case,
      <em class="replaceable">
       <code>
        expr
       </code>
      </em>
      shows a range of
          values using a set of
      <code class="literal">
       VALUES LESS THAN
      </code>
      operators. When using range partitioning, you must define at
          least one partition using
      <code class="literal">
       VALUES LESS THAN
      </code>
      .
          You cannot use
      <code class="literal">
       VALUES IN
      </code>
      with range
          partitioning.
     </p>
     <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
      <div class="admon-title">
       Note
      </div>
      <p>
       For tables partitioned by
       <code class="literal">
        RANGE
       </code>
       ,
       <code class="literal">
        VALUES LESS THAN
       </code>
       must be used with either
            an integer literal value or an expression that evaluates to
            a single integer value. In MySQL 8.4, you can
            overcome this limitation in a table that is defined using
       <code class="literal">
        PARTITION BY RANGE COLUMNS
       </code>
       , as described
            later in this section.
      </p>
     </div>
     <p>
      Suppose that you have a table that you wish to partition on a
          column containing year values, according to the following
          scheme.
     </p>
     <div class="informaltable">
      <table summary="A table partitioning scheme based on a column containing year values, as described in the preceding text. The table lists partition numbers and corresponding range of years.">
       <colgroup>
        <col style="width: 40%"/>
        <col style="width: 60%"/>
       </colgroup>
       <thead>
        <tr>
         <th>
          Partition Number:
         </th>
         <th>
          Years Range:
         </th>
        </tr>
       </thead>
       <tbody>
        <tr>
         <td>
          0
         </td>
         <td>
          1990 and earlier
         </td>
        </tr>
        <tr>
         <td>
          1
         </td>
         <td>
          1991 to 1994
         </td>
        </tr>
        <tr>
         <td>
          2
         </td>
         <td>
          1995 to 1998
         </td>
        </tr>
        <tr>
         <td>
          3
         </td>
         <td>
          1999 to 2002
         </td>
        </tr>
        <tr>
         <td>
          4
         </td>
         <td>
          2003 to 2005
         </td>
        </tr>
        <tr>
         <td>
          5
         </td>
         <td>
          2006 and later
         </td>
        </tr>
       </tbody>
      </table>
     </div>
     <p>
      A table implementing such a partitioning scheme can be
          realized by the
      <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
       <code class="literal">
        CREATE TABLE
       </code>
      </a>
      statement shown here:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa56828750"><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> t1 <span class="token punctuation">(</span>
    year_col  <span class="token datatype">INT</span><span class="token punctuation">,</span>
    some_data <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>year_col<span class="token punctuation">)</span> <span class="token punctuation">(</span>
    <span class="token keyword">PARTITION</span> p0 <span class="token keyword">VALUES</span> <span class="token keyword">LESS</span> <span class="token keyword">THAN</span> <span class="token punctuation">(</span><span class="token number">1991</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">PARTITION</span> p1 <span class="token keyword">VALUES</span> <span class="token keyword">LESS</span> <span class="token keyword">THAN</span> <span class="token punctuation">(</span><span class="token number">1995</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">1999</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">2002</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">2006</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 keyword">MAXVALUE</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      <code class="literal">
       PARTITION ... VALUES LESS THAN ...
      </code>
      statements work in a consecutive fashion.
      <code class="literal">
       VALUES LESS
          THAN MAXVALUE
      </code>
      works to specify
      <span class="quote">
       “
       <span class="quote">
        leftover
       </span>
       ”
      </span>
      values that are greater than the
          maximum value otherwise specified.
     </p>
     <p>
      <code class="literal">
       VALUES LESS THAN
      </code>
      clauses work sequentially
          in a manner similar to that of the
      <code class="literal">
       case
      </code>
      portions of a
      <code class="literal">
       switch ... case
      </code>
      block (as
          found in many programming languages such as C, Java, and PHP).
          That is, the clauses must be arranged in such a way that the
          upper limit specified in each successive
      <code class="literal">
       VALUES LESS
          THAN
      </code>
      is greater than that of the previous one, with
          the one referencing
      <code class="literal">
       MAXVALUE
      </code>
      coming last of
          all in the list.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       RANGE
          COLUMNS(
       <em class="replaceable">
        <code>
         column_list
        </code>
       </em>
       )
      </code>
     </p>
     <p>
      This variant on
      <code class="literal">
       RANGE
      </code>
      facilitates partition
          pruning for queries using range conditions on multiple columns
          (that is, having conditions such as
      <code class="literal">
       WHERE a = 1 AND b
          &lt; 10
      </code>
      or
      <code class="literal">
       WHERE a = 1 AND b = 10 AND c
          &lt; 10
      </code>
      ). It enables you to specify value ranges in
          multiple columns by using a list of columns in the
      <code class="literal">
       COLUMNS
      </code>
      clause and a set of column values
          in each
      <code class="literal">
       PARTITION ... VALUES LESS THAN
          (
       <em class="replaceable">
        <code>
         value_list
        </code>
       </em>
       )
      </code>
      partition
          definition clause. (In the simplest case, this set consists of
          a single column.) The maximum number of columns that can be
          referenced in the
      <em class="replaceable">
       <code>
        column_list
       </code>
      </em>
      and
      <em class="replaceable">
       <code>
        value_list
       </code>
      </em>
      is 16.
     </p>
     <p>
      The
      <em class="replaceable">
       <code>
        column_list
       </code>
      </em>
      used in the
      <code class="literal">
       COLUMNS
      </code>
      clause may contain only names of
          columns; each column in the list must be one of the following
          MySQL data types: the integer types; the string types; and
          time or date column types. Columns using
      <code class="literal">
       BLOB
      </code>
      ,
      <code class="literal">
       TEXT
      </code>
      ,
      <code class="literal">
       SET
      </code>
      ,
      <code class="literal">
       ENUM
      </code>
      ,
      <code class="literal">
       BIT
      </code>
      , or spatial data types are not
          permitted; columns that use floating-point number types are
          also not permitted. You also may not use functions or
          arithmetic expressions in the
      <code class="literal">
       COLUMNS
      </code>
      clause.
     </p>
     <p>
      The
      <code class="literal">
       VALUES LESS THAN
      </code>
      clause used in a
          partition definition must specify a literal value for each
          column that appears in the
      <code class="literal">
       COLUMNS()
      </code>
      clause; that is, the list of values used for each
      <code class="literal">
       VALUES LESS THAN
      </code>
      clause must contain the
          same number of values as there are columns listed in the
      <code class="literal">
       COLUMNS
      </code>
      clause. An attempt to use more or
          fewer values in a
      <code class="literal">
       VALUES LESS THAN
      </code>
      clause
          than there are in the
      <code class="literal">
       COLUMNS
      </code>
      clause causes
          the statement to fail with the error
      <span class="errortext">
       Inconsistency
          in usage of column lists for partitioning...
      </span>
      . You
          cannot use
      <code class="literal">
       NULL
      </code>
      for any value appearing in
      <code class="literal">
       VALUES LESS THAN
      </code>
      . It is possible to use
      <code class="literal">
       MAXVALUE
      </code>
      more than once for a given column
          other than the first, as shown in this example:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa22010538"><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> rc <span class="token punctuation">(</span>
    a <span class="token datatype">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    b <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>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">10</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 number">20</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> 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">50</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 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">65</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 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 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>
      Each value used in a
      <code class="literal">
       VALUES LESS THAN
      </code>
      value
          list must match the type of the corresponding column exactly;
          no conversion is made. For example, you cannot use the string
      <code class="literal">
       '1'
      </code>
      for a value that matches a column that
          uses an integer type (you must use the numeral
      <code class="literal">
       1
      </code>
      instead), nor can you use the numeral
      <code class="literal">
       1
      </code>
      for a value that matches a column that
          uses a string type (in such a case, you must use a quoted
          string:
      <code class="literal">
       '1'
      </code>
      ).
     </p>
     <p>
      For more information, see
      <a class="xref" href="partitioning-range.html" title="26.2.1 RANGE Partitioning">
       Section 26.2.1, “RANGE Partitioning”
      </a>
      , and
      <a class="xref" href="partitioning-pruning.html" title="26.4 Partition Pruning">
       Section 26.4, “Partition Pruning”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       LIST(
       <em class="replaceable">
        <code>
         expr
        </code>
       </em>
       )
      </code>
     </p>
     <p>
      This is useful when assigning partitions based on a table
          column with a restricted set of possible values, such as a
          state or country code. In such a case, all rows pertaining to
          a certain state or country can be assigned to a single
          partition, or a partition can be reserved for a certain set of
          states or countries. It is similar to
      <code class="literal">
       RANGE
      </code>
      , except that only
      <code class="literal">
       VALUES
          IN
      </code>
      may be used to specify permissible values for
          each partition.
     </p>
     <p>
      <code class="literal">
       VALUES IN
      </code>
      is used with a list of values to
          be matched. For instance, you could create a partitioning
          scheme such as the following:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa66858137"><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> client_firms <span class="token punctuation">(</span>
    id   <span class="token datatype">INT</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">35</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">LIST</span> <span class="token punctuation">(</span>id<span class="token punctuation">)</span> <span class="token punctuation">(</span>
    <span class="token keyword">PARTITION</span> r0 <span class="token keyword">VALUES</span> <span class="token keyword">IN</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">5</span><span class="token punctuation">,</span> <span class="token number">9</span><span class="token punctuation">,</span> <span class="token number">13</span><span class="token punctuation">,</span> <span class="token number">17</span><span class="token punctuation">,</span> <span class="token number">21</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">PARTITION</span> r1 <span class="token keyword">VALUES</span> <span class="token keyword">IN</span> <span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span> <span class="token number">6</span><span class="token punctuation">,</span> <span class="token number">10</span><span class="token punctuation">,</span> <span class="token number">14</span><span class="token punctuation">,</span> <span class="token number">18</span><span class="token punctuation">,</span> <span class="token number">22</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">PARTITION</span> r2 <span class="token keyword">VALUES</span> <span class="token keyword">IN</span> <span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">,</span> <span class="token number">7</span><span class="token punctuation">,</span> <span class="token number">11</span><span class="token punctuation">,</span> <span class="token number">15</span><span class="token punctuation">,</span> <span class="token number">19</span><span class="token punctuation">,</span> <span class="token number">23</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">PARTITION</span> r3 <span class="token keyword">VALUES</span> <span class="token keyword">IN</span> <span class="token punctuation">(</span><span class="token number">4</span><span class="token punctuation">,</span> <span class="token number">8</span><span class="token punctuation">,</span> <span class="token number">12</span><span class="token punctuation">,</span> <span class="token number">16</span><span class="token punctuation">,</span> <span class="token number">20</span><span class="token punctuation">,</span> <span class="token number">24</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      When using list partitioning, you must define at least one
          partition using
      <code class="literal">
       VALUES IN
      </code>
      . You cannot use
      <code class="literal">
       VALUES LESS THAN
      </code>
      with
      <code class="literal">
       PARTITION BY
          LIST
      </code>
      .
     </p>
     <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
      <div class="admon-title">
       Note
      </div>
      <p>
       For tables partitioned by
       <code class="literal">
        LIST
       </code>
       , the value
            list used with
       <code class="literal">
        VALUES IN
       </code>
       must consist of
            integer values only. In MySQL 8.4, you can
            overcome this limitation using partitioning by
       <code class="literal">
        LIST
            COLUMNS
       </code>
       , which is described later in this section.
      </p>
     </div>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       LIST
          COLUMNS(
       <em class="replaceable">
        <code>
         column_list
        </code>
       </em>
       )
      </code>
     </p>
     <p>
      This variant on
      <code class="literal">
       LIST
      </code>
      facilitates partition
          pruning for queries using comparison conditions on multiple
          columns (that is, having conditions such as
      <code class="literal">
       WHERE a =
          5 AND b = 5
      </code>
      or
      <code class="literal">
       WHERE a = 1 AND b = 10 AND c
          = 5
      </code>
      ). It enables you to specify values in multiple
          columns by using a list of columns in the
      <code class="literal">
       COLUMNS
      </code>
      clause and a set of column values
          in each
      <code class="literal">
       PARTITION ... VALUES IN
          (
       <em class="replaceable">
        <code>
         value_list
        </code>
       </em>
       )
      </code>
      partition
          definition clause.
     </p>
     <p>
      The rules governing regarding data types for the column list
          used in
      <code class="literal">
       LIST
          COLUMNS(
       <em class="replaceable">
        <code>
         column_list
        </code>
       </em>
       )
      </code>
      and
          the value list used in
      <code class="literal">
       VALUES
          IN(
       <em class="replaceable">
        <code>
         value_list
        </code>
       </em>
       )
      </code>
      are the
          same as those for the column list used in
      <code class="literal">
       RANGE
          COLUMNS(
       <em class="replaceable">
        <code>
         column_list
        </code>
       </em>
       )
      </code>
      and
          the value list used in
      <code class="literal">
       VALUES LESS
          THAN(
       <em class="replaceable">
        <code>
         value_list
        </code>
       </em>
       )
      </code>
      ,
          respectively, except that in the
      <code class="literal">
       VALUES IN
      </code>
      clause,
      <code class="literal">
       MAXVALUE
      </code>
      is not permitted, and you
          may use
      <code class="literal">
       NULL
      </code>
      .
     </p>
     <p>
      There is one important difference between the list of values
          used for
      <code class="literal">
       VALUES IN
      </code>
      with
      <code class="literal">
       PARTITION
          BY LIST COLUMNS
      </code>
      as opposed to when it is used with
      <code class="literal">
       PARTITION BY LIST
      </code>
      . When used with
      <code class="literal">
       PARTITION BY LIST COLUMNS
      </code>
      , each element in
          the
      <code class="literal">
       VALUES IN
      </code>
      clause must be a
      <span class="emphasis">
       <em>
        set
       </em>
      </span>
      of column values; the number of
          values in each set must be the same as the number of columns
          used in the
      <code class="literal">
       COLUMNS
      </code>
      clause, and the data
          types of these values must match those of the columns (and
          occur in the same order). In the simplest case, the set
          consists of a single column. The maximum number of columns
          that can be used in the
      <em class="replaceable">
       <code>
        column_list
       </code>
      </em>
      and in the elements making up the
      <em class="replaceable">
       <code>
        value_list
       </code>
      </em>
      is 16.
     </p>
     <p>
      The table defined by the following
      <code class="literal">
       CREATE
          TABLE
      </code>
      statement provides an example of a table using
      <code class="literal">
       LIST COLUMNS
      </code>
      partitioning:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa38658655"><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> lc <span class="token punctuation">(</span>
    a <span class="token datatype">INT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    b <span class="token datatype">INT</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">LIST</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">IN</span><span class="token punctuation">(</span> <span class="token punctuation">(</span><span class="token number">0</span><span class="token punctuation">,</span><span class="token number">0</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token boolean">NULL</span><span class="token punctuation">,</span><span class="token boolean">NULL</span><span class="token punctuation">)</span> <span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">PARTITION</span> p1 <span class="token keyword">VALUES</span> <span class="token keyword">IN</span><span class="token punctuation">(</span> <span class="token punctuation">(</span><span class="token number">0</span><span class="token punctuation">,</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token number">0</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token number">0</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 punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">)</span> <span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">PARTITION</span> p2 <span class="token keyword">VALUES</span> <span class="token keyword">IN</span><span class="token punctuation">(</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token number">0</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token number">0</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">,</span><span class="token number">0</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">,</span><span class="token number">1</span><span class="token punctuation">)</span> <span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">PARTITION</span> p3 <span class="token keyword">VALUES</span> <span class="token keyword">IN</span><span class="token punctuation">(</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token number">3</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token number">2</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 punctuation">(</span><span class="token number">3</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token number">3</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 punctuation">)</span><span class="token punctuation">;</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       PARTITIONS
       <em class="replaceable">
        <code>
         num
        </code>
       </em>
      </code>
     </p>
     <p>
      The number of partitions may optionally be specified with a
      <code class="literal">
       PARTITIONS
       <em class="replaceable">
        <code>
         num
        </code>
       </em>
      </code>
      clause, where
      <em class="replaceable">
       <code>
        num
       </code>
      </em>
      is the number of
          partitions. If both this clause
      <span class="emphasis">
       <em>
        and
       </em>
      </span>
      any
      <code class="literal">
       PARTITION
      </code>
      clauses are used,
      <em class="replaceable">
       <code>
        num
       </code>
      </em>
      must be equal to the total
          number of any partitions that are declared using
      <code class="literal">
       PARTITION
      </code>
      clauses.
     </p>
     <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
      <div class="admon-title">
       Note
      </div>
      <p>
       Whether or not you use a
       <code class="literal">
        PARTITIONS
       </code>
       clause in creating a table that is partitioned by
       <code class="literal">
        RANGE
       </code>
       or
       <code class="literal">
        LIST
       </code>
       , you
            must still include at least one
       <code class="literal">
        PARTITION
            VALUES
       </code>
       clause in the table definition (see below).
      </p>
     </div>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       SUBPARTITION BY
      </code>
     </p>
     <p>
      A partition may optionally be divided into a number of
          subpartitions. This can be indicated by using the optional
      <code class="literal">
       SUBPARTITION BY
      </code>
      clause. Subpartitioning may
          be done by
      <code class="literal">
       HASH
      </code>
      or
      <code class="literal">
       KEY
      </code>
      .
          Either of these may be
      <code class="literal">
       LINEAR
      </code>
      . These work
          in the same way as previously described for the equivalent
          partitioning types. (It is not possible to subpartition by
      <code class="literal">
       LIST
      </code>
      or
      <code class="literal">
       RANGE
      </code>
      .)
     </p>
     <p>
      The number of subpartitions can be indicated using the
      <code class="literal">
       SUBPARTITIONS
      </code>
      keyword followed by an
          integer value.
     </p>
    </li>
    <li class="listitem">
     <p>
      Rigorous checking of the value used in
      <code class="literal">
       PARTITIONS
      </code>
      or
      <code class="literal">
       SUBPARTITIONS
      </code>
      clauses is applied and this
          value must adhere to the following rules:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         The value must be a positive, nonzero integer.
        </p>
       </li>
       <li class="listitem">
        <p>
         No leading zeros are permitted.
        </p>
       </li>
       <li class="listitem">
        <p>
         The value must be an integer literal, and cannot not be an
              expression. For example,
         <code class="literal">
          PARTITIONS
              0.2E+01
         </code>
         is not permitted, even though
         <code class="literal">
          0.2E+01
         </code>
         evaluates to
         <code class="literal">
          2
         </code>
         . (Bug #15890)
        </p>
       </li>
      </ul>
     </div>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       <em class="replaceable">
        <code>
         partition_definition
        </code>
       </em>
      </code>
     </p>
     <p>
      Each partition may be individually defined using a
      <em class="replaceable">
       <code>
        partition_definition
       </code>
      </em>
      clause. The
          individual parts making up this clause are as follows:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         <code class="literal">
          PARTITION
          <em class="replaceable">
           <code>
            partition_name
           </code>
          </em>
         </code>
        </p>
        <p>
         Specifies a logical name for the partition.
        </p>
       </li>
       <li class="listitem">
        <p>
         <code class="literal">
          VALUES
         </code>
        </p>
        <p>
         For range partitioning, each partition must include a
         <code class="literal">
          VALUES LESS THAN
         </code>
         clause; for list
              partitioning, you must specify a
         <code class="literal">
          VALUES
              IN
         </code>
         clause for each partition. This is used to
              determine which rows are to be stored in this partition.
              See the discussions of partitioning types in
         <a class="xref" href="partitioning.html" title="Chapter 26 Partitioning">
          Chapter 26,
          <i>
           Partitioning
          </i>
         </a>
         , for syntax examples.
        </p>
       </li>
       <li class="listitem">
        <p>
         <code class="literal">
          [STORAGE] ENGINE
         </code>
        </p>
        <p>
         MySQL accepts a
         <code class="literal">
          [STORAGE] ENGINE
         </code>
         option
              for both
         <code class="literal">
          PARTITION
         </code>
         and
         <code class="literal">
          SUBPARTITION
         </code>
         . Currently, the only way
              in which this option can be used is to set all partitions
              or all subpartitions to the same storage engine, and an
              attempt to set different storage engines for partitions or
              subpartitions in the same table raises the error
         <span class="errortext">
          ERROR 1469 (HY000): The mix of handlers in the
              partitions is not permitted in this version of
              MySQL
         </span>
         .
        </p>
       </li>
       <li class="listitem">
        <p>
         <code class="literal">
          COMMENT
         </code>
        </p>
        <p>
         An optional
         <code class="literal">
          COMMENT
         </code>
         clause may be used
              to specify a string that describes the partition. Example:
        </p>
        <div class="copytoclipboard-wrapper">
         <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa65739169"><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">COMMENT</span> <span class="token operator">=</span> <span class="token string">'Data for the years previous to 1999'</span></code></pre>
        </div>
        <p>
         The maximum length for a partition comment is 1024
              characters.
        </p>
       </li>
       <li class="listitem">
        <p>
         <code class="literal">
          DATA DIRECTORY
         </code>
         and
         <code class="literal">
          INDEX
              DIRECTORY
         </code>
        </p>
        <p>
         <code class="literal">
          DATA DIRECTORY
         </code>
         and
         <code class="literal">
          INDEX
              DIRECTORY
         </code>
         may be used to indicate the directory
              where, respectively, the data and indexes for this
              partition are to be stored. Both the
         <code class="filename">
          <em class="replaceable">
           <code>
            data_dir
           </code>
          </em>
         </code>
         and the
         <code class="filename">
          <em class="replaceable">
           <code>
            index_dir
           </code>
          </em>
         </code>
         must be absolute system path names.
        </p>
        <p>
         The directory specified in a
         <code class="literal">
          DATA
              DIRECTORY
         </code>
         clause must be known to
         <code class="literal">
          InnoDB
         </code>
         . For more information, see
         <a class="xref" href="innodb-create-table-external.html#innodb-create-table-external-data-directory" title="Using the DATA DIRECTORY Clause">
          Using the DATA DIRECTORY Clause
         </a>
         .
        </p>
        <p>
         You must have the
         <a class="link" href="privileges-provided.html#priv_file">
          <code class="literal">
           FILE
          </code>
         </a>
         privilege to use the
         <code class="literal">
          DATA DIRECTORY
         </code>
         or
         <code class="literal">
          INDEX DIRECTORY
         </code>
         partition option.
        </p>
        <p>
         Example:
        </p>
        <div class="copytoclipboard-wrapper">
         <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa40237931"><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> th <span class="token punctuation">(</span>id <span class="token datatype">INT</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">30</span><span class="token punctuation">)</span><span class="token punctuation">,</span> adate <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 punctuation">(</span><span class="token function">YEAR</span><span class="token punctuation">(</span>adate<span class="token punctuation">)</span><span class="token punctuation">)</span>
<span class="token punctuation">(</span>
  <span class="token keyword">PARTITION</span> p1999 <span class="token keyword">VALUES</span> <span class="token keyword">IN</span> <span class="token punctuation">(</span><span class="token number">1995</span><span class="token punctuation">,</span> <span class="token number">1999</span><span class="token punctuation">,</span> <span class="token number">2003</span><span class="token punctuation">)</span>
    <span class="token keyword">DATA</span> <span class="token keyword">DIRECTORY</span> <span class="token operator">=</span> <span class="token string">'/var/appdata/95/data'</span>
    <span class="token keyword">INDEX</span> <span class="token keyword">DIRECTORY</span> <span class="token operator">=</span> <span class="token string">'/var/appdata/95/idx'</span><span class="token punctuation">,</span>
  <span class="token keyword">PARTITION</span> p2000 <span class="token keyword">VALUES</span> <span class="token keyword">IN</span> <span class="token punctuation">(</span><span class="token number">1996</span><span class="token punctuation">,</span> <span class="token number">2000</span><span class="token punctuation">,</span> <span class="token number">2004</span><span class="token punctuation">)</span>
    <span class="token keyword">DATA</span> <span class="token keyword">DIRECTORY</span> <span class="token operator">=</span> <span class="token string">'/var/appdata/96/data'</span>
    <span class="token keyword">INDEX</span> <span class="token keyword">DIRECTORY</span> <span class="token operator">=</span> <span class="token string">'/var/appdata/96/idx'</span><span class="token punctuation">,</span>
  <span class="token keyword">PARTITION</span> p2001 <span class="token keyword">VALUES</span> <span class="token keyword">IN</span> <span class="token punctuation">(</span><span class="token number">1997</span><span class="token punctuation">,</span> <span class="token number">2001</span><span class="token punctuation">,</span> <span class="token number">2005</span><span class="token punctuation">)</span>
    <span class="token keyword">DATA</span> <span class="token keyword">DIRECTORY</span> <span class="token operator">=</span> <span class="token string">'/var/appdata/97/data'</span>
    <span class="token keyword">INDEX</span> <span class="token keyword">DIRECTORY</span> <span class="token operator">=</span> <span class="token string">'/var/appdata/97/idx'</span><span class="token punctuation">,</span>
  <span class="token keyword">PARTITION</span> p2002 <span class="token keyword">VALUES</span> <span class="token keyword">IN</span> <span class="token punctuation">(</span><span class="token number">1998</span><span class="token punctuation">,</span> <span class="token number">2002</span><span class="token punctuation">,</span> <span class="token number">2006</span><span class="token punctuation">)</span>
    <span class="token keyword">DATA</span> <span class="token keyword">DIRECTORY</span> <span class="token operator">=</span> <span class="token string">'/var/appdata/98/data'</span>
    <span class="token keyword">INDEX</span> <span class="token keyword">DIRECTORY</span> <span class="token operator">=</span> <span class="token string">'/var/appdata/98/idx'</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
        </div>
        <p>
         <code class="literal">
          DATA DIRECTORY
         </code>
         and
         <code class="literal">
          INDEX
              DIRECTORY
         </code>
         behave in the same way as in the
         <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
          <code class="literal">
           CREATE TABLE
          </code>
         </a>
         statement's
         <em class="replaceable">
          <code>
           table_option
          </code>
         </em>
         clause as used for
         <code class="literal">
          MyISAM
         </code>
         tables.
        </p>
        <p>
         One data directory and one index directory may be
              specified per partition. If left unspecified, the data and
              indexes are stored by default in the table's database
              directory.
        </p>
        <p>
         The
         <code class="literal">
          DATA DIRECTORY
         </code>
         and
         <code class="literal">
          INDEX
              DIRECTORY
         </code>
         options are ignored for creating
              partitioned tables if
         <a class="link" href="sql-mode.html#sqlmode_no_dir_in_create">
          <code class="literal">
           NO_DIR_IN_CREATE
          </code>
         </a>
         is in
              effect.
        </p>
       </li>
       <li class="listitem">
        <p>
         <code class="literal">
          MAX_ROWS
         </code>
         and
         <code class="literal">
          MIN_ROWS
         </code>
        </p>
        <p>
         May be used to specify, respectively, the maximum and
              minimum number of rows to be stored in the partition. The
              values for
         <em class="replaceable">
          <code>
           max_number_of_rows
          </code>
         </em>
         and
         <em class="replaceable">
          <code>
           min_number_of_rows
          </code>
         </em>
         must be
              positive integers. As with the table-level options with
              the same names, these act only as
         <span class="quote">
          “
          <span class="quote">
           suggestions
          </span>
          ”
         </span>
         to the server and are not hard
              limits.
        </p>
       </li>
       <li class="listitem">
        <p>
         <code class="literal">
          TABLESPACE
         </code>
        </p>
        <p>
         May be used to designate an
         <code class="literal">
          InnoDB
         </code>
         file-per-table tablespace for the partition by specifying
         <code class="literal">
          TABLESPACE `innodb_file_per_table`
         </code>
         . All
              partitions must belong to the same storage engine.
        </p>
        <p>
         Placing
         <code class="literal">
          InnoDB
         </code>
         table partitions in
              shared
         <code class="literal">
          InnoDB
         </code>
         tablespaces is not
              supported. Shared tablespaces include the
         <code class="literal">
          InnoDB
         </code>
         system tablespace and general
              tablespaces.
        </p>
       </li>
      </ul>
     </div>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       <em class="replaceable">
        <code>
         subpartition_definition
        </code>
       </em>
      </code>
     </p>
     <p>
      The partition definition may optionally contain one or more
      <em class="replaceable">
       <code>
        subpartition_definition
       </code>
      </em>
      clauses.
          Each of these consists at a minimum of the
      <code class="literal">
       SUBPARTITION
       <em class="replaceable">
        <code>
         name
        </code>
       </em>
      </code>
      , where
      <em class="replaceable">
       <code>
        name
       </code>
      </em>
      is an identifier for the
          subpartition. Except for the replacement of the
      <code class="literal">
       PARTITION
      </code>
      keyword with
      <code class="literal">
       SUBPARTITION
      </code>
      , the syntax for a subpartition
          definition is identical to that for a partition definition.
     </p>
     <p>
      Subpartitioning must be done by
      <code class="literal">
       HASH
      </code>
      or
      <code class="literal">
       KEY
      </code>
      , and can be done only on
      <code class="literal">
       RANGE
      </code>
      or
      <code class="literal">
       LIST
      </code>
      partitions. See
      <a class="xref" href="partitioning-subpartitions.html" title="26.2.6 Subpartitioning">
       Section 26.2.6, “Subpartitioning”
      </a>
      .
     </p>
    </li>
   </ul>
  </div>
  <p>
   <span class="bold">
    <strong>
     Partitioning by Generated Columns
    </strong>
   </span>
  </p>
  <p>
   Partitioning by generated columns is permitted. For example:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa92395403"><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> t1 <span class="token punctuation">(</span>
  s1 <span class="token datatype">INT</span><span class="token punctuation">,</span>
  s2 <span class="token datatype">INT</span> <span class="token keyword">AS</span> <span class="token punctuation">(</span><span class="token function">EXP</span><span class="token punctuation">(</span>s1<span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token keyword">STORED</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 punctuation">(</span>s2<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">IN</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Partitioning sees a generated column as a regular column, which
      enables workarounds for limitations on functions that are not
      permitted for partitioning (see
   <a class="xref" href="partitioning-limitations-functions.html" title="26.6.3 Partitioning Limitations Relating to Functions">
    Section 26.6.3, “Partitioning Limitations Relating to Functions”
   </a>
   ). The
      preceding example demonstrates this technique:
   <a class="link" href="mathematical-functions.html#function_exp">
    <code class="literal">
     EXP()
    </code>
   </a>
   cannot be used directly in
      the
   <code class="literal">
    PARTITION BY
   </code>
   clause, but a generated column
      defined using
   <a class="link" href="mathematical-functions.html#function_exp">
    <code class="literal">
     EXP()
    </code>
   </a>
   is permitted.
  </p>
 </div>
 <br/>
</div>
