<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h2 class="title">
      <a name="storage-engine-setting">
      </a>
      18.1 Setting the Storage Engine
     </h2>
    </div>
   </div>
  </div>
  <p>
   When you create a new table, you can specify which storage engine
      to use by adding an
   <code class="literal">
    ENGINE
   </code>
   table option to the
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE TABLE
    </code>
   </a>
   statement:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa42581440"><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">-- ENGINE=INNODB not needed unless you have set a different</span>
<span class="token comment" spellcheck="true">-- default storage engine.</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t1 <span class="token punctuation">(</span>i <span class="token datatype">INT</span><span class="token punctuation">)</span> <span class="token keyword">ENGINE</span> <span class="token operator">=</span> INNODB<span class="token punctuation">;</span>
<span class="token comment" spellcheck="true">-- Simple table definitions can be switched from one to another.</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t2 <span class="token punctuation">(</span>i <span class="token datatype">INT</span><span class="token punctuation">)</span> <span class="token keyword">ENGINE</span> <span class="token operator">=</span> CSV<span class="token punctuation">;</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t3 <span class="token punctuation">(</span>i <span class="token datatype">INT</span><span class="token punctuation">)</span> <span class="token keyword">ENGINE</span> <span class="token operator">=</span> <span class="token keyword">MEMORY</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   When you omit the
   <code class="literal">
    ENGINE
   </code>
   option, the default
      storage engine is used. The default engine is
   <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
    <code class="literal">
     InnoDB
    </code>
   </a>
   in MySQL 8.4. You
      can specify the default engine by using the
   <a class="link" href="server-system-variables.html#sysvar_default_storage_engine">
    <code class="literal">
     --default-storage-engine
    </code>
   </a>
   server
      startup option, or by setting the
   <a class="link" href="server-system-variables.html#sysvar_default_storage_engine">
    <code class="literal">
     default-storage-engine
    </code>
   </a>
   option in
      the
   <code class="filename">
    my.cnf
   </code>
   configuration file.
  </p>
  <p>
   You can set the default storage engine for the current session by
      setting the
   <a class="link" href="server-system-variables.html#sysvar_default_storage_engine">
    <code class="literal">
     default_storage_engine
    </code>
   </a>
   variable:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa1268972"><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">SET</span> default_storage_engine<span class="token operator">=</span><span class="token keyword">NDBCLUSTER</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   The storage engine for
   <code class="literal">
    TEMPORARY
   </code>
   tables created
      with
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE
      TEMPORARY TABLE
    </code>
   </a>
   can be set separately from the engine
      for permanent tables by setting the
   <a class="link" href="server-system-variables.html#sysvar_default_tmp_storage_engine">
    <code class="literal">
     default_tmp_storage_engine
    </code>
   </a>
   ,
      either at startup or at runtime.
  </p>
  <p>
   To convert a table from one storage engine to another, use an
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
    </code>
   </a>
   statement that
      indicates the new engine:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa3874200"><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">ENGINE</span> <span class="token operator">=</span> InnoDB<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   See
   <a class="xref" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    Section 15.1.20, “CREATE TABLE Statement”
   </a>
   , and
   <a class="xref" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    Section 15.1.9, “ALTER TABLE Statement”
   </a>
   .
  </p>
  <p>
   If you try to use a storage engine that is not compiled in or that
      is compiled in but deactivated, MySQL instead creates a table
      using the default storage engine. For example, in a replication
      setup, perhaps your source server uses
   <code class="literal">
    InnoDB
   </code>
   tables for maximum safety, but the replica servers use other
      storage engines for speed at the expense of durability or
      concurrency.
  </p>
  <p>
   By default, a warning is generated whenever
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE TABLE
    </code>
   </a>
   or
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
    </code>
   </a>
   cannot use the default
      storage engine. To prevent confusing, unintended behavior if the
      desired engine is unavailable, enable the
   <a class="link" href="sql-mode.html#sqlmode_no_engine_substitution">
    <code class="literal">
     NO_ENGINE_SUBSTITUTION
    </code>
   </a>
   SQL mode.
      If the desired engine is unavailable, this setting produces an
      error instead of a warning, and the table is not created or
      altered. See
   <a class="xref" href="sql-mode.html" title="7.1.11 Server SQL Modes">
    Section 7.1.11, “Server SQL Modes”
   </a>
   .
  </p>
  <p>
   MySQL may store a table's index and data in one or more other
      files, depending on the storage engine. Table and column
      definitions are stored in the MySQL data dictionary. Individual
      storage engines create any additional files required for the
      tables that they manage. If a table name contains special
      characters, the names for the table files contain encoded versions
      of those characters as described in
   <a class="xref" href="identifier-mapping.html" title="11.2.4 Mapping of Identifiers to File Names">
    Section 11.2.4, “Mapping of Identifiers to File Names”
   </a>
   .
  </p>
 </div>
 <br/>
</div>
