<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="alter-table">
      </a>
      15.1.9 ALTER TABLE Statement
     </h3>
    </div>
   </div>
  </div>
  <div class="toc">
   <dl class="toc">
    <dt>
     <span class="section">
      <a href="alter-table-partition-operations.html">
       15.1.9.1 ALTER TABLE Partition Operations
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="alter-table-generated-columns.html">
       15.1.9.2 ALTER TABLE and Generated Columns
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="alter-table-examples.html">
       15.1.9.3 ALTER TABLE Examples
      </a>
     </span>
    </dt>
   </dl>
  </div>
  <a class="indexterm" name="idm46045189579920">
  </a>
  <a class="indexterm" name="idm46045189578848">
  </a>
  <a class="indexterm" name="idm46045189577360">
  </a>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa70194336"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> <em class="replaceable">tbl_name</em>
    <span class="token punctuation">[</span><em class="replaceable">alter_option</em> <span class="token punctuation">[</span><span class="token punctuation">,</span> <em class="replaceable">alter_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 punctuation">]</span>
    <span class="token punctuation">[</span><em class="replaceable">partition_options</em><span class="token punctuation">]</span>

<em class="replaceable">alter_option</em>: {
    <em class="replaceable">table_options</em>
  <span class="token operator">|</span> <span class="token keyword">ADD</span> <span class="token punctuation">[</span><span class="token keyword">COLUMN</span><span class="token punctuation">]</span> <em class="replaceable">col_name</em> <em class="replaceable">column_definition</em>
        <span class="token punctuation">[</span><span class="token keyword">FIRST</span> <span class="token operator">|</span> <span class="token keyword">AFTER</span> <em class="replaceable">col_name</em><span class="token punctuation">]</span>
  <span class="token operator">|</span> <span class="token keyword">ADD</span> <span class="token punctuation">[</span><span class="token keyword">COLUMN</span><span class="token punctuation">]</span> <span class="token punctuation">(</span><em class="replaceable">col_name</em> <em class="replaceable">column_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 operator">|</span> <span class="token keyword">ADD</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">ADD</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 keyword">ADD</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 keyword">ADD</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 keyword">ADD</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> <span class="token keyword">ADD</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">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>
  <span class="token operator">|</span> <span class="token keyword">DROP</span> {<span class="token keyword">CHECK</span> <span class="token operator">|</span> <span class="token keyword">CONSTRAINT</span>} <em class="replaceable">symbol</em>
  <span class="token operator">|</span> <span class="token keyword">ALTER</span> {<span class="token keyword">CHECK</span> <span class="token operator">|</span> <span class="token keyword">CONSTRAINT</span>} <em class="replaceable">symbol</em> <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 operator">|</span> <span class="token keyword">ALGORITHM</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> INSTANT <span class="token operator">|</span> <span class="token keyword">INPLACE</span> <span class="token operator">|</span> <span class="token keyword">COPY</span>}
  <span class="token operator">|</span> <span class="token keyword">ALTER</span> <span class="token punctuation">[</span><span class="token keyword">COLUMN</span><span class="token punctuation">]</span> <em class="replaceable">col_name</em> {
        <span class="token keyword">SET</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 operator">|</span> <span class="token keyword">SET</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">DROP</span> <span class="token keyword">DEFAULT</span>
    }
  <span class="token operator">|</span> <span class="token keyword">ALTER</span> <span class="token keyword">INDEX</span> <em class="replaceable">index_name</em> {<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">CHANGE</span> <span class="token punctuation">[</span><span class="token keyword">COLUMN</span><span class="token punctuation">]</span> <em class="replaceable">old_col_name</em> <em class="replaceable">new_col_name</em> <em class="replaceable">column_definition</em>
        <span class="token punctuation">[</span><span class="token keyword">FIRST</span> <span class="token operator">|</span> <span class="token keyword">AFTER</span> <em class="replaceable">col_name</em><span class="token punctuation">]</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 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 punctuation">]</span>
  <span class="token operator">|</span> CONVERT <span class="token keyword">TO</span> <span class="token keyword">CHARACTER</span> <span class="token keyword">SET</span> <em class="replaceable">charset_name</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 operator">|</span> {<span class="token keyword">DISABLE</span> <span class="token operator">|</span> <span class="token keyword">ENABLE</span>} <span class="token keyword">KEYS</span>
  <span class="token operator">|</span> {<span class="token keyword">DISCARD</span> <span class="token operator">|</span> <span class="token keyword">IMPORT</span>} <span class="token keyword">TABLESPACE</span>
  <span class="token operator">|</span> <span class="token keyword">DROP</span> <span class="token punctuation">[</span><span class="token keyword">COLUMN</span><span class="token punctuation">]</span> <em class="replaceable">col_name</em>
  <span class="token operator">|</span> <span class="token keyword">DROP</span> {<span class="token keyword">INDEX</span> <span class="token operator">|</span> <span class="token keyword">KEY</span>} <em class="replaceable">index_name</em>
  <span class="token operator">|</span> <span class="token keyword">DROP</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span>
  <span class="token operator">|</span> <span class="token keyword">DROP</span> <span class="token keyword">FOREIGN</span> <span class="token keyword">KEY</span> <em class="replaceable">fk_symbol</em>
  <span class="token operator">|</span> <span class="token keyword">FORCE</span>
  <span class="token operator">|</span> <span class="token keyword">LOCK</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">NONE</span> <span class="token operator">|</span> <span class="token keyword">SHARED</span> <span class="token operator">|</span> <span class="token keyword">EXCLUSIVE</span>}
  <span class="token operator">|</span> <span class="token keyword">MODIFY</span> <span class="token punctuation">[</span><span class="token keyword">COLUMN</span><span class="token punctuation">]</span> <em class="replaceable">col_name</em> <em class="replaceable">column_definition</em>
        <span class="token punctuation">[</span><span class="token keyword">FIRST</span> <span class="token operator">|</span> <span class="token keyword">AFTER</span> <em class="replaceable">col_name</em><span class="token punctuation">]</span>
  <span class="token operator">|</span> <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> <em class="replaceable">col_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 operator">|</span> <span class="token keyword">RENAME</span> <span class="token keyword">COLUMN</span> <em class="replaceable">old_col_name</em> <span class="token keyword">TO</span> <em class="replaceable">new_col_name</em>
  <span class="token operator">|</span> <span class="token keyword">RENAME</span> {<span class="token keyword">INDEX</span> <span class="token operator">|</span> <span class="token keyword">KEY</span>} <em class="replaceable">old_index_name</em> <span class="token keyword">TO</span> <em class="replaceable">new_index_name</em>
  <span class="token operator">|</span> <span class="token keyword">RENAME</span> <span class="token punctuation">[</span><span class="token keyword">TO</span> <span class="token operator">|</span> <span class="token keyword">AS</span><span class="token punctuation">]</span> <em class="replaceable">new_tbl_name</em>
  <span class="token operator">|</span> {<span class="token keyword">WITHOUT</span> <span class="token operator">|</span> <span class="token keyword">WITH</span>} <span class="token keyword">VALIDATION</span>
}

<em class="replaceable">partition_options</em>:
    <em class="replaceable">partition_option</em> <span class="token punctuation">[</span><em class="replaceable">partition_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">partition_option</em>: {
    <span class="token keyword">ADD</span> <span class="token keyword">PARTITION</span> <span class="token punctuation">(</span><em class="replaceable">partition_definition</em><span class="token punctuation">)</span>
  <span class="token operator">|</span> <span class="token keyword">DROP</span> <span class="token keyword">PARTITION</span> <em class="replaceable">partition_names</em>
  <span class="token operator">|</span> <span class="token keyword">DISCARD</span> <span class="token keyword">PARTITION</span> {<em class="replaceable">partition_names</em> <span class="token operator">|</span> <span class="token keyword">ALL</span>} <span class="token keyword">TABLESPACE</span>
  <span class="token operator">|</span> <span class="token keyword">IMPORT</span> <span class="token keyword">PARTITION</span> {<em class="replaceable">partition_names</em> <span class="token operator">|</span> <span class="token keyword">ALL</span>} <span class="token keyword">TABLESPACE</span>
  <span class="token operator">|</span> <span class="token keyword">TRUNCATE</span> <span class="token keyword">PARTITION</span> {<em class="replaceable">partition_names</em> <span class="token operator">|</span> <span class="token keyword">ALL</span>}
  <span class="token operator">|</span> <span class="token keyword">COALESCE</span> <span class="token keyword">PARTITION</span> <span class="token keyword"><em class="replaceable">number</em></span>
  <span class="token operator">|</span> <span class="token keyword">REORGANIZE</span> <span class="token keyword">PARTITION</span> <em class="replaceable">partition_names</em> <span class="token keyword">INTO</span> <span class="token punctuation">(</span><em class="replaceable">partition_definitions</em><span class="token punctuation">)</span>
  <span class="token operator">|</span> <span class="token keyword">EXCHANGE</span> <span class="token keyword">PARTITION</span> <em class="replaceable">partition_name</em> <span class="token keyword">WITH</span> <span class="token keyword">TABLE</span> <em class="replaceable">tbl_name</em> <span class="token punctuation">[</span>{<span class="token keyword">WITH</span> <span class="token operator">|</span> <span class="token keyword">WITHOUT</span>} <span class="token keyword">VALIDATION</span><span class="token punctuation">]</span>
  <span class="token operator">|</span> <span class="token keyword">ANALYZE</span> <span class="token keyword">PARTITION</span> {<em class="replaceable">partition_names</em> <span class="token operator">|</span> <span class="token keyword">ALL</span>}
  <span class="token operator">|</span> <span class="token keyword">CHECK</span> <span class="token keyword">PARTITION</span> {<em class="replaceable">partition_names</em> <span class="token operator">|</span> <span class="token keyword">ALL</span>}
  <span class="token operator">|</span> <span class="token keyword">OPTIMIZE</span> <span class="token keyword">PARTITION</span> {<em class="replaceable">partition_names</em> <span class="token operator">|</span> <span class="token keyword">ALL</span>}
  <span class="token operator">|</span> <span class="token keyword">REBUILD</span> <span class="token keyword">PARTITION</span> {<em class="replaceable">partition_names</em> <span class="token operator">|</span> <span class="token keyword">ALL</span>}
  <span class="token operator">|</span> <span class="token keyword">REPAIR</span> <span class="token keyword">PARTITION</span> {<em class="replaceable">partition_names</em> <span class="token operator">|</span> <span class="token keyword">ALL</span>}
  <span class="token operator">|</span> <span class="token keyword">REMOVE</span> <span class="token keyword">PARTITIONING</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>}
}

<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">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> <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 operator">|</span> <span class="token keyword">MEMORY</span>}<span class="token punctuation">]</span>
  <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 punctuation">(</span>see <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> <span class="token keyword">options</span><span class="token punctuation">)</span></code></pre>
  </div>
  <p>
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
    </code>
   </a>
   changes the structure
      of a table. For example, you can add or delete columns, create or
      destroy indexes, change the type of existing columns, or rename
      columns or the table itself. You can also change characteristics
      such as the storage engine used for the table or the table
      comment.
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      To use
      <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
       <code class="literal">
        ALTER TABLE
       </code>
      </a>
      , you need
      <a class="link" href="privileges-provided.html#priv_alter">
       <code class="literal">
        ALTER
       </code>
      </a>
      ,
      <a class="link" href="privileges-provided.html#priv_create">
       <code class="literal">
        CREATE
       </code>
      </a>
      , and
      <a class="link" href="privileges-provided.html#priv_insert">
       <code class="literal">
        INSERT
       </code>
      </a>
      privileges for the
          table. Renaming a table requires
      <a class="link" href="privileges-provided.html#priv_alter">
       <code class="literal">
        ALTER
       </code>
      </a>
      and
      <a class="link" href="privileges-provided.html#priv_drop">
       <code class="literal">
        DROP
       </code>
      </a>
      on the old table,
      <a class="link" href="privileges-provided.html#priv_alter">
       <code class="literal">
        ALTER
       </code>
      </a>
      ,
      <a class="link" href="privileges-provided.html#priv_create">
       <code class="literal">
        CREATE
       </code>
      </a>
      , and
      <a class="link" href="privileges-provided.html#priv_insert">
       <code class="literal">
        INSERT
       </code>
      </a>
      on the new table.
     </p>
    </li>
    <li class="listitem">
     <p>
      Following the table name, specify the alterations to be made.
          If none are given,
      <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
       <code class="literal">
        ALTER TABLE
       </code>
      </a>
      does nothing.
     </p>
    </li>
    <li class="listitem">
     <p>
      The syntax for many of the permissible alterations is similar
          to clauses of the
      <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
       <code class="literal">
        CREATE TABLE
       </code>
      </a>
      statement.
      <em class="replaceable">
       <code>
        column_definition
       </code>
      </em>
      clauses use the same syntax for
      <code class="literal">
       ADD
      </code>
      and
      <code class="literal">
       CHANGE
      </code>
      as for
      <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
       <code class="literal">
        CREATE
          TABLE
       </code>
      </a>
      . For more information, see
      <a class="xref" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
       Section 15.1.20, “CREATE TABLE Statement”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      The word
      <code class="literal">
       COLUMN
      </code>
      is optional and can be
          omitted, except for
      <code class="literal">
       RENAME COLUMN
      </code>
      (to
          distinguish a column-renaming operation from the
      <code class="literal">
       RENAME
      </code>
      table-renaming operation).
     </p>
    </li>
    <li class="listitem">
     <p>
      Multiple
      <code class="literal">
       ADD
      </code>
      ,
      <code class="literal">
       ALTER
      </code>
      ,
      <code class="literal">
       DROP
      </code>
      , and
      <code class="literal">
       CHANGE
      </code>
      clauses
          are permitted in a single
      <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
       <code class="literal">
        ALTER
          TABLE
       </code>
      </a>
      statement, separated by commas. This is a
          MySQL extension to standard SQL, which permits only one of
          each clause per
      <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
       <code class="literal">
        ALTER TABLE
       </code>
      </a>
      statement. For example, to drop multiple columns in a single
          statement, do this:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa72565496"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t2 <span class="token keyword">DROP</span> <span class="token keyword">COLUMN</span> c<span class="token punctuation">,</span> <span class="token keyword">DROP</span> <span class="token keyword">COLUMN</span> d<span class="token punctuation">;</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      If a storage engine does not support an attempted
      <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
       <code class="literal">
        ALTER TABLE
       </code>
      </a>
      operation, a
          warning may result. Such warnings can be displayed with
      <a class="link" href="show-warnings.html" title="15.7.7.42 SHOW WARNINGS Statement">
       <code class="literal">
        SHOW WARNINGS
       </code>
      </a>
      . See
      <a class="xref" href="show-warnings.html" title="15.7.7.42 SHOW WARNINGS Statement">
       Section 15.7.7.42, “SHOW WARNINGS Statement”
      </a>
      . For information on
          troubleshooting
      <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
       <code class="literal">
        ALTER TABLE
       </code>
      </a>
      ,
          see
      <a class="xref" href="alter-table-problems.html" title="B.3.6.1 Problems with ALTER TABLE">
       Section B.3.6.1, “Problems with ALTER TABLE”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      For information about generated columns, see
      <a class="xref" href="alter-table-generated-columns.html" title="15.1.9.2 ALTER TABLE and Generated Columns">
       Section 15.1.9.2, “ALTER TABLE and Generated Columns”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      For usage examples, see
      <a class="xref" href="alter-table-examples.html" title="15.1.9.3 ALTER TABLE Examples">
       Section 15.1.9.3, “ALTER TABLE Examples”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
       <code class="literal">
        InnoDB
       </code>
      </a>
      supports addition of
          multi-valued indexes on JSON columns using 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>
      . See
      <a class="xref" href="create-index.html#create-index-multi-valued" title="Multi-Valued Indexes">
       Multi-Valued Indexes
      </a>
      , for detailed
          information regarding multi-valued index creation and usage
          of, as well as restrictions and limitations on multi-valued
          indexes.
     </p>
    </li>
    <li class="listitem">
     <p>
      With the
      <a class="ulink" href="/doc/c-api/8.4/en/mysql-info.html" target="_top">
       <code class="literal">
        mysql_info()
       </code>
      </a>
      C API
          function, you can find out how many rows were copied by
      <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
       <code class="literal">
        ALTER TABLE
       </code>
      </a>
      . See
      <a class="ulink" href="/doc/c-api/8.4/en/mysql-info.html" target="_top">
       mysql_info()
      </a>
      .
     </p>
     <a class="indexterm" name="idm46045189467616">
     </a>
    </li>
   </ul>
  </div>
  <p>
   There are several additional aspects to the
   <code class="literal">
    ALTER
      TABLE
   </code>
   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="alter-table.html#alter-table-options" title="Table Options">
       Table Options
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="alter-table.html#alter-table-performance" title="Performance and Space Requirements">
       Performance and Space Requirements
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="alter-table.html#alter-table-concurrency" title="Concurrency Control">
       Concurrency Control
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="alter-table.html#alter-table-add-drop-column" title="Adding and Dropping Columns">
       Adding and Dropping Columns
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="alter-table.html#alter-table-redefine-column" title="Renaming, Redefining, and Reordering Columns">
       Renaming, Redefining, and Reordering Columns
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="alter-table.html#alter-table-index" title="Primary Keys and Indexes">
       Primary Keys and Indexes
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="alter-table.html#alter-table-foreign-key" title="Foreign Keys and Other Constraints">
       Foreign Keys and Other Constraints
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="alter-table.html#alter-table-character-set" title="Changing the Character Set">
       Changing the Character Set
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="alter-table.html#alter-table-import" title="Importing InnoDB Tables">
       Importing InnoDB Tables
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="alter-table.html#alter-table-row-order" title="Row Order for MyISAM Tables">
       Row Order for MyISAM Tables
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="alter-table.html#alter-table-partition-options" title="Partitioning Options">
       Partitioning Options
      </a>
     </p>
    </li>
   </ul>
  </div>
  <h4>
   <a name="alter-table-options">
   </a>
   Table Options
  </h4>
  <p>
   <em class="replaceable">
    <code>
     table_options
    </code>
   </em>
   signifies table options
      of the kind that can be used in the
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE
      TABLE
    </code>
   </a>
   statement, such as
   <code class="literal">
    ENGINE
   </code>
   ,
   <code class="literal">
    AUTO_INCREMENT
   </code>
   ,
   <code class="literal">
    AVG_ROW_LENGTH
   </code>
   ,
   <code class="literal">
    MAX_ROWS
   </code>
   ,
   <code class="literal">
    ROW_FORMAT
   </code>
   , or
   <code class="literal">
    TABLESPACE
   </code>
   .
  </p>
  <p>
   For descriptions of all table options, see
   <a class="xref" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    Section 15.1.20, “CREATE TABLE Statement”
   </a>
   . However,
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
    </code>
   </a>
   ignores
   <code class="literal">
    DATA
      DIRECTORY
   </code>
   and
   <code class="literal">
    INDEX DIRECTORY
   </code>
   when
      given as table options.
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
    </code>
   </a>
   permits them only as partitioning options, and requires that you
      have the
   <code class="literal">
    FILE
   </code>
   privilege.
  </p>
  <p>
   Use of table options with
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER
      TABLE
    </code>
   </a>
   provides a convenient way of altering single table
      characteristics. For example:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      If
      <code class="literal">
       t1
      </code>
      is currently not an
      <code class="literal">
       InnoDB
      </code>
      table, this statement changes its
          storage engine to
      <code class="literal">
       InnoDB
      </code>
      :
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa3578747"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">ENGINE</span> <span class="token operator">=</span> InnoDB<span class="token punctuation">;</span></code></pre>
     </div>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         See
         <a class="xref" href="converting-tables-to-innodb.html" title="17.6.1.5 Converting Tables from MyISAM to InnoDB">
          Section 17.6.1.5, “Converting Tables from MyISAM to InnoDB”
         </a>
         for
              considerations when switching tables to the
         <code class="literal">
          InnoDB
         </code>
         storage engine.
        </p>
       </li>
       <li class="listitem">
        <p>
         When you specify an
         <code class="literal">
          ENGINE
         </code>
         clause,
         <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
          <code class="literal">
           ALTER TABLE
          </code>
         </a>
         rebuilds the
              table. This is true even if the table already has the
              specified storage engine.
        </p>
       </li>
       <li class="listitem">
        <p>
         Running
         <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
          <code class="literal">
           ALTER
              TABLE
           <em class="replaceable">
            <code>
             tbl_name
            </code>
           </em>
           ENGINE=INNODB
          </code>
         </a>
         on an existing
         <code class="literal">
          InnoDB
         </code>
         table performs a
         <span class="quote">
          “
          <span class="quote">
           null
          </span>
          ”
         </span>
         <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
          <code class="literal">
           ALTER
              TABLE
          </code>
         </a>
         operation, which can be used to defragment
              an
         <code class="literal">
          InnoDB
         </code>
         table, as described in
         <a class="xref" href="innodb-file-defragmenting.html" title="17.11.4 Defragmenting a Table">
          Section 17.11.4, “Defragmenting a Table”
         </a>
         . Running
         <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
          <code class="literal">
           ALTER TABLE
           <em class="replaceable">
            <code>
             tbl_name
            </code>
           </em>
           FORCE
          </code>
         </a>
         on an
         <code class="literal">
          InnoDB
         </code>
         table performs the same
              function.
        </p>
       </li>
       <li class="listitem">
        <p>
         <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
          <code class="literal">
           ALTER TABLE
           <em class="replaceable">
            <code>
             tbl_name
            </code>
           </em>
           ENGINE=INNODB
          </code>
         </a>
         and
         <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
          <code class="literal">
           ALTER TABLE
           <em class="replaceable">
            <code>
             tbl_name
            </code>
           </em>
           FORCE
          </code>
         </a>
         use
         <a class="link" href="innodb-online-ddl.html" title="17.12 InnoDB and Online DDL">
          online DDL
         </a>
         . For
              more information, see
         <a class="xref" href="innodb-online-ddl.html" title="17.12 InnoDB and Online DDL">
          Section 17.12, “InnoDB and Online DDL”
         </a>
         .
        </p>
       </li>
       <li class="listitem">
        <p>
         The outcome of attempting to change the storage engine of
              a table is affected by whether the desired storage engine
              is available and the setting of the
         <a class="link" href="sql-mode.html#sqlmode_no_engine_substitution">
          <code class="literal">
           NO_ENGINE_SUBSTITUTION
          </code>
         </a>
         SQL mode, as described in
         <a class="xref" href="sql-mode.html" title="7.1.11 Server SQL Modes">
          Section 7.1.11, “Server SQL Modes”
         </a>
         .
        </p>
       </li>
       <li class="listitem">
        <p>
         To prevent inadvertent loss of data,
         <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
          <code class="literal">
           ALTER TABLE
          </code>
         </a>
         cannot be used
              to change the storage engine of a table to
         <code class="literal">
          MERGE
         </code>
         or
         <code class="literal">
          BLACKHOLE
         </code>
         .
        </p>
       </li>
      </ul>
     </div>
    </li>
    <li class="listitem">
     <p>
      To change the
      <code class="literal">
       InnoDB
      </code>
      table to use
          compressed row-storage format:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa79985828"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">ROW_FORMAT</span> <span class="token operator">=</span> <span class="token keyword">COMPRESSED</span><span class="token punctuation">;</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <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 to
          enable encryption.
     </p>
     <p>
      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 use an
      <code class="literal">
       ENCRYPTION
      </code>
      clause with a setting that differs from the default schema
          encryption setting.
     </p>
     <p>
      <code class="literal">
       ENCRYPTION
      </code>
      is also supported for tables
          residing in general tablespaces.
     </p>
     <p>
      For tables that reside in general tablespaces, table and
          tablespace encryption must match.
     </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 table already uses
      <code class="literal">
       InnoDB
      </code>
      (and
          you do not change the table's storage engine), or if the
      <code class="literal">
       ALTER 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>
      Altering table encryption by moving a table to a different
          tablespace or changing the storage engine is not permitted
          without explicitly specifying an
      <code class="literal">
       ENCRYPTION
      </code>
      clause.
     </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 if the table uses a
          storage engine that does not support encryption. Attempting to
          create a table without an
      <code class="literal">
       ENCRYPTION
      </code>
      clause
          in an encryption-enabled schema using a storage engine that
          does not support encryption is also not permitted.
     </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>
      To reset the current auto-increment value:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa50932411"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">AUTO_INCREMENT</span> <span class="token operator">=</span> <span class="token number">13</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      You cannot reset the counter to a value less than or equal to
          the value that is currently in use. For both
      <code class="literal">
       InnoDB
      </code>
      and
      <code class="literal">
       MyISAM
      </code>
      , if
          the value is less than or equal to the maximum value currently
          in the
      <code class="literal">
       AUTO_INCREMENT
      </code>
      column, the value is
          reset to the current maximum
      <code class="literal">
       AUTO_INCREMENT
      </code>
      column value plus one.
     </p>
    </li>
    <li class="listitem">
     <p>
      To change the default table character set:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa77478368"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">CHARACTER</span> <span class="token keyword">SET</span> <span class="token operator">=</span> utf8mb4<span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      See also
      <a class="xref" href="alter-table.html#alter-table-character-set" title="Changing the Character Set">
       Changing the Character Set
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      To add (or change) a table comment:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa1024643"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">COMMENT</span> <span class="token operator">=</span> <span class="token string">'New table comment'</span><span class="token punctuation">;</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      Use
      <code class="literal">
       ALTER TABLE
      </code>
      with the
      <code class="literal">
       TABLESPACE
      </code>
      option to move
      <code class="literal">
       InnoDB
      </code>
      tables between existing
      <a class="link" href="glossary.html#glos_general_tablespace" title="general tablespace">
       general
          tablespaces
      </a>
      ,
      <a class="link" href="glossary.html#glos_file_per_table" title="file-per-table">
       file-per-table
      </a>
      tablespaces, and the
      <a class="link" href="glossary.html#glos_system_tablespace" title="system tablespace">
       system
          tablespace
      </a>
      . See
      <a class="xref" href="general-tablespaces.html#general-tablespaces-moving-non-partitioned-tables" title="Moving Tables Between Tablespaces Using ALTER TABLE">
       Moving Tables Between Tablespaces Using ALTER TABLE
      </a>
      .
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         <code class="literal">
          ALTER TABLE ... TABLESPACE
         </code>
         operations
              always cause a full table rebuild, even if the
         <code class="literal">
          TABLESPACE
         </code>
         attribute has not changed
              from its previous value.
        </p>
       </li>
       <li class="listitem">
        <p>
         <code class="literal">
          ALTER TABLE ... TABLESPACE
         </code>
         syntax does
              not support moving a table from a temporary tablespace to
              a persistent tablespace.
        </p>
       </li>
       <li class="listitem">
        <p>
         The
         <code class="literal">
          DATA DIRECTORY
         </code>
         clause, which is
              supported with
         <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
          <code class="literal">
           CREATE TABLE
              ... TABLESPACE
          </code>
         </a>
         , is not supported with
         <code class="literal">
          ALTER TABLE ... TABLESPACE
         </code>
         , and is
              ignored if specified.
        </p>
       </li>
       <li class="listitem">
        <p>
         For more information about the capabilities and
              limitations of the
         <code class="literal">
          TABLESPACE
         </code>
         option,
              see
         <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
          <code class="literal">
           CREATE TABLE
          </code>
         </a>
         .
        </p>
       </li>
      </ul>
     </div>
    </li>
    <li class="listitem">
     <p>
      MySQL NDB Cluster 8.4 supports setting
      <code class="literal">
       NDB_TABLE
      </code>
      options for controlling a
          table's partition balance (fragment count type),
          read-from-any-replica capability, full replication, or any
          combination of these, as part of the table comment for an
      <code class="literal">
       ALTER TABLE
      </code>
      statement in the same manner as
          for
      <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
       <code class="literal">
        CREATE TABLE
       </code>
      </a>
      , as shown in
          this example:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa47427485"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">COMMENT</span> <span class="token operator">=</span> <span class="token string">"NDB_TABLE=READ_BACKUP=0,PARTITION_BALANCE=FOR_RA_BY_NODE"</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      It is also possible to set
      <code class="literal">
       NDB_COMMENT
      </code>
      options for columns of
      <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
       <code class="literal">
        NDB
       </code>
      </a>
      tables
          as part of an
      <code class="literal">
       ALTER TABLE
      </code>
      statement, like
          this one:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa79404315"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 
  <span class="token keyword">CHANGE</span> <span class="token keyword">COLUMN</span> c1 c1 <span class="token datatype">BLOB</span> 
    <span class="token keyword">COMMENT</span> <span class="token operator">=</span> <span class="token string">'NDB_COLUMN=BLOB_INLINE_SIZE=4096,MAX_BLOB_PART_SIZE'</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      Bear in mind that
      <code class="literal">
       ALTER TABLE ... COMMENT
          ...
      </code>
      discards any existing comment for the table. See
      <a class="xref" href="create-table.html#create-table-comment-ndb-table-options" title="Setting NDB_TABLE options">
       Setting NDB_TABLE options
      </a>
      , for
          additional information and examples.
     </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 table, column, and index attributes for primary and
          secondary storage engines. These options are reserved for
          future use. Index attributes cannot be altered. An index must
          be dropped and added back with the desired change, which can
          be performed in a single
      <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
       <code class="literal">
        ALTER
          TABLE
       </code>
      </a>
      statement.
     </p>
    </li>
   </ul>
  </div>
  <p>
   To verify that the table options were changed as intended, use
   <a class="link" href="show-create-table.html" title="15.7.7.11 SHOW CREATE TABLE Statement">
    <code class="literal">
     SHOW CREATE TABLE
    </code>
   </a>
   , or query the
      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>
  <h4>
   <a name="alter-table-performance">
   </a>
   Performance and Space Requirements
  </h4>
  <p>
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
    </code>
   </a>
   operations are
      processed using one of the following algorithms:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       COPY
      </code>
      : Operations are performed on a copy of
          the original table, and table data is copied from the original
          table to the new table row by row. Concurrent DML is not
          permitted.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       INPLACE
      </code>
      : Operations avoid copying table
          data but may rebuild the table in place. An exclusive metadata
          lock on the table may be taken briefly during preparation and
          execution phases of the operation. Typically, concurrent DML
          is supported.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       INSTANT
      </code>
      : Operations only modify metadata in
          the data dictionary. An exclusive metadata lock on the table
          may be taken briefly during the execution phase of the
          operation. Table data is unaffected, making operations
          instantaneous. Concurrent DML is permitted.
     </p>
    </li>
   </ul>
  </div>
  <p>
   For tables using the
   <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
    <code class="literal">
     NDB
    </code>
   </a>
   storage
      engine, these algorithms work as follows:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       COPY
      </code>
      :
      <code class="literal">
       NDB
      </code>
      creates a copy
          of the table and alters it; the NDB Cluster handler then
          copies the data between the old and new versions of the table.
          Subsequently,
      <code class="literal">
       NDB
      </code>
      deletes the old table and
          renames the new one.
     </p>
     <p>
      This is sometimes also referred to as a
      <span class="quote">
       “
       <span class="quote">
        copying
       </span>
       ”
      </span>
      or
      <span class="quote">
       “
       <span class="quote">
        offline
       </span>
       ”
      </span>
      <code class="literal">
       ALTER TABLE
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       INPLACE
      </code>
      : The data nodes make the required
          changes; the NDB Cluster handler does not copy data or
          otherwise take part.
     </p>
     <p>
      This is sometimes also referred to as a
      <span class="quote">
       “
       <span class="quote">
        non-copying
       </span>
       ”
      </span>
      or
      <span class="quote">
       “
       <span class="quote">
        online
       </span>
       ”
      </span>
      <code class="literal">
       ALTER TABLE
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       INSTANT
      </code>
      : Not supported by
      <code class="literal">
       NDB
      </code>
      .
     </p>
    </li>
   </ul>
  </div>
  <p>
   See
   <a class="xref" href="mysql-cluster-online-operations.html" title="25.6.12 Online Operations with ALTER TABLE in NDB Cluster">
    Section 25.6.12, “Online Operations with ALTER TABLE in NDB Cluster”
   </a>
   , for more
      information.
  </p>
  <p>
   The
   <code class="literal">
    ALGORITHM
   </code>
   clause is optional. If the
   <code class="literal">
    ALGORITHM
   </code>
   clause is omitted, MySQL uses
   <code class="literal">
    ALGORITHM=INSTANT
   </code>
   for storage engines and
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
    </code>
   </a>
   clauses that support
      it. Otherwise,
   <code class="literal">
    ALGORITHM=INPLACE
   </code>
   is used. If
   <code class="literal">
    ALGORITHM=INPLACE
   </code>
   is not supported,
   <code class="literal">
    ALGORITHM=COPY
   </code>
   is used.
  </p>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    After adding a column to a partitioned table using
    <code class="literal">
     ALGORITHM=INSTANT
    </code>
    , it is no longer possible
        to perform
    <a class="link" href="alter-table-partition-operations.html" title="15.1.9.1 ALTER TABLE Partition Operations">
     <code class="literal">
      ALTER
        TABLE ... EXCHANGE PARTITION
     </code>
    </a>
    on the table.
   </p>
  </div>
  <p>
   Specifying an
   <code class="literal">
    ALGORITHM
   </code>
   clause requires the
      operation to use the specified algorithm for clauses and storage
      engines that support it, or fail with an error otherwise.
      Specifying
   <code class="literal">
    ALGORITHM=DEFAULT
   </code>
   is the same as
      omitting the
   <code class="literal">
    ALGORITHM
   </code>
   clause.
  </p>
  <p>
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
    </code>
   </a>
   operations that use the
   <code class="literal">
    COPY
   </code>
   algorithm wait for other operations that
      are modifying the table to complete. After alterations are applied
      to the table copy, data is copied over, the original table is
      deleted, and the table copy is renamed to the name of the original
      table. While the
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
    </code>
   </a>
   operation executes, the original table is readable by other
      sessions (with the exception noted shortly). Updates and writes to
      the table started after the
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER
      TABLE
    </code>
   </a>
   operation begins are stalled until the new table
      is ready, then are automatically redirected to the new table. The
      temporary copy of the table is created in the database directory
      of the original table unless it is a
   <code class="literal">
    RENAME TO
   </code>
   operation that moves the table to a database that resides in a
      different directory.
  </p>
  <p>
   The exception referred to earlier is that
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
    </code>
   </a>
   blocks reads (not just
      writes) at the point where it is ready to clear outdated table
      structures from the table and table definition caches. At this
      point, it must acquire an exclusive lock. To do so, it waits for
      current readers to finish, and blocks new reads and writes.
  </p>
  <p>
   An
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
    </code>
   </a>
   operation that uses
      the
   <code class="literal">
    COPY
   </code>
   algorithm prevents concurrent DML
      operations. Concurrent queries are still allowed. That is, a
      table-copying operation always includes at least the concurrency
      restrictions of
   <code class="literal">
    LOCK=SHARED
   </code>
   (allow queries but
      not DML). You can further restrict concurrency for operations that
      support the
   <code class="literal">
    LOCK
   </code>
   clause by specifying
   <code class="literal">
    LOCK=EXCLUSIVE
   </code>
   , which prevents DML and queries.
      For more information, see
   <a class="xref" href="alter-table.html#alter-table-concurrency" title="Concurrency Control">
    Concurrency Control
   </a>
   .
  </p>
  <p>
   To force use of the
   <code class="literal">
    COPY
   </code>
   algorithm for an
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
    </code>
   </a>
   operation that would
      otherwise not use it, specify
   <code class="literal">
    ALGORITHM=COPY
   </code>
   or
      enable the
   <a class="link" href="server-system-variables.html#sysvar_old_alter_table">
    <code class="literal">
     old_alter_table
    </code>
   </a>
   system
      variable. If there is a conflict between the
   <code class="literal">
    old_alter_table
   </code>
   setting and an
   <code class="literal">
    ALGORITHM
   </code>
   clause with a value other than
   <code class="literal">
    DEFAULT
   </code>
   , the
   <code class="literal">
    ALGORITHM
   </code>
   clause takes precedence.
  </p>
  <p>
   For
   <code class="literal">
    InnoDB
   </code>
   tables, an
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
    </code>
   </a>
   operation that uses the
   <code class="literal">
    COPY
   </code>
   algorithm on a table that resides in a
   <a class="link" href="glossary.html#glos_shared_tablespace" title="shared tablespace">
    shared tablespace
   </a>
   can increase the amount of space used by the tablespace. Such
      operations require as much additional space as the data in the
      table plus indexes. For a table residing in a shared tablespace,
      the additional space used during the operation is not released
      back to the operating system as it is for a table that resides in
      a
   <a class="link" href="glossary.html#glos_file_per_table" title="file-per-table">
    file-per-table
   </a>
   tablespace.
  </p>
  <p>
   For information about space requirements for online DDL
      operations, see
   <a class="xref" href="innodb-online-ddl-space-requirements.html" title="17.12.3 Online DDL Space Requirements">
    Section 17.12.3, “Online DDL Space Requirements”
   </a>
   .
  </p>
  <p>
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
    </code>
   </a>
   operations that support
      the
   <code class="literal">
    INPLACE
   </code>
   algorithm include:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       ALTER TABLE
      </code>
      operations supported by the
      <code class="literal">
       InnoDB
      </code>
      <a class="link" href="glossary.html#glos_online_ddl" title="online DDL">
       online DDL
      </a>
      feature. See
      <a class="xref" href="innodb-online-ddl-operations.html" title="17.12.1 Online DDL Operations">
       Section 17.12.1, “Online DDL Operations”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      Renaming a table. MySQL renames files that correspond to the
          table
      <em class="replaceable">
       <code>
        tbl_name
       </code>
      </em>
      without making a
          copy. (You can also use the
      <a class="link" href="rename-table.html" title="15.1.36 RENAME TABLE Statement">
       <code class="literal">
        RENAME
          TABLE
       </code>
      </a>
      statement to rename tables. See
      <a class="xref" href="rename-table.html" title="15.1.36 RENAME TABLE Statement">
       Section 15.1.36, “RENAME TABLE Statement”
      </a>
      .) Privileges granted
          specifically for the renamed table are not migrated to the new
          name. They must be changed manually.
     </p>
    </li>
    <li class="listitem">
     <p>
      Operations that modify table metadata only. These operations
          are immediate because the server does not touch table
          contents. Metadata-only operations include:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         Renaming a column. In NDB Cluster, this operation can also
              be performed online.
        </p>
       </li>
       <li class="listitem">
        <p>
         Changing the default value of a column (except for
         <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
          <code class="literal">
           NDB
          </code>
         </a>
         tables).
        </p>
       </li>
       <li class="listitem">
        <p>
         Modifying the definition of an
         <a class="link" href="enum.html" title="13.3.5 The ENUM Type">
          <code class="literal">
           ENUM
          </code>
         </a>
         or
         <a class="link" href="set.html" title="13.3.6 The SET Type">
          <code class="literal">
           SET
          </code>
         </a>
         column by adding new
              enumeration or set members to the
         <span class="emphasis">
          <em>
           end
          </em>
         </span>
         of the list of valid member values, as long as the storage
              size of the data type does not change. For example, adding
              a member to a
         <a class="link" href="set.html" title="13.3.6 The SET Type">
          <code class="literal">
           SET
          </code>
         </a>
         column
              that has 8 members changes the required storage per value
              from 1 byte to 2 bytes; this requires a table copy. Adding
              members in the middle of the list causes renumbering of
              existing members, which requires a table copy.
        </p>
       </li>
       <li class="listitem">
        <p>
         Changing the definition of a spatial column to remove the
         <code class="literal">
          SRID
         </code>
         attribute. (Adding or changing an
         <code class="literal">
          SRID
         </code>
         attribute requires a rebuild, and
              cannot be done in place, because the server must verify
              that all values have the specified
         <code class="literal">
          SRID
         </code>
         value.)
        </p>
       </li>
       <li class="listitem">
        <p>
         Changing a column character set, when these conditions
              apply:
        </p>
        <div class="itemizedlist">
         <ul class="itemizedlist" style="list-style-type: square; ">
          <li class="listitem">
           <p>
            The column data type is
            <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
            <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
             <code class="literal">
              TEXT
             </code>
            </a>
            type, or
            <a class="link" href="enum.html" title="13.3.5 The ENUM Type">
             <code class="literal">
              ENUM
             </code>
            </a>
            .
           </p>
          </li>
          <li class="listitem">
           <p>
            The character set change is from
            <code class="literal">
             utf8mb3
            </code>
            to
            <code class="literal">
             utf8mb4
            </code>
            , or any character set to
            <code class="literal">
             binary
            </code>
            .
           </p>
          </li>
          <li class="listitem">
           <p>
            There is no index on the column.
           </p>
          </li>
         </ul>
        </div>
       </li>
       <li class="listitem">
        <p>
         Changing a generated column, when these conditions apply:
        </p>
        <div class="itemizedlist">
         <ul class="itemizedlist" style="list-style-type: square; ">
          <li class="listitem">
           <p>
            For
            <code class="literal">
             InnoDB
            </code>
            tables, statements that
                  modify generated stored columns but do not change
                  their type, expression, or nullability.
           </p>
          </li>
          <li class="listitem">
           <p>
            For non-
            <code class="literal">
             InnoDB
            </code>
            tables, statements
                  that modify generated stored or virtual columns but do
                  not change their type, expression, or nullability.
           </p>
          </li>
         </ul>
        </div>
        <p>
         An example of such a change is a change to the column
              comment.
        </p>
       </li>
      </ul>
     </div>
    </li>
    <li class="listitem">
     <p>
      Renaming an index.
     </p>
    </li>
    <li class="listitem">
     <p>
      Adding or dropping a secondary index, for
      <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. See
      <a class="xref" href="innodb-online-ddl-operations.html" title="17.12.1 Online DDL Operations">
       Section 17.12.1, “Online DDL Operations”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      For
      <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
       <code class="literal">
        NDB
       </code>
      </a>
      tables, operations that
          add and drop indexes on variable-width columns. These
          operations occur online, without table copying and without
          blocking concurrent DML actions for most of their duration.
          See
      <a class="xref" href="mysql-cluster-online-operations.html" title="25.6.12 Online Operations with ALTER TABLE in NDB Cluster">
       Section 25.6.12, “Online Operations with ALTER TABLE in NDB Cluster”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      Modifying index visibility with an
      <code class="literal">
       ALTER
          INDEX
      </code>
      operation.
     </p>
    </li>
    <li class="listitem">
     <p>
      Column modifications of tables containing generated columns
          that depend on columns with a
      <code class="literal">
       DEFAULT
      </code>
      value
          if the modified columns are not involved in the generated
          column expressions. For example, changing the
      <code class="literal">
       NULL
      </code>
      property of a separate column can be
          done in place without a table rebuild.
     </p>
    </li>
   </ul>
  </div>
  <p>
   <code class="literal">
    ALTER TABLE
   </code>
   operations that support the
   <code class="literal">
    INSTANT
   </code>
   algorithm include:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Adding a column. This feature is referred to as
      <span class="quote">
       “
       <span class="quote">
        Instant
        <code class="literal">
         ADD COLUMN
        </code>
       </span>
       ”
      </span>
      . Limitations apply. See
      <a class="xref" href="innodb-online-ddl-operations.html" title="17.12.1 Online DDL Operations">
       Section 17.12.1, “Online DDL Operations”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      Dropping a column. This feature is referred to as
      <span class="quote">
       “
       <span class="quote">
        Instant
        <code class="literal">
         DROP COLUMN
        </code>
       </span>
       ”
      </span>
      .
          Limitations apply. See
      <a class="xref" href="innodb-online-ddl-operations.html" title="17.12.1 Online DDL Operations">
       Section 17.12.1, “Online DDL Operations”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      Adding or dropping a virtual column.
     </p>
    </li>
    <li class="listitem">
     <p>
      Adding or dropping a column default value.
     </p>
    </li>
    <li class="listitem">
     <p>
      Modifying the definition of an
      <a class="link" href="enum.html" title="13.3.5 The ENUM Type">
       <code class="literal">
        ENUM
       </code>
      </a>
      or
      <a class="link" href="set.html" title="13.3.6 The SET Type">
       <code class="literal">
        SET
       </code>
      </a>
      column. The same
          restrictions apply as described above for
      <code class="literal">
       ALGORITHM=INSTANT
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      Changing the index type.
     </p>
    </li>
    <li class="listitem">
     <p>
      Renaming a table. The same restrictions apply as described
          above for
      <code class="literal">
       ALGORITHM=INSTANT
      </code>
      .
     </p>
    </li>
   </ul>
  </div>
  <p>
   For more information about operations that support
   <code class="literal">
    ALGORITHM=INSTANT
   </code>
   , see
   <a class="xref" href="innodb-online-ddl-operations.html" title="17.12.1 Online DDL Operations">
    Section 17.12.1, “Online DDL Operations”
   </a>
   .
  </p>
  <p>
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
    </code>
   </a>
   upgrades MySQL 5.5
      temporal columns to 5.6 format for
   <code class="literal">
    ADD COLUMN
   </code>
   ,
   <code class="literal">
    CHANGE COLUMN
   </code>
   ,
   <code class="literal">
    MODIFY
      COLUMN
   </code>
   ,
   <code class="literal">
    ADD INDEX
   </code>
   , and
   <code class="literal">
    FORCE
   </code>
   operations. This conversion cannot be
      done using the
   <code class="literal">
    INPLACE
   </code>
   algorithm because the
      table must be rebuilt, so specifying
   <code class="literal">
    ALGORITHM=INPLACE
   </code>
   in these cases results in an
      error. Specify
   <code class="literal">
    ALGORITHM=COPY
   </code>
   if necessary.
  </p>
  <p>
   If an
   <code class="literal">
    ALTER TABLE
   </code>
   operation on a multicolumn
      index used to partition a table by
   <code class="literal">
    KEY
   </code>
   changes
      the order of the columns, it can only be performed using
   <code class="literal">
    ALGORITHM=COPY
   </code>
   .
  </p>
  <p>
   The
   <code class="literal">
    WITHOUT VALIDATION
   </code>
   and
   <code class="literal">
    WITH
      VALIDATION
   </code>
   clauses affect whether
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
    </code>
   </a>
   performs an in-place
      operation for
   <a class="link" href="glossary.html#glos_virtual_generated_column" title="virtual generated column">
    virtual generated
      column
   </a>
   modifications. See
   <a class="xref" href="alter-table-generated-columns.html" title="15.1.9.2 ALTER TABLE and Generated Columns">
    Section 15.1.9.2, “ALTER TABLE and Generated Columns”
   </a>
   .
  </p>
  <p>
   NDB Cluster 8.4 supports online operations using the
      same
   <code class="literal">
    ALGORITHM=INPLACE
   </code>
   syntax used with the
      standard MySQL Server.
   <code class="literal">
    NDB
   </code>
   does not allow
      changing a tablespace online. See
   <a class="xref" href="mysql-cluster-online-operations.html" title="25.6.12 Online Operations with ALTER TABLE in NDB Cluster">
    Section 25.6.12, “Online Operations with ALTER TABLE in NDB Cluster”
   </a>
   , for more
      information.
  </p>
  <p>
   When performing a copying
   <code class="literal">
    ALTER TABLE
   </code>
   ,
   <code class="literal">
    NDB
   </code>
   checks to ensure that no concurrent writes
      have been made to the affected table. If it finds that any have
      been made,
   <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
    <code class="literal">
     NDB
    </code>
   </a>
   rejects the
   <code class="literal">
    ALTER TABLE
   </code>
   statement and raises
   <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_table_def_changed" target="_top">
    <code class="literal">
     ER_TABLE_DEF_CHANGED
    </code>
   </a>
   .
  </p>
  <p>
   <code class="literal">
    ALTER TABLE
   </code>
   with
   <code class="literal">
    DISCARD ... PARTITION
      ... TABLESPACE
   </code>
   or
   <code class="literal">
    IMPORT ... PARTITION ...
      TABLESPACE
   </code>
   does not create any temporary tables or
      temporary partition files.
  </p>
  <p>
   <code class="literal">
    ALTER TABLE
   </code>
   with
   <code class="literal">
    ADD
      PARTITION
   </code>
   ,
   <code class="literal">
    DROP PARTITION
   </code>
   ,
   <code class="literal">
    COALESCE PARTITION
   </code>
   ,
   <code class="literal">
    REBUILD
      PARTITION
   </code>
   , or
   <code class="literal">
    REORGANIZE PARTITION
   </code>
   does not create temporary tables (except when used with
   <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
    <code class="literal">
     NDB
    </code>
   </a>
   tables); however, these
      operations can and do create temporary partition files.
  </p>
  <p>
   <code class="literal">
    ADD
   </code>
   or
   <code class="literal">
    DROP
   </code>
   operations for
   <code class="literal">
    RANGE
   </code>
   or
   <code class="literal">
    LIST
   </code>
   partitions are
      immediate operations or nearly so.
   <code class="literal">
    ADD
   </code>
   or
   <code class="literal">
    COALESCE
   </code>
   operations for
   <code class="literal">
    HASH
   </code>
   or
   <code class="literal">
    KEY
   </code>
   partitions copy data between all
      partitions, unless
   <code class="literal">
    LINEAR HASH
   </code>
   or
   <code class="literal">
    LINEAR KEY
   </code>
   was used; this is effectively the
      same as creating a new table, although the
   <code class="literal">
    ADD
   </code>
   or
   <code class="literal">
    COALESCE
   </code>
   operation is performed partition by
      partition.
   <code class="literal">
    REORGANIZE
   </code>
   operations copy only
      changed partitions and do not touch unchanged ones.
  </p>
  <p>
   For
   <code class="literal">
    MyISAM
   </code>
   tables, you can speed up index
      re-creation (the slowest part of the alteration process) by
      setting the
   <a class="link" href="server-system-variables.html#sysvar_myisam_sort_buffer_size">
    <code class="literal">
     myisam_sort_buffer_size
    </code>
   </a>
   system
      variable to a high value.
  </p>
  <h4>
   <a name="alter-table-concurrency">
   </a>
   Concurrency Control
  </h4>
  <p>
   For
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
    </code>
   </a>
   operations that
      support it, you can use the
   <code class="literal">
    LOCK
   </code>
   clause to
      control the level of concurrent reads and writes on a table while
      it is being altered. Specifying a non-default value for this
      clause enables you to require a certain amount of concurrent
      access or exclusivity during the alter operation, and halts the
      operation if the requested degree of locking is not available.
  </p>
  <p>
   Only
   <code class="literal">
    LOCK = DEFAULT
   </code>
   is permitted for operations
      that use
   <code class="literal">
    ALGORITHM=INSTANT
   </code>
   . The other
   <code class="literal">
    LOCK
   </code>
   clause parameters are not applicable.
  </p>
  <p>
   The parameters for the
   <code class="literal">
    LOCK
   </code>
   clause are:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       LOCK = DEFAULT
      </code>
     </p>
     <p>
      Maximum level of concurrency for the given
      <code class="literal">
       ALGORITHM
      </code>
      clause (if any) and
      <code class="literal">
       ALTER TABLE
      </code>
      operation: Permit concurrent
          reads and writes if supported. If not, permit concurrent reads
          if supported. If not, enforce exclusive access.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       LOCK = NONE
      </code>
     </p>
     <p>
      If supported, permit concurrent reads and writes. Otherwise,
          an error occurs.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       LOCK = SHARED
      </code>
     </p>
     <p>
      If supported, permit concurrent reads but block writes. Writes
          are blocked even if concurrent writes are supported by the
          storage engine for the given
      <code class="literal">
       ALGORITHM
      </code>
      clause (if any) and
      <code class="literal">
       ALTER TABLE
      </code>
      operation.
          If concurrent reads are not supported, an error occurs.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       LOCK = EXCLUSIVE
      </code>
     </p>
     <p>
      Enforce exclusive access. This is done even if concurrent
          reads/writes are supported by the storage engine for the given
      <code class="literal">
       ALGORITHM
      </code>
      clause (if any) and
      <code class="literal">
       ALTER TABLE
      </code>
      operation.
     </p>
    </li>
   </ul>
  </div>
  <h4>
   <a name="alter-table-add-drop-column">
   </a>
   Adding and Dropping Columns
  </h4>
  <p>
   Use
   <code class="literal">
    ADD
   </code>
   to add new columns to a table, and
   <code class="literal">
    DROP
   </code>
   to remove existing columns.
   <code class="literal">
    DROP
    <em class="replaceable">
     <code>
      col_name
     </code>
    </em>
   </code>
   is a MySQL extension
      to standard SQL.
  </p>
  <p>
   To add a column at a specific position within a table row, use
   <code class="literal">
    FIRST
   </code>
   or
   <code class="literal">
    AFTER
    <em class="replaceable">
     <code>
      col_name
     </code>
    </em>
   </code>
   . The default is to
      add the column last.
  </p>
  <p>
   If a table contains only one column, the column cannot be dropped.
      If what you intend is to remove the table, use the
   <a class="link" href="drop-table.html" title="15.1.32 DROP TABLE Statement">
    <code class="literal">
     DROP TABLE
    </code>
   </a>
   statement instead.
  </p>
  <p>
   If columns are dropped from a table, the columns are also removed
      from any index of which they are a part. If all columns that make
      up an index are dropped, the index is dropped as well. If you use
   <code class="literal">
    CHANGE
   </code>
   or
   <code class="literal">
    MODIFY
   </code>
   to shorten
      a column for which an index exists on the column, and the
      resulting column length is less than the index length, MySQL
      shortens the index automatically.
  </p>
  <p>
   For
   <code class="literal">
    ALTER TABLE ... ADD
   </code>
   , if the column has an
      expression default value that uses a nondeterministic function,
      the statement may produce a warning or error. For further
      information, see
   <a class="xref" href="data-type-defaults.html" title="13.6 Data Type Default Values">
    Section 13.6, “Data Type Default Values”
   </a>
   , and
   <a class="xref" href="replication-gtids-restrictions.html" title="19.1.3.7 Restrictions on Replication with GTIDs">
    Section 19.1.3.7, “Restrictions on Replication with GTIDs”
   </a>
   .
  </p>
  <h4>
   <a name="alter-table-redefine-column">
   </a>
   Renaming, Redefining, and Reordering Columns
  </h4>
  <a class="indexterm" name="idm46045189128768">
  </a>
  <a class="indexterm" name="idm46045189127280">
  </a>
  <a class="indexterm" name="idm46045189125792">
  </a>
  <a class="indexterm" name="idm46045189124304">
  </a>
  <p>
   The
   <code class="literal">
    CHANGE
   </code>
   ,
   <code class="literal">
    MODIFY
   </code>
   ,
   <code class="literal">
    RENAME COLUMN
   </code>
   , and
   <code class="literal">
    ALTER
   </code>
   clauses enable the names and definitions of existing columns to be
      altered. They have these comparative characteristics:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       CHANGE
      </code>
      :
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         Can rename a column and change its definition, or both.
        </p>
       </li>
       <li class="listitem">
        <p>
         Has more capability than
         <code class="literal">
          MODIFY
         </code>
         or
         <code class="literal">
          RENAME COLUMN
         </code>
         , but at the expense of
              convenience for some operations.
         <code class="literal">
          CHANGE
         </code>
         requires naming the column twice if not renaming it, and
              requires respecifying the column definition if only
              renaming it.
        </p>
       </li>
       <li class="listitem">
        <p>
         With
         <code class="literal">
          FIRST
         </code>
         or
         <code class="literal">
          AFTER
         </code>
         ,
              can reorder columns.
        </p>
       </li>
      </ul>
     </div>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       MODIFY
      </code>
      :
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         Can change a column definition but not its name.
        </p>
       </li>
       <li class="listitem">
        <p>
         More convenient than
         <code class="literal">
          CHANGE
         </code>
         to change a
              column definition without renaming it.
        </p>
       </li>
       <li class="listitem">
        <p>
         With
         <code class="literal">
          FIRST
         </code>
         or
         <code class="literal">
          AFTER
         </code>
         ,
              can reorder columns.
        </p>
       </li>
      </ul>
     </div>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       RENAME COLUMN
      </code>
      :
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         Can change a column name but not its definition.
        </p>
       </li>
       <li class="listitem">
        <p>
         More convenient than
         <code class="literal">
          CHANGE
         </code>
         to rename a
              column without changing its definition.
        </p>
       </li>
      </ul>
     </div>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       ALTER
      </code>
      : Used only to change a column default
          value.
     </p>
    </li>
   </ul>
  </div>
  <a class="indexterm" name="idm46045189098400">
  </a>
  <a class="indexterm" name="idm46045189097328">
  </a>
  <p>
   <code class="literal">
    CHANGE
   </code>
   is a MySQL extension to standard SQL.
   <code class="literal">
    MODIFY
   </code>
   and
   <code class="literal">
    RENAME COLUMN
   </code>
   are
      MySQL extensions for Oracle compatibility.
  </p>
  <p>
   To alter a column to change both its name and definition, use
   <code class="literal">
    CHANGE
   </code>
   , specifying the old and new names and
      the new definition. For example, to rename an
   <code class="literal">
    INT NOT
      NULL
   </code>
   column from
   <code class="literal">
    a
   </code>
   to
   <code class="literal">
    b
   </code>
   and change its definition to use the
   <code class="literal">
    BIGINT
   </code>
   data type while retaining the
   <code class="literal">
    NOT NULL
   </code>
   attribute, do this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa62279787"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">CHANGE</span> a b <span class="token datatype">BIGINT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   To change a column definition but not its name, use
   <code class="literal">
    CHANGE
   </code>
   or
   <code class="literal">
    MODIFY
   </code>
   . With
   <code class="literal">
    CHANGE
   </code>
   , the syntax requires two column names,
      so you must specify the same name twice to leave the name
      unchanged. For example, to change the definition of column
   <code class="literal">
    b
   </code>
   , do this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa15469092"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">CHANGE</span> b b <span class="token datatype">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   <code class="literal">
    MODIFY
   </code>
   is more convenient to change the
      definition without changing the name because it requires the
      column name only once:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa33906722"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">MODIFY</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></code></pre>
  </div>
  <p>
   To change a column name but not its definition, use
   <code class="literal">
    CHANGE
   </code>
   or
   <code class="literal">
    RENAME COLUMN
   </code>
   .
      With
   <code class="literal">
    CHANGE
   </code>
   , the syntax requires a column
      definition, so to leave the definition unchanged, you must
      respecify the definition the column currently has. For example, to
      rename an
   <code class="literal">
    INT NOT NULL
   </code>
   column from
   <code class="literal">
    b
   </code>
   to
   <code class="literal">
    a
   </code>
   , do this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa91860529"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">CHANGE</span> b a <span class="token datatype">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   <code class="literal">
    RENAME COLUMN
   </code>
   is more convenient to change the
      name without changing the definition because it requires only the
      old and new names:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa3473577"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">RENAME</span> <span class="token keyword">COLUMN</span> b <span class="token keyword">TO</span> a<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   In general, you cannot rename a column to a name that already
      exists in the table. However, this is sometimes not the case, such
      as when you swap names or move them through a cycle. If a table
      has columns named
   <code class="literal">
    a
   </code>
   ,
   <code class="literal">
    b
   </code>
   , and
   <code class="literal">
    c
   </code>
   , these are valid operations:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa58698386"><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 comment" spellcheck="true">-- swap a and b</span>
<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">RENAME</span> <span class="token keyword">COLUMN</span> a <span class="token keyword">TO</span> b<span class="token punctuation">,</span>
               <span class="token keyword">RENAME</span> <span class="token keyword">COLUMN</span> b <span class="token keyword">TO</span> a<span class="token punctuation">;</span>
<span class="token comment" spellcheck="true">-- "rotate" a, b, c through a cycle</span>
<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">RENAME</span> <span class="token keyword">COLUMN</span> a <span class="token keyword">TO</span> b<span class="token punctuation">,</span>
               <span class="token keyword">RENAME</span> <span class="token keyword">COLUMN</span> b <span class="token keyword">TO</span> c<span class="token punctuation">,</span>
               <span class="token keyword">RENAME</span> <span class="token keyword">COLUMN</span> c <span class="token keyword">TO</span> a<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   For column definition changes using
   <code class="literal">
    CHANGE
   </code>
   or
   <code class="literal">
    MODIFY
   </code>
   , the definition must include the data
      type and all attributes that should apply to the new column, other
      than index attributes such as
   <code class="literal">
    PRIMARY KEY
   </code>
   or
   <code class="literal">
    UNIQUE
   </code>
   . Attributes present in the original
      definition but not specified for the new definition are not
      carried forward. Suppose that a column
   <code class="literal">
    col1
   </code>
   is
      defined as
   <code class="literal">
    INT UNSIGNED DEFAULT 1 COMMENT 'my
      column'
   </code>
   and you modify the column as follows, intending
      to change only
   <code class="literal">
    INT
   </code>
   to
   <code class="literal">
    BIGINT
   </code>
   :
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa36479411"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">MODIFY</span> col1 <span class="token datatype">BIGINT</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   That statement changes the data type from
   <code class="literal">
    INT
   </code>
   to
   <code class="literal">
    BIGINT
   </code>
   , but it also drops the
   <code class="literal">
    UNSIGNED
   </code>
   ,
   <code class="literal">
    DEFAULT
   </code>
   , and
   <code class="literal">
    COMMENT
   </code>
   attributes. To retain them, the
      statement must include them explicitly:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa94917873"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">MODIFY</span> col1 <span class="token datatype">BIGINT</span> <span class="token keyword">UNSIGNED</span> <span class="token keyword">DEFAULT</span> <span class="token number">1</span> <span class="token keyword">COMMENT</span> <span class="token string">'my column'</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   For data type changes using
   <code class="literal">
    CHANGE
   </code>
   or
   <code class="literal">
    MODIFY
   </code>
   , MySQL tries to convert existing column
      values to the new type as well as possible.
  </p>
  <div class="warning" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Warning
   </div>
   <p>
    This conversion may result in alteration of data. For example,
        if you shorten a string column, values may be truncated. To
        prevent the operation from succeeding if conversions to the new
        data type would result in loss of data, enable strict SQL mode
        before using
    <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
     <code class="literal">
      ALTER TABLE
     </code>
    </a>
    (see
    <a class="xref" href="sql-mode.html" title="7.1.11 Server SQL Modes">
     Section 7.1.11, “Server SQL Modes”
    </a>
    ).
   </p>
  </div>
  <p>
   If you use
   <code class="literal">
    CHANGE
   </code>
   or
   <code class="literal">
    MODIFY
   </code>
   to shorten a column for which an index exists on the column, and
      the resulting column length is less than the index length, MySQL
      shortens the index automatically.
  </p>
  <p>
   For columns renamed by
   <code class="literal">
    CHANGE
   </code>
   or
   <code class="literal">
    RENAME COLUMN
   </code>
   , MySQL automatically renames
      these references to the renamed column:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Indexes that refer to the old column, including invisible
          indexes and disabled
      <code class="literal">
       MyISAM
      </code>
      indexes.
     </p>
    </li>
    <li class="listitem">
     <p>
      Foreign keys that refer to the old column.
     </p>
    </li>
   </ul>
  </div>
  <p>
   For columns renamed by
   <code class="literal">
    CHANGE
   </code>
   or
   <code class="literal">
    RENAME COLUMN
   </code>
   , MySQL does not automatically
      rename these references to the renamed column:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Generated column and partition expressions that refer to the
          renamed column. You must use
      <code class="literal">
       CHANGE
      </code>
      to
          redefine such expressions in the same
      <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
       <code class="literal">
        ALTER TABLE
       </code>
      </a>
      statement as the
          one that renames the column.
     </p>
    </li>
    <li class="listitem">
     <p>
      Views and stored programs that refer to the renamed column.
          You must manually alter the definition of these objects to
          refer to the new column name.
     </p>
    </li>
   </ul>
  </div>
  <p>
   To reorder columns within a table, use
   <code class="literal">
    FIRST
   </code>
   and
   <code class="literal">
    AFTER
   </code>
   in
   <code class="literal">
    CHANGE
   </code>
   or
   <code class="literal">
    MODIFY
   </code>
   operations.
  </p>
  <a class="indexterm" name="idm46045189035088">
  </a>
  <p>
   <code class="literal">
    ALTER ... SET DEFAULT
   </code>
   or
   <code class="literal">
    ALTER ...
      DROP DEFAULT
   </code>
   specify a new default value for a column or
      remove the old default value, respectively. If the old default is
      removed and the column can be
   <code class="literal">
    NULL
   </code>
   , the new
      default is
   <code class="literal">
    NULL
   </code>
   . If the column cannot be
   <code class="literal">
    NULL
   </code>
   , MySQL assigns a default value as
      described in
   <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>
   <code class="literal">
    ALTER ... SET VISIBLE
   </code>
   and
   <code class="literal">
    ALTER ...
      SET INVISIBLE
   </code>
   enable column visibility to be changed.
      See
   <a class="xref" href="invisible-columns.html" title="15.1.20.10 Invisible Columns">
    Section 15.1.20.10, “Invisible Columns”
   </a>
   .
  </p>
  <h4>
   <a name="alter-table-index">
   </a>
   Primary Keys and Indexes
  </h4>
  <a class="indexterm" name="idm46045189025408">
  </a>
  <a class="indexterm" name="idm46045189024368">
  </a>
  <a class="indexterm" name="idm46045189022880">
  </a>
  <p>
   <code class="literal">
    DROP PRIMARY KEY
   </code>
   drops the
   <a class="link" href="glossary.html#glos_primary_key" title="primary key">
    primary key
   </a>
   . If there is
      no primary key, an error occurs. For information about the
      performance characteristics of primary keys, especially for
   <code class="literal">
    InnoDB
   </code>
   tables, see
   <a class="xref" href="primary-key-optimization.html" title="10.3.2 Primary Key Optimization">
    Section 10.3.2, “Primary Key Optimization”
   </a>
   .
  </p>
  <p>
   If the
   <a class="link" href="server-system-variables.html#sysvar_sql_require_primary_key">
    <code class="literal">
     sql_require_primary_key
    </code>
   </a>
   system variable is enabled, attempting to drop a primary key
      produces an error.
  </p>
  <a class="indexterm" name="idm46045189015968">
  </a>
  <a class="indexterm" name="idm46045189014896">
  </a>
  <p>
   If you add a
   <code class="literal">
    UNIQUE INDEX
   </code>
   or
   <code class="literal">
    PRIMARY
      KEY
   </code>
   to a table, MySQL stores it before any nonunique
      index to permit detection of duplicate keys as early as possible.
  </p>
  <a class="indexterm" name="idm46045189011856">
  </a>
  <a class="indexterm" name="idm46045189010784">
  </a>
  <a class="indexterm" name="idm46045189009296">
  </a>
  <p>
   <a class="link" href="drop-index.html" title="15.1.27 DROP INDEX Statement">
    <code class="literal">
     DROP INDEX
    </code>
   </a>
   removes an index. This
      is a MySQL extension to standard SQL. See
   <a class="xref" href="drop-index.html" title="15.1.27 DROP INDEX Statement">
    Section 15.1.27, “DROP INDEX Statement”
   </a>
   . To determine index names, use
   <code class="literal">
    SHOW INDEX FROM
    <em class="replaceable">
     <code>
      tbl_name
     </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>
   . For details about
   <code class="literal">
    USING
   </code>
   , see
   <a class="xref" href="create-index.html" title="15.1.15 CREATE INDEX Statement">
    Section 15.1.15, “CREATE INDEX Statement”
   </a>
   . The
      preferred position is after the column list. Expect support for
      use of the option before the column list to be removed in a future
      MySQL release.
  </p>
  <p>
   <em class="replaceable">
    <code>
     index_option
    </code>
   </em>
   values specify additional
      options for an index.
   <code class="literal">
    USING
   </code>
   is one such option.
      For details 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>
   .
  </p>
  <p>
   <code class="literal">
    RENAME INDEX
    <em class="replaceable">
     <code>
      old_index_name
     </code>
    </em>
    TO
    <em class="replaceable">
     <code>
      new_index_name
     </code>
    </em>
   </code>
   renames an
      index. This is a MySQL extension to standard SQL. The content of
      the table remains unchanged.
   <em class="replaceable">
    <code>
     old_index_name
    </code>
   </em>
   must be the name of an
      existing index in the table that is not dropped by the same
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
    </code>
   </a>
   statement.
   <em class="replaceable">
    <code>
     new_index_name
    </code>
   </em>
   is the new index name,
      which cannot duplicate the name of an index in the resulting table
      after changes have been applied. Neither index name can be
   <code class="literal">
    PRIMARY
   </code>
   .
  </p>
  <a class="indexterm" name="idm46045188993152">
  </a>
  <a class="indexterm" name="idm46045188992080">
  </a>
  <a class="indexterm" name="idm46045188990592">
  </a>
  <p>
   If you use
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
    </code>
   </a>
   on a
   <code class="literal">
    MyISAM
   </code>
   table, all nonunique indexes are created
      in a separate batch (as for
   <a class="link" href="repair-table.html" title="15.7.3.5 REPAIR TABLE Statement">
    <code class="literal">
     REPAIR
      TABLE
    </code>
   </a>
   ). This should make
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER
      TABLE
    </code>
   </a>
   much faster when you have many indexes.
  </p>
  <p>
   For
   <code class="literal">
    MyISAM
   </code>
   tables, key updating can be
      controlled explicitly. Use
   <code class="literal">
    ALTER TABLE ... DISABLE
      KEYS
   </code>
   to tell MySQL to stop updating nonunique indexes.
      Then use
   <code class="literal">
    ALTER TABLE ... ENABLE KEYS
   </code>
   to
      re-create missing indexes.
   <code class="literal">
    MyISAM
   </code>
   does this
      with a special algorithm that is much faster than inserting keys
      one by one, so disabling keys before performing bulk insert
      operations should give a considerable speedup. Using
   <code class="literal">
    ALTER TABLE ... DISABLE KEYS
   </code>
   requires the
   <a class="link" href="privileges-provided.html#priv_index">
    <code class="literal">
     INDEX
    </code>
   </a>
   privilege in addition to the
      privileges mentioned earlier.
  </p>
  <p>
   While the nonunique indexes are disabled, they are ignored for
      statements such as
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   and
   <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
    <code class="literal">
     EXPLAIN
    </code>
   </a>
   that otherwise would use
      them.
  </p>
  <p>
   After an
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
    </code>
   </a>
   statement, it
      may be necessary to run
   <a class="link" href="analyze-table.html" title="15.7.3.1 ANALYZE TABLE Statement">
    <code class="literal">
     ANALYZE
      TABLE
    </code>
   </a>
   to update index cardinality information. 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>
  <a class="indexterm" name="idm46045188971920">
  </a>
  <p>
   The
   <code class="literal">
    ALTER INDEX
   </code>
   operation permits an index to
      be made visible or invisible. An invisible index is not used by
      the optimizer. Modification of index visibility applies to indexes
      other than primary keys (either explicit or implicit), and cannot
      be performed using
   <code class="literal">
    ALGORITHM=INSTANT
   </code>
   . This
      feature is storage engine neutral (supported for any engine). 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>
  <h4>
   <a name="alter-table-foreign-key">
   </a>
   Foreign Keys and Other Constraints
  </h4>
  <a class="indexterm" name="idm46045188966784">
  </a>
  <a class="indexterm" name="idm46045188965744">
  </a>
  <p>
   The
   <code class="literal">
    FOREIGN KEY
   </code>
   and
   <code class="literal">
    REFERENCES
   </code>
   clauses are supported by the
   <code class="literal">
    InnoDB
   </code>
   and
   <code class="literal">
    NDB
   </code>
   storage
      engines, which implement
   <code class="literal">
    ADD [CONSTRAINT
      [
    <em class="replaceable">
     <code>
      symbol
     </code>
    </em>
    ]] FOREIGN KEY
      [
    <em class="replaceable">
     <code>
      index_name
     </code>
    </em>
    ] (...) REFERENCES ...
      (...)
   </code>
   . 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>
   .
      For other storage engines, the clauses are parsed but ignored.
  </p>
  <p>
   For
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
    </code>
   </a>
   , unlike
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE TABLE
    </code>
   </a>
   ,
   <code class="literal">
    ADD FOREIGN
      KEY
   </code>
   ignores
   <em class="replaceable">
    <code>
     index_name
    </code>
   </em>
   if
      given and uses an automatically generated foreign key name. As a
      workaround, include the
   <code class="literal">
    CONSTRAINT
   </code>
   clause to
      specify the foreign key name:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa68560052"><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">ADD</span> <span class="token keyword">CONSTRAINT</span> <span class="token keyword"><em class="replaceable">name</em></span> <span class="token keyword">FOREIGN</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 punctuation">.</span><span class="token punctuation">)</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span></code></pre>
  </div>
  <div class="important" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Important
   </div>
   <p>
    MySQL silently ignores inline
    <code class="literal">
     REFERENCES
    </code>
    specifications, where the references are defined as part of the
        column specification. MySQL accepts only
    <code class="literal">
     REFERENCES
    </code>
    clauses defined as part of a
        separate
    <code class="literal">
     FOREIGN KEY
    </code>
    specification.
   </p>
  </div>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    Partitioned
    <code class="literal">
     InnoDB
    </code>
    tables do not support
        foreign keys. This restriction does not apply to
    <code class="literal">
     NDB
    </code>
    tables, including those explicitly
        partitioned by
    <code class="literal">
     [LINEAR] KEY
    </code>
    . For more
        information, see
    <a class="xref" href="partitioning-limitations-storage-engines.html" title="26.6.2 Partitioning Limitations Relating to Storage Engines">
     Section 26.6.2, “Partitioning Limitations Relating to Storage Engines”
    </a>
    .
   </p>
  </div>
  <a class="indexterm" name="idm46045188945520">
  </a>
  <a class="indexterm" name="idm46045188944448">
  </a>
  <a class="indexterm" name="idm46045188942960">
  </a>
  <p>
   MySQL Server and NDB Cluster both support the use of
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
    </code>
   </a>
   to drop foreign keys:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa75617452"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> <em class="replaceable">tbl_name</em> <span class="token keyword">DROP</span> <span class="token keyword">FOREIGN</span> <span class="token keyword">KEY</span> <em class="replaceable">fk_symbol</em><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Adding and dropping a foreign key in the same
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
    </code>
   </a>
   statement is supported
      for
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE ...
      ALGORITHM=INPLACE
    </code>
   </a>
   but not for
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE ...
      ALGORITHM=COPY
    </code>
   </a>
   .
  </p>
  <p>
   The server prohibits changes to foreign key columns that have the
      potential to cause loss of referential integrity. A workaround is
      to use
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
      ... DROP FOREIGN KEY
    </code>
   </a>
   before changing the column
      definition and
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER
      TABLE ... ADD FOREIGN KEY
    </code>
   </a>
   afterward. Examples of
      prohibited changes include:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Changes to the data type of foreign key columns that may be
          unsafe. For example, changing
      <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
       <code class="literal">
        VARCHAR(20)
       </code>
      </a>
      to
      <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
       <code class="literal">
        VARCHAR(30)
       </code>
      </a>
      is permitted, but
          changing it to
      <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
       <code class="literal">
        VARCHAR(1024)
       </code>
      </a>
      is
          not because that alters the number of length bytes required to
          store individual values.
     </p>
    </li>
    <li class="listitem">
     <p>
      Changing a
      <code class="literal">
       NULL
      </code>
      column to
      <code class="literal">
       NOT
          NULL
      </code>
      in non-strict mode is prohibited to prevent
          converting
      <code class="literal">
       NULL
      </code>
      values to default
          non-
      <code class="literal">
       NULL
      </code>
      values, for which there are no
          corresponding values in the referenced table. The operation is
          permitted in strict mode, but an error is returned if any such
          conversion is required.
     </p>
    </li>
   </ul>
  </div>
  <p>
   <code class="literal">
    ALTER TABLE
    <em class="replaceable">
     <code>
      tbl_name
     </code>
    </em>
    RENAME
    <em class="replaceable">
     <code>
      new_tbl_name
     </code>
    </em>
   </code>
   changes
      internally generated foreign key constraint names and user-defined
      foreign key constraint names that begin with the string
   <span class="quote">
    “
    <span class="quote">
     <em class="replaceable">
      <code>
       tbl_name
      </code>
     </em>
     _ibfk_
    </span>
    ”
   </span>
   to
      reflect the new table name.
   <code class="literal">
    InnoDB
   </code>
   interprets
      foreign key constraint names that begin with the string
   <span class="quote">
    “
    <span class="quote">
     <em class="replaceable">
      <code>
       tbl_name
      </code>
     </em>
     _ibfk_
    </span>
    ”
   </span>
   as
      internally generated names.
  </p>
  <a class="indexterm" name="idm46045188917328">
  </a>
  <p>
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
    </code>
   </a>
   permits
   <code class="literal">
    CHECK
   </code>
   constraints for existing tables to be
      added, dropped, or altered:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Add a new
      <code class="literal">
       CHECK
      </code>
      constraint:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa68801855"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> <em class="replaceable">tbl_name</em>
    <span class="token keyword">ADD</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">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><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      The meaning of constraint syntax elements is the same as for
      <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
       <code class="literal">
        CREATE TABLE
       </code>
      </a>
      . 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>
      Drop an existing
      <code class="literal">
       CHECK
      </code>
      constraint named
      <em class="replaceable">
       <code>
        symbol
       </code>
      </em>
      :
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa63969837"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> <em class="replaceable">tbl_name</em>
    <span class="token keyword">DROP</span> <span class="token keyword">CHECK</span> <em class="replaceable">symbol</em><span class="token punctuation">;</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      Alter whether an existing
      <code class="literal">
       CHECK
      </code>
      constraint
          named
      <em class="replaceable">
       <code>
        symbol
       </code>
      </em>
      is enforced:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa76747192"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> <em class="replaceable">tbl_name</em>
    <span class="token keyword">ALTER</span> <span class="token keyword">CHECK</span> <em class="replaceable">symbol</em> <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></code></pre>
     </div>
    </li>
   </ul>
  </div>
  <p>
   The
   <code class="literal">
    DROP CHECK
   </code>
   and
   <code class="literal">
    ALTER
      CHECK
   </code>
   clauses are MySQL extensions to standard SQL.
  </p>
  <p>
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
    </code>
   </a>
   permits more general
      (and SQL standard) syntax for dropping and altering existing
      constraints of any type, where the constraint type is determined
      from the constraint name:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Drop an existing constraint named
      <em class="replaceable">
       <code>
        symbol
       </code>
      </em>
      :
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa10600870"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> <em class="replaceable">tbl_name</em>
    <span class="token keyword">DROP</span> <span class="token keyword">CONSTRAINT</span> <em class="replaceable">symbol</em><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      If the
      <a class="link" href="server-system-variables.html#sysvar_sql_require_primary_key">
       <code class="literal">
        sql_require_primary_key
       </code>
      </a>
      system variable is enabled, attempting to drop a primary key
          produces an error.
     </p>
    </li>
    <li class="listitem">
     <p>
      Alter whether an existing constraint named
      <em class="replaceable">
       <code>
        symbol
       </code>
      </em>
      is enforced:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa36041624"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> <em class="replaceable">tbl_name</em>
    <span class="token keyword">ALTER</span> <span class="token keyword">CONSTRAINT</span> <em class="replaceable">symbol</em> <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></code></pre>
     </div>
     <p>
      Only
      <code class="literal">
       CHECK
      </code>
      constraints can be altered to be
          unenforced. All other constraint types are always enforced.
     </p>
    </li>
   </ul>
  </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. When multiple constraints have the same name,
   <code class="literal">
    DROP CONSTRAINT
   </code>
   and
   <code class="literal">
    ADD
      CONSTRAINT
   </code>
   are ambiguous and an error occurs. In such
      cases, constraint-specific syntax must be used to modify the
      constraint. For example, use
   <code class="literal">
    DROP PRIMARY KEY
   </code>
   or DROP FOREIGN KEY to drop a primary key or foreign key.
  </p>
  <p>
   If a table alteration causes a violation of an enforced
   <code class="literal">
    CHECK
   </code>
   constraint, an error occurs and the table
      is not modified. Examples of operations for which an error occurs:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Attempts to add the
      <code class="literal">
       AUTO_INCREMENT
      </code>
      attribute to a column that is used in a
      <code class="literal">
       CHECK
      </code>
      constraint.
     </p>
    </li>
    <li class="listitem">
     <p>
      Attempts to add an enforced
      <code class="literal">
       CHECK
      </code>
      constraint or enforce a nonenforced
      <code class="literal">
       CHECK
      </code>
      constraint for which existing rows violate the constraint
          condition.
     </p>
    </li>
    <li class="listitem">
     <p>
      Attempts to modify, rename, or drop a column that is used in a
      <code class="literal">
       CHECK
      </code>
      constraint, unless that constraint is
          also dropped in the same statement. Exception: If a
      <code class="literal">
       CHECK
      </code>
      constraint refers only to a single
          column, dropping the column automatically drops the
          constraint.
     </p>
    </li>
   </ul>
  </div>
  <p>
   <code class="literal">
    ALTER TABLE
    <em class="replaceable">
     <code>
      tbl_name
     </code>
    </em>
    RENAME
    <em class="replaceable">
     <code>
      new_tbl_name
     </code>
    </em>
   </code>
   changes
      internally generated and user-defined
   <code class="literal">
    CHECK
   </code>
   constraint names that begin with the string
   <span class="quote">
    “
    <span class="quote">
     <em class="replaceable">
      <code>
       tbl_name
      </code>
     </em>
     _chk_
    </span>
    ”
   </span>
   to reflect
      the new table name. MySQL interprets
   <code class="literal">
    CHECK
   </code>
   constraint names that begin with the string
   <span class="quote">
    “
    <span class="quote">
     <em class="replaceable">
      <code>
       tbl_name
      </code>
     </em>
     _chk_
    </span>
    ”
   </span>
   as
      internally generated names.
  </p>
  <h4>
   <a name="alter-table-character-set">
   </a>
   Changing the Character Set
  </h4>
  <p>
   <a class="indexterm" name="idm46045188867552">
   </a>
   To change the table default character set and all character
      columns (
   <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>
   ) to a new character set, use a
      statement like this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa32961287"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> <em class="replaceable">tbl_name</em> CONVERT <span class="token keyword">TO</span> <span class="token keyword">CHARACTER</span> <span class="token keyword">SET</span> <em class="replaceable">charset_name</em><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   The statement also changes the collation of all character columns.
      If you specify no
   <code class="literal">
    COLLATE
   </code>
   clause to indicate
      which collation to use, the statement uses default collation for
      the character set. If this collation is inappropriate for the
      intended table use (for example, if it would change from a
      case-sensitive collation to a case-insensitive collation), specify
      a collation explicitly.
  </p>
  <p>
   For a column that has a data type of
   <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
    <code class="literal">
     VARCHAR
    </code>
   </a>
   or one of the
   <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
    <code class="literal">
     TEXT
    </code>
   </a>
   types,
   <code class="literal">
    CONVERT TO
      CHARACTER SET
   </code>
   changes the data type as necessary to
      ensure that the new column is long enough to store as many
      characters as the original column. For example, a
   <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
    <code class="literal">
     TEXT
    </code>
   </a>
   column has two length bytes,
      which store the byte-length of values in the column, up to a
      maximum of 65,535. For a
   <code class="literal">
    latin1
   </code>
   <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
    <code class="literal">
     TEXT
    </code>
   </a>
   column, each character
      requires a single byte, so the column can store up to 65,535
      characters. If the column is converted to
   <code class="literal">
    utf8mb4
   </code>
   , each character might require up to 4
      bytes, for a maximum possible length of 4 × 65,535 = 262,140
      bytes. That length does not fit in a
   <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
    <code class="literal">
     TEXT
    </code>
   </a>
   column's length bytes, so
      MySQL converts the data type to
   <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
    <code class="literal">
     MEDIUMTEXT
    </code>
   </a>
   , which is the smallest
      string type for which the length bytes can record a value of
      262,140. Similarly, a
   <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
    <code class="literal">
     VARCHAR
    </code>
   </a>
   column might be converted to
   <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
    <code class="literal">
     MEDIUMTEXT
    </code>
   </a>
   .
  </p>
  <p>
   To avoid data type changes of the type just described, do not use
   <code class="literal">
    CONVERT TO CHARACTER SET
   </code>
   . Instead, use
   <code class="literal">
    MODIFY
   </code>
   to change individual columns. For
      example:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa34821741"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t <span class="token keyword">MODIFY</span> latin1_text_col <span class="token datatype">TEXT</span> <span class="token keyword">CHARACTER</span> <span class="token keyword">SET</span> utf8mb4<span class="token punctuation">;</span>
<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t <span class="token keyword">MODIFY</span> latin1_varchar_col <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><em class="replaceable">M</em><span class="token punctuation">)</span> <span class="token keyword">CHARACTER</span> <span class="token keyword">SET</span> utf8mb4<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   If you specify
   <code class="literal">
    CONVERT TO CHARACTER SET binary
   </code>
   ,
      the
   <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 are converted to their
      corresponding 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>
   ). This means that the columns
      no longer have a character set and a subsequent
   <code class="literal">
    CONVERT
      TO
   </code>
   operation does not apply to them.
  </p>
  <p>
   If
   <em class="replaceable">
    <code>
     charset_name
    </code>
   </em>
   is
   <code class="literal">
    DEFAULT
   </code>
   in a
   <code class="literal">
    CONVERT TO CHARACTER
      SET
   </code>
   operation, the character set named by the
   <a class="link" href="server-system-variables.html#sysvar_character_set_database">
    <code class="literal">
     character_set_database
    </code>
   </a>
   system
      variable is used.
  </p>
  <div class="warning" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Warning
   </div>
   <p>
    The
    <code class="literal">
     CONVERT TO
    </code>
    operation converts column
        values between the original and named character sets. This is
    <span class="emphasis">
     <em>
      not
     </em>
    </span>
    what you want if you have a column in
        one character set (like
    <code class="literal">
     latin1
    </code>
    ) but the
        stored values actually use some other, incompatible character
        set (like
    <code class="literal">
     utf8mb4
    </code>
    ). In this case, you have to
        do the following for each such column:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa51832325"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">CHANGE</span> c1 c1 <span class="token datatype">BLOB</span><span class="token punctuation">;</span>
<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">CHANGE</span> c1 c1 <span class="token datatype">TEXT</span> <span class="token keyword">CHARACTER</span> <span class="token keyword">SET</span> utf8mb4<span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    The reason this works is that there is no conversion when you
        convert to or from
    <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
     <code class="literal">
      BLOB
     </code>
    </a>
    columns.
   </p>
  </div>
  <p>
   To change only the
   <span class="emphasis">
    <em>
     default
    </em>
   </span>
   character set for
      a table, use this statement:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa17876601"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> <em class="replaceable">tbl_name</em> <span class="token keyword">DEFAULT</span> <span class="token keyword">CHARACTER</span> <span class="token keyword">SET</span> <em class="replaceable">charset_name</em><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   The word
   <code class="literal">
    DEFAULT
   </code>
   is optional. The default
      character set is the character set that is used if you do not
      specify the character set for columns that you add to a table
      later (for example, with
   <code class="literal">
    ALTER TABLE ... ADD
      column
   </code>
   ).
  </p>
  <p>
   When the
   <a class="link" href="server-system-variables.html#sysvar_foreign_key_checks">
    <code class="literal">
     foreign_key_checks
    </code>
   </a>
   system variable is enabled, which is the default setting,
      character set conversion is not permitted on tables that include a
      character string column used in a foreign key constraint. The
      workaround is to disable
   <a class="link" href="server-system-variables.html#sysvar_foreign_key_checks">
    <code class="literal">
     foreign_key_checks
    </code>
   </a>
   before
      performing the character set conversion. You must perform the
      conversion on both tables involved in the foreign key constraint
      before re-enabling
   <a class="link" href="server-system-variables.html#sysvar_foreign_key_checks">
    <code class="literal">
     foreign_key_checks
    </code>
   </a>
   . If you
      re-enable
   <a class="link" href="server-system-variables.html#sysvar_foreign_key_checks">
    <code class="literal">
     foreign_key_checks
    </code>
   </a>
   after converting only one of the tables, an
   <code class="literal">
    ON DELETE
      CASCADE
   </code>
   or
   <code class="literal">
    ON UPDATE CASCADE
   </code>
   operation could corrupt data in the referencing table due to
      implicit conversion that occurs during these operations (Bug
      #45290, Bug #74816).
  </p>
  <h4>
   <a name="alter-table-import">
   </a>
   Importing InnoDB Tables
  </h4>
  <a class="indexterm" name="idm46045188809568">
  </a>
  <a class="indexterm" name="idm46045188808496">
  </a>
  <p>
   An
   <code class="literal">
    InnoDB
   </code>
   table created in its own
   <a class="link" href="glossary.html#glos_file_per_table" title="file-per-table">
    file-per-table
   </a>
   tablespace can be imported from a backup or from another MySQL
      server instance using
   <code class="literal">
    DISCARD TABLEPACE
   </code>
   and
   <code class="literal">
    IMPORT TABLESPACE
   </code>
   clauses. See
   <a class="xref" href="innodb-table-import.html" title="17.6.1.3 Importing InnoDB Tables">
    Section 17.6.1.3, “Importing InnoDB Tables”
   </a>
   .
  </p>
  <h4>
   <a name="alter-table-row-order">
   </a>
   Row Order for MyISAM Tables
  </h4>
  <a class="indexterm" name="idm46045188801936">
  </a>
  <p>
   <code class="literal">
    ORDER BY
   </code>
   enables you to create the new table
      with the rows in a specific order. This option is useful primarily
      when you know that you query the rows in a certain order most of
      the time. By using this option after major changes to the table,
      you might be able to get higher performance. In some cases, it
      might make sorting easier for MySQL if the table is in order by
      the column that you want to order it by later.
  </p>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    The table does not remain in the specified order after inserts
        and deletes.
   </p>
  </div>
  <p>
   <code class="literal">
    ORDER BY
   </code>
   syntax permits one or more column
      names to be specified for sorting, each of which optionally can be
      followed by
   <code class="literal">
    ASC
   </code>
   or
   <code class="literal">
    DESC
   </code>
   to
      indicate ascending or descending sort order, respectively. The
      default is ascending order. Only column names are permitted as
      sort criteria; arbitrary expressions are not permitted. This
      clause should be given last after any other clauses.
  </p>
  <p>
   <code class="literal">
    ORDER BY
   </code>
   does not make sense for
   <code class="literal">
    InnoDB
   </code>
   tables because
   <code class="literal">
    InnoDB
   </code>
   always orders table rows according to the
   <a class="link" href="glossary.html#glos_clustered_index" title="clustered index">
    clustered index
   </a>
   .
  </p>
  <p>
   When used on a partitioned table,
   <code class="literal">
    ALTER TABLE ... ORDER
      BY
   </code>
   orders rows within each partition only.
  </p>
  <h4>
   <a name="alter-table-partition-options">
   </a>
   Partitioning Options
  </h4>
  <p>
   <em class="replaceable">
    <code>
     partition_options
    </code>
   </em>
   signifies options
      that can be used with partitioned tables for repartitioning, to
      add, drop, discard, import, merge, and split partitions, and to
      perform partitioning maintenance.
  </p>
  <p>
   It is possible for an
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
    </code>
   </a>
   statement to contain a
   <code class="literal">
    PARTITION BY
   </code>
   or
   <code class="literal">
    REMOVE PARTITIONING
   </code>
   clause in an addition to
      other alter specifications, but the
   <code class="literal">
    PARTITION
      BY
   </code>
   or
   <code class="literal">
    REMOVE PARTITIONING
   </code>
   clause must
      be specified last after any other specifications. The
   <code class="literal">
    ADD
      PARTITION
   </code>
   ,
   <code class="literal">
    DROP PARTITION
   </code>
   ,
   <code class="literal">
    DISCARD PARTITION
   </code>
   ,
   <code class="literal">
    IMPORT
      PARTITION
   </code>
   ,
   <code class="literal">
    COALESCE PARTITION
   </code>
   ,
   <code class="literal">
    REORGANIZE PARTITION
   </code>
   ,
   <code class="literal">
    EXCHANGE
      PARTITION
   </code>
   ,
   <code class="literal">
    ANALYZE PARTITION
   </code>
   ,
   <code class="literal">
    CHECK PARTITION
   </code>
   , and
   <code class="literal">
    REPAIR
      PARTITION
   </code>
   options cannot be combined with other alter
      specifications in a single
   <code class="literal">
    ALTER TABLE
   </code>
   , since
      the options just listed act on individual partitions.
  </p>
  <p>
   For more information about partition options, see
   <a class="xref" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    Section 15.1.20, “CREATE TABLE Statement”
   </a>
   , and
   <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>
   . For
      information about and examples of
   <code class="literal">
    ALTER TABLE ...
      EXCHANGE PARTITION
   </code>
   statements, see
   <a class="xref" href="partitioning-management-exchange.html" title="26.3.3 Exchanging Partitions and Subpartitions with Tables">
    Section 26.3.3, “Exchanging Partitions and Subpartitions with Tables”
   </a>
   .
  </p>
 </div>
 <br/>
</div>
