<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="general-tablespaces">
      </a>
      17.6.3.3 General Tablespaces
     </h4>
    </div>
   </div>
  </div>
  <p>
   A general tablespace is a shared
   <code class="literal">
    InnoDB
   </code>
   tablespace that is created using
   <a class="link" href="create-tablespace.html" title="15.1.21 CREATE TABLESPACE Statement">
    <code class="literal">
     CREATE
      TABLESPACE
    </code>
   </a>
   syntax. General tablespace capabilities and
      features are 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="general-tablespaces.html#general-tablespaces-capabilities" title="General Tablespace Capabilities">
       General Tablespace Capabilities
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="general-tablespaces.html#general-tablespaces-creating" title="Creating a General Tablespace">
       Creating a General Tablespace
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="general-tablespaces.html#general-tablespaces-adding-tables" title="Adding Tables to a General Tablespace">
       Adding Tables to a General Tablespace
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="general-tablespaces.html#general-tablespaces-row-format-support" title="General Tablespace Row Format Support">
       General Tablespace Row Format Support
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <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>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="general-tablespaces.html#general-tablespaces-renaming" title="Renaming a General Tablespace">
       Renaming a General Tablespace
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="general-tablespaces.html#general-tablespaces-dropping" title="Dropping a General Tablespace">
       Dropping a General Tablespace
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="general-tablespaces.html#general-tablespaces-limitations" title="General Tablespace Limitations">
       General Tablespace Limitations
      </a>
     </p>
    </li>
   </ul>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="general-tablespaces-capabilities">
       </a>
       General Tablespace Capabilities
      </h5>
     </div>
    </div>
   </div>
   <p>
    General tablespaces provide the following capabilities:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       Similar to the system tablespace, general tablespaces are
            shared tablespaces capable of storing data for multiple
            tables.
      </p>
     </li>
     <li class="listitem">
      <p>
       General tablespaces have a potential memory advantage over
       <a class="link" href="innodb-file-per-table-tablespaces.html" title="17.6.3.2 File-Per-Table Tablespaces">
        file-per-table
            tablespaces
       </a>
       . The server keeps tablespace metadata in
            memory for the lifetime of a tablespace. Multiple tables in
            fewer general tablespaces consume less memory for tablespace
            metadata than the same number of tables in separate
            file-per-table tablespaces.
      </p>
     </li>
     <li class="listitem">
      <p>
       General tablespace data files can be placed in a directory
            relative to or independent of the MySQL data directory,
            which provides you with many of the data file and storage
            management capabilities of
       <a class="link" href="innodb-file-per-table-tablespaces.html" title="17.6.3.2 File-Per-Table Tablespaces">
        file-per-table
            tablespaces
       </a>
       . As with file-per-table tablespaces, the
            ability to place data files outside of the MySQL data
            directory allows you to manage performance of critical
            tables separately, setup RAID or DRBD for specific tables,
            or bind tables to particular disks, for example.
      </p>
     </li>
     <li class="listitem">
      <p>
       General tablespaces support all table row formats and
            associated features.
      </p>
     </li>
     <li class="listitem">
      <p>
       The
       <code class="literal">
        TABLESPACE
       </code>
       option can be used with
       <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
        <code class="literal">
         CREATE TABLE
        </code>
       </a>
       to create tables
            in a general tablespaces, file-per-table tablespace, or in
            the system tablespace.
      </p>
     </li>
     <li class="listitem">
      <p>
       The
       <code class="literal">
        TABLESPACE
       </code>
       option can be used with
       <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
        <code class="literal">
         ALTER TABLE
        </code>
       </a>
       to move tables
            between general tablespaces, file-per-table tablespaces, and
            the system tablespace.
      </p>
     </li>
    </ul>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="general-tablespaces-creating">
       </a>
       Creating a General Tablespace
      </h5>
     </div>
    </div>
   </div>
   <p>
    General tablespaces are created using
    <a class="link" href="create-tablespace.html" title="15.1.21 CREATE TABLESPACE Statement">
     <code class="literal">
      CREATE TABLESPACE
     </code>
    </a>
    syntax.
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa22223263"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLESPACE</span> <em class="replaceable">tablespace_name</em>
    <span class="token punctuation">[</span><span class="token keyword">ADD</span> <span class="token keyword">DATAFILE</span> <span class="token string">'<em class="replaceable">file_name</em>'</span><span class="token punctuation">]</span>
    <span class="token punctuation">[</span><span class="token keyword">FILE_BLOCK_SIZE</span> <span class="token operator">=</span> <span class="token keyword"><em class="replaceable">value</em></span><span class="token punctuation">]</span>
        <span class="token punctuation">[</span><span class="token keyword">ENGINE</span> <span class="token punctuation">[</span><span class="token operator">=</span><span class="token punctuation">]</span> <em class="replaceable">engine_name</em><span class="token punctuation">]</span></code></pre>
   </div>
   <p>
    A general tablespace can be created in the data directory or
        outside of it. To avoid conflicts with implicitly created
        file-per-table tablespaces, creating a general tablespace in a
        subdirectory under the data directory is not supported. When
        creating a general tablespace outside of the data directory, the
        directory must exist and must be known to
    <code class="literal">
     InnoDB
    </code>
    prior to creating the tablespace. To
        make an unknown directory known to
    <code class="literal">
     InnoDB
    </code>
    ,
        add the directory to the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_directories">
     <code class="literal">
      innodb_directories
     </code>
    </a>
    argument
        value.
    <a class="link" href="innodb-parameters.html#sysvar_innodb_directories">
     <code class="literal">
      innodb_directories
     </code>
    </a>
    is a
        read-only startup option. Configuring it requires restarting the
        server.
   </p>
   <p>
    Examples:
   </p>
   <p>
    Creating a general tablespace in the data directory:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa90744458"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLESPACE</span> <span class="token punctuation">`</span>ts1<span class="token punctuation">`</span> <span class="token keyword">ADD</span> <span class="token keyword">DATAFILE</span> <span class="token string">'ts1.ibd'</span> <span class="token keyword">Engine</span><span class="token operator">=</span>InnoDB<span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    or
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa34549756"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLESPACE</span> <span class="token punctuation">`</span>ts1<span class="token punctuation">`</span> <span class="token keyword">Engine</span><span class="token operator">=</span>InnoDB<span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    The
    <code class="literal">
     ADD DATAFILE
    </code>
    clause is optional. If the
    <code class="literal">
     ADD DATAFILE
    </code>
    clause is not specified when
        creating a tablespace, a tablespace data file with a unique file
        name is created implicitly. The unique file name is a 128 bit
        UUID formatted into five groups of hexadecimal numbers separated
        by dashes
        (
    <em class="replaceable">
     <code>
      aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee
     </code>
    </em>
    ).
        General tablespace data files include an
    <code class="filename">
     .ibd
    </code>
    file extension. In a replication
        environment, the data file name created on the source is not the
        same as the data file name created on the replica.
   </p>
   <p>
    Creating a general tablespace in a directory outside of the data
        directory:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa77995652"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLESPACE</span> <span class="token punctuation">`</span>ts1<span class="token punctuation">`</span> <span class="token keyword">ADD</span> <span class="token keyword">DATAFILE</span> <span class="token string">'/my/tablespace/directory/ts1.ibd'</span> <span class="token keyword">Engine</span><span class="token operator">=</span>InnoDB<span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    You can specify a path that is relative to the data directory as
        long as the tablespace directory is not under the data
        directory. In this example, the
    <code class="filename">
     my_tablespace
    </code>
    directory is at the same
        level as the data directory:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa48485064"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLESPACE</span> <span class="token punctuation">`</span>ts1<span class="token punctuation">`</span> <span class="token keyword">ADD</span> <span class="token keyword">DATAFILE</span> <span class="token string">'../my_tablespace/ts1.ibd'</span> <span class="token keyword">Engine</span><span class="token operator">=</span>InnoDB<span class="token punctuation">;</span></code></pre>
   </div>
   <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
    <div class="admon-title">
     Note
    </div>
    <p>
     The
     <code class="literal">
      ENGINE = InnoDB
     </code>
     clause must be defined
          as part of the
     <a class="link" href="create-tablespace.html" title="15.1.21 CREATE TABLESPACE Statement">
      <code class="literal">
       CREATE
          TABLESPACE
      </code>
     </a>
     statement, or
     <code class="literal">
      InnoDB
     </code>
     must be defined as the default storage engine
          (
     <a class="link" href="server-system-variables.html#sysvar_default_storage_engine">
      <code class="literal">
       default_storage_engine=InnoDB
      </code>
     </a>
     ).
    </p>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="general-tablespaces-adding-tables">
       </a>
       Adding Tables to a General Tablespace
      </h5>
     </div>
    </div>
   </div>
   <p>
    After creating a general tablespace,
    <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
     <code class="literal">
      CREATE TABLE
      <em class="replaceable">
       <code>
        tbl_name
       </code>
      </em>
      ... TABLESPACE [=]
      <em class="replaceable">
       <code>
        tablespace_name
       </code>
      </em>
     </code>
    </a>
    or
    <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>
      TABLESPACE [=]
      <em class="replaceable">
       <code>
        tablespace_name
       </code>
      </em>
     </code>
    </a>
    statements
        can be used to add tables to the tablespace, as shown in the
        following examples:
   </p>
   <p>
    <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
     <code class="literal">
      CREATE TABLE
     </code>
    </a>
    :
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa7562109"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t1 <span class="token punctuation">(</span>c1 <span class="token datatype">INT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">)</span> <span class="token keyword">TABLESPACE</span> ts1<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>
    :
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa69231378"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t2 <span class="token keyword">TABLESPACE</span> ts1<span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    Adding table partitions to shared tablespaces is not supported.
        Shared tablespaces include the
    <code class="literal">
     InnoDB
    </code>
    system
        tablespace and general tablespaces.
   </p>
   <p>
    For detailed syntax information, see
    <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
     <code class="literal">
      CREATE
        TABLE
     </code>
    </a>
    and
    <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
     <code class="literal">
      ALTER TABLE
     </code>
    </a>
    .
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="general-tablespaces-row-format-support">
       </a>
       General Tablespace Row Format Support
      </h5>
     </div>
    </div>
   </div>
   <p>
    General tablespaces support all table row formats
        (
    <code class="literal">
     REDUNDANT
    </code>
    ,
    <code class="literal">
     COMPACT
    </code>
    ,
    <code class="literal">
     DYNAMIC
    </code>
    ,
    <code class="literal">
     COMPRESSED
    </code>
    ) with
        the caveat that compressed and uncompressed tables cannot
        coexist in the same general tablespace due to different physical
        page sizes.
   </p>
   <p>
    For a general tablespace to contain compressed tables
        (
    <code class="literal">
     ROW_FORMAT=COMPRESSED
    </code>
    ), the
    <code class="literal">
     FILE_BLOCK_SIZE
    </code>
    option must be specified, and
        the
    <code class="literal">
     FILE_BLOCK_SIZE
    </code>
    value must be a valid
        compressed page size in relation to the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_page_size">
     <code class="literal">
      innodb_page_size
     </code>
    </a>
    value. Also,
        the physical page size of the compressed table
        (
    <code class="literal">
     KEY_BLOCK_SIZE
    </code>
    ) must be equal to
    <code class="literal">
     FILE_BLOCK_SIZE/1024
    </code>
    . For example, if
    <a class="link" href="innodb-parameters.html#sysvar_innodb_page_size">
     <code class="literal">
      innodb_page_size=16KB
     </code>
    </a>
    and
    <code class="literal">
     FILE_BLOCK_SIZE=8K
    </code>
    , the
    <code class="literal">
     KEY_BLOCK_SIZE
    </code>
    of the table must be 8.
   </p>
   <p>
    The following table shows permitted
    <a class="link" href="innodb-parameters.html#sysvar_innodb_page_size">
     <code class="literal">
      innodb_page_size
     </code>
    </a>
    ,
    <code class="literal">
     FILE_BLOCK_SIZE
    </code>
    , and
    <code class="literal">
     KEY_BLOCK_SIZE
    </code>
    combinations.
    <code class="literal">
     FILE_BLOCK_SIZE
    </code>
    values may also be specified
        in bytes. To determine a valid
    <code class="literal">
     KEY_BLOCK_SIZE
    </code>
    value for a given
    <code class="literal">
     FILE_BLOCK_SIZE
    </code>
    , divide the
    <code class="literal">
     FILE_BLOCK_SIZE
    </code>
    value by 1024. Table
        compression is not support for 32K and 64K
    <code class="literal">
     InnoDB
    </code>
    page sizes. For more information about
    <code class="literal">
     KEY_BLOCK_SIZE
    </code>
    , see
    <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
     <code class="literal">
      CREATE TABLE
     </code>
    </a>
    , and
    <a class="xref" href="innodb-compression-usage.html" title="17.9.1.2 Creating Compressed Tables">
     Section 17.9.1.2, “Creating Compressed Tables”
    </a>
    .
   </p>
   <div class="table">
    <a name="idm46045165437104">
    </a>
    <p class="title">
     <b>
      Table 17.3 Permitted Page Size, FILE_BLOCK_SIZE, and KEY_BLOCK_SIZE Combinations
for Compressed Tables
     </b>
    </p>
    <div class="table-contents">
     <table frame="all">
      <colgroup>
       <col style="width: 33%"/>
       <col style="width: 33%"/>
       <col style="width: 34%"/>
      </colgroup>
      <thead>
       <tr>
        <th scope="col">
         InnoDB Page Size (innodb_page_size)
        </th>
        <th scope="col">
         Permitted FILE_BLOCK_SIZE Value
        </th>
        <th scope="col">
         Permitted KEY_BLOCK_SIZE Value
        </th>
       </tr>
      </thead>
      <tbody>
       <tr>
        <th scope="row">
         64KB
        </th>
        <td>
         64K (65536)
        </td>
        <td>
         Compression is not supported
        </td>
       </tr>
       <tr>
        <th scope="row">
         32KB
        </th>
        <td>
         32K (32768)
        </td>
        <td>
         Compression is not supported
        </td>
       </tr>
       <tr>
        <th scope="row">
         16KB
        </th>
        <td>
         16K (16384)
        </td>
        <td>
         None. If
         <a class="link" href="innodb-parameters.html#sysvar_innodb_page_size">
          <code class="literal">
           innodb_page_size
          </code>
         </a>
         is equal to
         <code class="literal">
          FILE_BLOCK_SIZE
         </code>
         , the tablespace cannot
              contain a compressed table.
        </td>
       </tr>
       <tr>
        <th scope="row">
         16KB
        </th>
        <td>
         8K (8192)
        </td>
        <td>
         8
        </td>
       </tr>
       <tr>
        <th scope="row">
         16KB
        </th>
        <td>
         4K (4096)
        </td>
        <td>
         4
        </td>
       </tr>
       <tr>
        <th scope="row">
         16KB
        </th>
        <td>
         2K (2048)
        </td>
        <td>
         2
        </td>
       </tr>
       <tr>
        <th scope="row">
         16KB
        </th>
        <td>
         1K (1024)
        </td>
        <td>
         1
        </td>
       </tr>
       <tr>
        <th scope="row">
         8KB
        </th>
        <td>
         8K (8192)
        </td>
        <td>
         None. If
         <a class="link" href="innodb-parameters.html#sysvar_innodb_page_size">
          <code class="literal">
           innodb_page_size
          </code>
         </a>
         is equal to
         <code class="literal">
          FILE_BLOCK_SIZE
         </code>
         , the tablespace cannot
              contain a compressed table.
        </td>
       </tr>
       <tr>
        <th scope="row">
         8KB
        </th>
        <td>
         4K (4096)
        </td>
        <td>
         4
        </td>
       </tr>
       <tr>
        <th scope="row">
         8KB
        </th>
        <td>
         2K (2048)
        </td>
        <td>
         2
        </td>
       </tr>
       <tr>
        <th scope="row">
         8KB
        </th>
        <td>
         1K (1024)
        </td>
        <td>
         1
        </td>
       </tr>
       <tr>
        <th scope="row">
         4KB
        </th>
        <td>
         4K (4096)
        </td>
        <td>
         None. If
         <a class="link" href="innodb-parameters.html#sysvar_innodb_page_size">
          <code class="literal">
           innodb_page_size
          </code>
         </a>
         is equal to
         <code class="literal">
          FILE_BLOCK_SIZE
         </code>
         , the tablespace cannot
              contain a compressed table.
        </td>
       </tr>
       <tr>
        <th scope="row">
         4KB
        </th>
        <td>
         2K (2048)
        </td>
        <td>
         2
        </td>
       </tr>
       <tr>
        <th scope="row">
         4KB
        </th>
        <td>
         1K (1024)
        </td>
        <td>
         1
        </td>
       </tr>
      </tbody>
     </table>
    </div>
    <div class="table-contents">
     <table cellpadding="0" cellspacing="0" style="position: fixed; top: 0px; display: none; left: 401px; width: 739px;">
      <thead>
       <tr>
        <th scope="col" style="width: 243.531px;">
         InnoDB Page Size (innodb_page_size)
        </th>
        <th scope="col" style="width: 243.531px;">
         Permitted FILE_BLOCK_SIZE Value
        </th>
        <th scope="col" style="width: 250.938px;">
         Permitted KEY_BLOCK_SIZE Value
        </th>
       </tr>
      </thead>
     </table>
    </div>
   </div>
   <br class="table-break"/>
   <p>
    This example demonstrates creating a general tablespace and
        adding a compressed table. The example assumes a default
    <a class="link" href="innodb-parameters.html#sysvar_innodb_page_size">
     <code class="literal">
      innodb_page_size
     </code>
    </a>
    of 16KB. The
    <code class="literal">
     FILE_BLOCK_SIZE
    </code>
    of 8192 requires that the
        compressed table have a
    <code class="literal">
     KEY_BLOCK_SIZE
    </code>
    of 8.
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa46936737"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLESPACE</span> <span class="token punctuation">`</span>ts2<span class="token punctuation">`</span> <span class="token keyword">ADD</span> <span class="token keyword">DATAFILE</span> <span class="token string">'ts2.ibd'</span> <span class="token keyword">FILE_BLOCK_SIZE</span> <span class="token operator">=</span> <span class="token number">8192</span> <span class="token keyword">Engine</span><span class="token operator">=</span>InnoDB<span class="token punctuation">;</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t4 <span class="token punctuation">(</span>c1 <span class="token datatype">INT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">)</span> <span class="token keyword">TABLESPACE</span> ts2 <span class="token keyword">ROW_FORMAT</span><span class="token operator">=</span><span class="token keyword">COMPRESSED</span> <span class="token keyword">KEY_BLOCK_SIZE</span><span class="token operator">=</span><span class="token number">8</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    If you do not specify
    <code class="literal">
     FILE_BLOCK_SIZE
    </code>
    when
        creating a general tablespace,
    <code class="literal">
     FILE_BLOCK_SIZE
    </code>
    defaults to
    <a class="link" href="innodb-parameters.html#sysvar_innodb_page_size">
     <code class="literal">
      innodb_page_size
     </code>
    </a>
    . When
    <code class="literal">
     FILE_BLOCK_SIZE
    </code>
    is equal to
    <a class="link" href="innodb-parameters.html#sysvar_innodb_page_size">
     <code class="literal">
      innodb_page_size
     </code>
    </a>
    , the
        tablespace may only contain tables with an uncompressed row
        format (
    <code class="literal">
     COMPACT
    </code>
    ,
    <code class="literal">
     REDUNDANT
    </code>
    , and
    <code class="literal">
     DYNAMIC
    </code>
    row
        formats).
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="general-tablespaces-moving-non-partitioned-tables">
       </a>
       Moving Tables Between Tablespaces Using ALTER TABLE
      </h5>
     </div>
    </div>
   </div>
   <p>
    <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
     <code class="literal">
      ALTER TABLE
     </code>
    </a>
    with the
    <code class="literal">
     TABLESPACE
    </code>
    option can be used to move a table
        to an existing general tablespace, to a new file-per-table
        tablespace, or to the system tablespace.
   </p>
   <p>
    Adding table partitions to shared tablespaces is not supported.
        Shared tablespaces include the
    <code class="literal">
     InnoDB
    </code>
    system
        tablespace and general tablespaces.
   </p>
   <p>
    To move a table from a file-per-table tablespace or from the
        system tablespace to a general tablespace, specify the name of
        the general tablespace. The general tablespace must exist. See
    <a class="link" href="alter-tablespace.html" title="15.1.10 ALTER TABLESPACE Statement">
     <code class="literal">
      ALTER TABLESPACE
     </code>
    </a>
    for more
        information.
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa26357229"><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> tbl_name <span class="token keyword">TABLESPACE</span> <span class="token punctuation">[</span><span class="token operator">=</span><span class="token punctuation">]</span> <em class="replaceable">tablespace_name</em><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    To move a table from a general tablespace or file-per-table
        tablespace to the system tablespace, specify
    <code class="literal">
     innodb_system
    </code>
    as the tablespace name.
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa76237607"><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> tbl_name <span class="token keyword">TABLESPACE</span> <span class="token punctuation">[</span><span class="token operator">=</span><span class="token punctuation">]</span> innodb_system<span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    To move a table from the system tablespace or a general
        tablespace to a file-per-table tablespace, specify
    <code class="literal">
     innodb_file_per_table
    </code>
    as the tablespace name.
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa81104879"><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> tbl_name <span class="token keyword">TABLESPACE</span> <span class="token punctuation">[</span><span class="token operator">=</span><span class="token punctuation">]</span> innodb_file_per_table<span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    <code class="literal">
     ALTER TABLE ... TABLESPACE
    </code>
    operations cause a
        full table rebuild, even if the
    <code class="literal">
     TABLESPACE
    </code>
    attribute has not changed from its previous value.
   </p>
   <p>
    <code class="literal">
     ALTER TABLE ... TABLESPACE
    </code>
    syntax does not
        support moving a table from a temporary tablespace to a
        persistent tablespace.
   </p>
   <p>
    The
    <code class="literal">
     DATA DIRECTORY
    </code>
    clause is permitted with
    <code class="literal">
     CREATE TABLE ...
        TABLESPACE=innodb_file_per_table
    </code>
    but is otherwise not
        supported for use in combination with the
    <code class="literal">
     TABLESPACE
    </code>
    option. The directory specified in
        a
    <code class="literal">
     DATA DIRECTORY
    </code>
    clause must be known to
    <code class="literal">
     InnoDB
    </code>
    . For more information, see
    <a class="xref" href="innodb-create-table-external.html#innodb-create-table-external-data-directory" title="Using the DATA DIRECTORY Clause">
     Using the DATA DIRECTORY Clause
    </a>
    .
   </p>
   <p>
    Restrictions apply when moving tables from encrypted
        tablespaces. See
    <a class="xref" href="innodb-data-encryption.html#innodb-data-encryption-limitations" title="Encryption Limitations">
     Encryption Limitations
    </a>
    .
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="general-tablespaces-renaming">
       </a>
       Renaming a General Tablespace
      </h5>
     </div>
    </div>
   </div>
   <p>
    Renaming a general tablespace is supported using
    <a class="link" href="alter-tablespace.html" title="15.1.10 ALTER TABLESPACE Statement">
     <code class="literal">
      ALTER
        TABLESPACE ... RENAME TO
     </code>
    </a>
    syntax.
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa67453619"><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">TABLESPACE</span> s1 <span class="token keyword">RENAME</span> <span class="token keyword">TO</span> s2<span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    The
    <a class="link" href="privileges-provided.html#priv_create-tablespace">
     <code class="literal">
      CREATE TABLESPACE
     </code>
    </a>
    privilege
        is required to rename a general tablespace.
   </p>
   <p>
    <code class="literal">
     RENAME TO
    </code>
    operations are implicitly performed
        in
    <a class="link" href="server-system-variables.html#sysvar_autocommit">
     <code class="literal">
      autocommit
     </code>
    </a>
    mode regardless
        of the
    <a class="link" href="server-system-variables.html#sysvar_autocommit">
     <code class="literal">
      autocommit
     </code>
    </a>
    setting.
   </p>
   <p>
    A
    <code class="literal">
     RENAME TO
    </code>
    operation cannot be performed
        while
    <a class="link" href="lock-tables.html" title="15.3.6 LOCK TABLES and UNLOCK TABLES Statements">
     <code class="literal">
      LOCK TABLES
     </code>
    </a>
    or
    <a class="link" href="flush.html" title="15.7.8.3 FLUSH Statement">
     <code class="literal">
      FLUSH TABLES WITH READ
        LOCK
     </code>
    </a>
    is in effect for tables that reside in the
        tablespace.
   </p>
   <p>
    Exclusive
    <a class="link" href="glossary.html#glos_metadata_lock" title="metadata lock">
     metadata
        locks
    </a>
    are taken on tables within a general tablespace
        while the tablespace is renamed, which prevents concurrent DDL.
        Concurrent DML is supported.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="general-tablespaces-dropping">
       </a>
       Dropping a General Tablespace
      </h5>
     </div>
    </div>
   </div>
   <p>
    The
    <a class="link" href="drop-tablespace.html" title="15.1.33 DROP TABLESPACE Statement">
     <code class="literal">
      DROP TABLESPACE
     </code>
    </a>
    statement is
        used to drop an
    <code class="literal">
     InnoDB
    </code>
    general tablespace.
   </p>
   <p>
    All tables must be dropped from the tablespace prior to a
    <a class="link" href="drop-tablespace.html" title="15.1.33 DROP TABLESPACE Statement">
     <code class="literal">
      DROP TABLESPACE
     </code>
    </a>
    operation. If the
        tablespace is not empty,
    <a class="link" href="drop-tablespace.html" title="15.1.33 DROP TABLESPACE Statement">
     <code class="literal">
      DROP
        TABLESPACE
     </code>
    </a>
    returns an error.
   </p>
   <p>
    Use a query similar to the following to identify tables in a
        general tablespace.
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa28439679"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> a<span class="token punctuation">.</span><span class="token keyword">NAME</span> <span class="token keyword">AS</span> space_name<span class="token punctuation">,</span> b<span class="token punctuation">.</span><span class="token keyword">NAME</span> <span class="token keyword">AS</span> <span class="token keyword">table_name</span> <span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span>INNODB_TABLESPACES a<span class="token punctuation">,</span>
       INFORMATION_SCHEMA<span class="token punctuation">.</span>INNODB_TABLES b <span class="token keyword">WHERE</span> a<span class="token punctuation">.</span>SPACE<span class="token operator">=</span>b<span class="token punctuation">.</span>SPACE <span class="token operator">AND</span> a<span class="token punctuation">.</span><span class="token keyword">NAME</span> <span class="token operator">LIKE</span> <span class="token string">'ts1'</span><span class="token punctuation">;</span>
<span class="token output"><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><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><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></span>
<span class="token output"><span class="token punctuation">|</span> space_name <span class="token punctuation">|</span> table_name <span class="token punctuation">|</span></span>
<span class="token output"><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><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><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></span>
<span class="token output"><span class="token punctuation">|</span> ts1        <span class="token punctuation">|</span> test/t1    <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> ts1        <span class="token punctuation">|</span> test/t2    <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> ts1        <span class="token punctuation">|</span> test/t3    <span class="token punctuation">|</span></span>
<span class="token output"><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><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><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></span></code></pre>
   </div>
   <p>
    A general
    <code class="literal">
     InnoDB
    </code>
    tablespace is not deleted
        automatically when the last table in the tablespace is dropped.
        The tablespace must be dropped explicitly using
    <a class="link" href="drop-tablespace.html" title="15.1.33 DROP TABLESPACE Statement">
     <code class="literal">
      DROP TABLESPACE
      <em class="replaceable">
       <code>
        tablespace_name
       </code>
      </em>
     </code>
    </a>
    .
   </p>
   <p>
    A general tablespace does not belong to any particular database.
        A
    <a class="link" href="drop-database.html" title="15.1.24 DROP DATABASE Statement">
     <code class="literal">
      DROP DATABASE
     </code>
    </a>
    operation can
        drop tables that belong to a general tablespace but it cannot
        drop the tablespace, even if the
    <a class="link" href="drop-database.html" title="15.1.24 DROP DATABASE Statement">
     <code class="literal">
      DROP
        DATABASE
     </code>
    </a>
    operation drops all tables that belong to the
        tablespace.
   </p>
   <p>
    Similar to the system tablespace, truncating or dropping tables
        stored in a general tablespace creates free space internally in
        the general tablespace
    <a class="link" href="glossary.html#glos_ibd_file" title=".ibd file">
     .ibd data
        file
    </a>
    which can only be used for new
    <code class="literal">
     InnoDB
    </code>
    data. Space is not released back to
        the operating system as it is when a file-per-table tablespace
        is deleted during a
    <a class="link" href="drop-table.html" title="15.1.32 DROP TABLE Statement">
     <code class="literal">
      DROP TABLE
     </code>
    </a>
    operation.
   </p>
   <p>
    This example demonstrates how to drop an
    <code class="literal">
     InnoDB
    </code>
    general tablespace. The general
        tablespace
    <code class="literal">
     ts1
    </code>
    is created with a single
        table. The table must be dropped before dropping the tablespace.
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa34621784"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLESPACE</span> <span class="token punctuation">`</span>ts1<span class="token punctuation">`</span> <span class="token keyword">ADD</span> <span class="token keyword">DATAFILE</span> <span class="token string">'ts1.ibd'</span> <span class="token keyword">Engine</span><span class="token operator">=</span>InnoDB<span class="token punctuation">;</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t1 <span class="token punctuation">(</span>c1 <span class="token datatype">INT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">)</span> <span class="token keyword">TABLESPACE</span> ts1 <span class="token keyword">Engine</span><span class="token operator">=</span>InnoDB<span class="token punctuation">;</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">DROP</span> <span class="token keyword">TABLE</span> t1<span class="token punctuation">;</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">DROP</span> <span class="token keyword">TABLESPACE</span> ts1<span class="token punctuation">;</span></code></pre>
   </div>
   <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
    <div class="admon-title">
     Note
    </div>
    <p>
     <code class="literal">
      <em class="replaceable">
       <code>
        tablespace_name
       </code>
      </em>
     </code>
     is a case-sensitive identifier in MySQL.
    </p>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="general-tablespaces-limitations">
       </a>
       General Tablespace Limitations
      </h5>
     </div>
    </div>
   </div>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       A generated or existing tablespace cannot be changed to a
            general tablespace.
      </p>
     </li>
     <li class="listitem">
      <p>
       Creation of temporary general tablespaces is not supported.
      </p>
     </li>
     <li class="listitem">
      <p>
       General tablespaces do not support temporary tables.
      </p>
     </li>
     <li class="listitem">
      <p>
       Similar to the system tablespace, truncating or dropping
            tables stored in a general tablespace creates free space
            internally in the general tablespace
       <a class="link" href="glossary.html#glos_ibd_file" title=".ibd file">
        .ibd data file
       </a>
       which
            can only be used for new
       <code class="literal">
        InnoDB
       </code>
       data.
            Space is not released back to the operating system as it is
            for
       <a class="link" href="glossary.html#glos_file_per_table" title="file-per-table">
        file-per-table
       </a>
       tablespaces.
      </p>
      <p>
       Additionally, a table-copying
       <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
        <code class="literal">
         ALTER
            TABLE
        </code>
       </a>
       operation on table that resides in a shared
            tablespace (a general tablespace or the system tablespace)
            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. The additional space required for
            the table-copying
       <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
        <code class="literal">
         ALTER TABLE
        </code>
       </a>
       operation is not released back to the operating system as it
            is for file-per-table tablespaces.
      </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 ...
            DISCARD TABLESPACE
        </code>
       </a>
       and
       <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
        <code class="literal">
         ALTER TABLE
            ...IMPORT TABLESPACE
        </code>
       </a>
       are not supported for tables
            that belong to a general tablespace.
      </p>
     </li>
     <li class="listitem">
      <p>
       Placing table partitions in general tablespaces is not
            supported.
      </p>
     </li>
     <li class="listitem">
      <p>
       The
       <code class="literal">
        ADD DATAFILE
       </code>
       clause is not supported
            in a replication environment where the source and replica
            reside on the same host, as it would cause the source and
            replica to create a tablespace of the same name in the same
            location, which is not supported. However, if the
       <code class="literal">
        ADD DATAFILE
       </code>
       clause is omitted, the
            tablespace is created in the data directory with a generated
            file name that is unique, which is permitted.
      </p>
     </li>
     <li class="listitem">
      <p>
       General tablespaces cannot be created in the undo tablespace
            directory
            (
       <a class="link" href="innodb-parameters.html#sysvar_innodb_undo_directory">
        <code class="literal">
         innodb_undo_directory
        </code>
       </a>
       )
            unless that directly is known to
       <code class="literal">
        InnoDB
       </code>
       .
            Known directories are those defined by the
       <a class="link" href="server-system-variables.html#sysvar_datadir">
        <code class="literal">
         datadir
        </code>
       </a>
       ,
       <a class="link" href="innodb-parameters.html#sysvar_innodb_data_home_dir">
        <code class="literal">
         innodb_data_home_dir
        </code>
       </a>
       , and
       <a class="link" href="innodb-parameters.html#sysvar_innodb_directories">
        <code class="literal">
         innodb_directories
        </code>
       </a>
       variables.
      </p>
     </li>
    </ul>
   </div>
  </div>
 </div>
 <br/>
</div>
