Movatterモバイル変換

• General Tablespace Capabilities

• Creating a General Tablespace

• Adding Tables to a General Tablespace

• General Tablespace Row Format Support

• Moving Tables Between Tablespaces Using ALTER TABLE

• Renaming a General Tablespace

• Dropping a General Tablespace

• General Tablespace Limitations

General tablespaces provide the following capabilities:

General tablespaces are created usingCREATE TABLESPACE syntax.

CREATE TABLESPACEtablespace_name    [ADD DATAFILE 'file_name']    [FILE_BLOCK_SIZE =value]        [ENGINE [=]engine_name]

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 toInnoDB prior to creating the tablespace. To make an unknown directory known toInnoDB, add the directory to theinnodb_directories argument value.innodb_directories is a read-only startup option. Configuring it requires restarting the server.

Examples:

Creating a general tablespace in the data directory:

mysql> CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' Engine=InnoDB;

or

mysql> CREATE TABLESPACE `ts1` Engine=InnoDB;

TheADD DATAFILE clause is optional. If theADD DATAFILE 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 (aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee). General tablespace data files include an.ibd 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.

Creating a general tablespace in a directory outside of the data directory:

mysql> CREATE TABLESPACE `ts1` ADD DATAFILE '/my/tablespace/directory/ts1.ibd' Engine=InnoDB;

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, themy_tablespace directory is at the same level as the data directory:

mysql> CREATE TABLESPACE `ts1` ADD DATAFILE '../my_tablespace/ts1.ibd' Engine=InnoDB;

After creating a general tablespace,CREATE TABLEtbl_name ... TABLESPACE [=]tablespace_name orALTER TABLEtbl_name TABLESPACE [=]tablespace_name statements can be used to add tables to the tablespace, as shown in the following examples:

CREATE TABLE:

mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1;

ALTER TABLE:

mysql> ALTER TABLE t2 TABLESPACE ts1;

Adding table partitions to shared tablespaces is not supported. Shared tablespaces include theInnoDB system tablespace and general tablespaces.

For detailed syntax information, seeCREATE TABLE andALTER TABLE.

General tablespaces support all table row formats (REDUNDANT,COMPACT,DYNAMIC,COMPRESSED) with the caveat that compressed and uncompressed tables cannot coexist in the same general tablespace due to different physical page sizes.

For a general tablespace to contain compressed tables (ROW_FORMAT=COMPRESSED), theFILE_BLOCK_SIZE option must be specified, and theFILE_BLOCK_SIZE value must be a valid compressed page size in relation to theinnodb_page_size value. Also, the physical page size of the compressed table (KEY_BLOCK_SIZE) must be equal toFILE_BLOCK_SIZE/1024. For example, ifinnodb_page_size=16KB andFILE_BLOCK_SIZE=8K, theKEY_BLOCK_SIZE of the table must be 8.

The following table shows permittedinnodb_page_size,FILE_BLOCK_SIZE, andKEY_BLOCK_SIZE combinations.FILE_BLOCK_SIZE values may also be specified in bytes. To determine a validKEY_BLOCK_SIZE value for a givenFILE_BLOCK_SIZE, divide theFILE_BLOCK_SIZE value by 1024. Table compression is not support for 32K and 64KInnoDB page sizes. For more information aboutKEY_BLOCK_SIZE, seeCREATE TABLE, andSection 17.9.1.2, “Creating Compressed Tables”.

This example demonstrates creating a general tablespace and adding a compressed table. The example assumes a defaultinnodb_page_size of 16KB. TheFILE_BLOCK_SIZE of 8192 requires that the compressed table have aKEY_BLOCK_SIZE of 8.

mysql> CREATE TABLESPACE `ts2` ADD DATAFILE 'ts2.ibd' FILE_BLOCK_SIZE = 8192 Engine=InnoDB;mysql> CREATE TABLE t4 (c1 INT PRIMARY KEY) TABLESPACE ts2 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

If you do not specifyFILE_BLOCK_SIZE when creating a general tablespace,FILE_BLOCK_SIZE defaults toinnodb_page_size. WhenFILE_BLOCK_SIZE is equal toinnodb_page_size, the tablespace may only contain tables with an uncompressed row format (COMPACT,REDUNDANT, andDYNAMIC row formats).

ALTER TABLE with theTABLESPACE 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.

Adding table partitions to shared tablespaces is not supported. Shared tablespaces include theInnoDB system tablespace and general tablespaces.

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. SeeALTER TABLESPACE for more information.

ALTER TABLE tbl_name TABLESPACE [=]tablespace_name;

To move a table from a general tablespace or file-per-table tablespace to the system tablespace, specifyinnodb_system as the tablespace name.

ALTER TABLE tbl_name TABLESPACE [=] innodb_system;

To move a table from the system tablespace or a general tablespace to a file-per-table tablespace, specifyinnodb_file_per_table as the tablespace name.

ALTER TABLE tbl_name TABLESPACE [=] innodb_file_per_table;

ALTER TABLE ... TABLESPACE operations cause a full table rebuild, even if theTABLESPACE attribute has not changed from its previous value.

ALTER TABLE ... TABLESPACE syntax does not support moving a table from a temporary tablespace to a persistent tablespace.

TheDATA DIRECTORY clause is permitted withCREATE TABLE ... TABLESPACE=innodb_file_per_table but is otherwise not supported for use in combination with theTABLESPACE option. The directory specified in aDATA DIRECTORY clause must be known toInnoDB. For more information, seeUsing the DATA DIRECTORY Clause.

Restrictions apply when moving tables from encrypted tablespaces. SeeEncryption Limitations.

Renaming a general tablespace is supported usingALTER TABLESPACE ... RENAME TO syntax.

ALTER TABLESPACE s1 RENAME TO s2;

TheCREATE TABLESPACE privilege is required to rename a general tablespace.

RENAME TO operations are implicitly performed inautocommit mode regardless of theautocommit setting.

ARENAME TO operation cannot be performed whileLOCK TABLES orFLUSH TABLES WITH READ LOCK is in effect for tables that reside in the tablespace.

Exclusivemetadata locks are taken on tables within a general tablespace while the tablespace is renamed, which prevents concurrent DDL. Concurrent DML is supported.

TheDROP TABLESPACE statement is used to drop anInnoDB general tablespace.

All tables must be dropped from the tablespace prior to aDROP TABLESPACE operation. If the tablespace is not empty,DROP TABLESPACE returns an error.

Use a query similar to the following to identify tables in a general tablespace.

mysql> SELECT a.NAME AS space_name, b.NAME AS table_name FROM INFORMATION_SCHEMA.INNODB_TABLESPACES a,       INFORMATION_SCHEMA.INNODB_TABLES b WHERE a.SPACE=b.SPACE AND a.NAME LIKE 'ts1';+------------+------------+| space_name | table_name |+------------+------------+| ts1        | test/t1    || ts1        | test/t2    || ts1        | test/t3    |+------------+------------+

A generalInnoDB tablespace is not deleted automatically when the last table in the tablespace is dropped. The tablespace must be dropped explicitly usingDROP TABLESPACEtablespace_name.

A general tablespace does not belong to any particular database. ADROP DATABASE operation can drop tables that belong to a general tablespace but it cannot drop the tablespace, even if theDROP DATABASE operation drops all tables that belong to the tablespace.

Similar to the system tablespace, truncating or dropping tables stored in a general tablespace creates free space internally in the general tablespace.ibd data file which can only be used for newInnoDB data. Space is not released back to the operating system as it is when a file-per-table tablespace is deleted during aDROP TABLE operation.

This example demonstrates how to drop anInnoDB general tablespace. The general tablespacets1 is created with a single table. The table must be dropped before dropping the tablespace.

mysql> CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' Engine=InnoDB;mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1 Engine=InnoDB;mysql> DROP TABLE t1;mysql> DROP TABLESPACE ts1;