Rechercher dans le manuel MySQL
13.1.21 CREATE TABLESPACE Syntax
This statement is used to create a tablespace. The precise syntax
and semantics depend on the storage engine used. In standard MySQL
releases, this is always an InnoDB
tablespace. MySQL NDB Cluster also supports tablespaces using the
NDB
storage engine.
Considerations for InnoDB
CREATE TABLESPACE
syntax is used to
create general tablespaces or undo tablespaces. The
UNDO
keyword, introduced in MySQL 8.0.14, must
be specified to create an undo tablespace.
A general tablespace is a shared tablespace. It can hold multiple tables, and supports all table row formats. General tablespaces can be created in a location relative to or independent of the data directory.
After creating an InnoDB
general tablespace,
use CREATE TABLE
or
tbl_name
... TABLESPACE [=]
tablespace_name
ALTER TABLE
to add tables
to the tablespace. For more information, see
Section 15.6.3.3, “General Tablespaces”.
tbl_name
TABLESPACE [=]
tablespace_name
Undo tablespaces contain undo logs. Undo tablespaces can be created in a chosen location by specifying a fully qualified data file path. For more information, see Section 15.6.3.4, “Undo Tablespaces”.
Considerations for NDB Cluster
This statement is used to create a tablespace, which can contain
one or more data files, providing storage space for NDB Cluster
Disk Data tables (see Section 22.5.13, “NDB Cluster Disk Data Tables”).
One data file is created and added to the tablespace using this
statement. Additional data files may be added to the tablespace by
using the ALTER TABLESPACE
statement (see Section 13.1.10, “ALTER TABLESPACE Syntax”).
All NDB Cluster Disk Data objects share the same namespace. This means that each Disk Data object must be uniquely named (and not merely each Disk Data object of a given type). For example, you cannot have a tablespace and a log file group with the same name, or a tablespace and a data file with the same name.
A log file group of one or more UNDO
log files
must be assigned to the tablespace to be created with the
USE LOGFILE GROUP
clause.
logfile_group
must be an existing log
file group created with CREATE LOGFILE
GROUP
(see Section 13.1.16, “CREATE LOGFILE GROUP Syntax”).
Multiple tablespaces may use the same log file group for
UNDO
logging.
When setting EXTENT_SIZE
or
INITIAL_SIZE
, you may optionally follow the
number with a one-letter abbreviation for an order of magnitude,
similar to those used in my.cnf
. Generally,
this is one of the letters M
(for megabytes) or
G
(for gigabytes).
INITIAL_SIZE
and EXTENT_SIZE
are subject to rounding as follows:
EXTENT_SIZE
is rounded up to the nearest whole multiple of 32K.INITIAL_SIZE
is rounded down to the nearest whole multiple of 32K; this result is rounded up to the nearest whole multiple ofEXTENT_SIZE
(after any rounding).
The rounding just described is done explicitly, and a warning is
issued by the MySQL Server when any such rounding is performed.
The rounded values are also used by the NDB kernel for calculating
INFORMATION_SCHEMA.FILES
column
values and other purposes. However, to avoid an unexpected result,
we suggest that you always use whole multiples of 32K in
specifying these options.
When CREATE TABLESPACE
is used with
ENGINE [=] NDB
, a tablespace and associated
data file are created on each Cluster data node. You can verify
that the data files were created and obtain information about them
by querying the
INFORMATION_SCHEMA.FILES
table. (See
the example later in this section.)
(See Section 25.11, “The INFORMATION_SCHEMA FILES Table”.)
Options
ADD DATAFILE
: Defines the name of a tablespace data file. TheADD DATAFILE
clause is required when creating undo tablespaces. Otherwise, it is optional as of MySQL 8.0.14.An
InnoDB
tablespace supports only a single data file, whose name must include a.ibd
extension. An NDB Cluster tablespace supports multiple data files which can have any legal file names; more data files can be added to an NDB Cluster tablespace following its creation by using anALTER TABLESPACE
statement.To place a general tablespace data file in a location outside of the data directory, include a fully qualified path or a path relative to the data directory. Only a fully qualified path is permitted for undo tablespaces. If you do not specify a path, a general tablespace is created in the data directory. An undo tablespace created without specifying a path is created in the directory defined by the
innodb_undo_directory
variable. If theinnodb_undo_directory
variable is undefined, undo tablespaces are created in the data directory.Creating a general tablespace in a subdirectory under the data directory is not supported to avoid conflicts with implicitly created file-per-table tablespaces. When creating a general tablespace or undo tablespace outside of the data directory, the directory must exist and must be known to
InnoDB
prior to creating the tablespace. To make a directory known toInnoDB
, add it to theinnodb_directories
value or to one of the variables whose values are appended to theinnodb_directories
value.innodb_directories
is a read-only variable. Configuring it requires restarting the server.The
, including any specified path, must be quoted with single or double quotations marks. File names (not counting the file extension) and directory names must be at least one byte in length. Zero length file names and directory names are not supported.file_name
If the
ADD 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
). A file extension is added if required by the storage engine. An.ibd
file extension is added forInnoDB
general tablespace data files. In a replication environment, the data file name created on the master is not the same as the data file name created on the slave.As of MySQL 8.0.17, the
ADD DATAFILE
clause does not permit circular directory references when creating anInnoDB
tablespace. For example, the circular directory reference (/../
) in the following statement is not permitted:An exception to this restriction exists on Linux, where a circular directory reference is permitted if the preceding directory is a symbolic link. For example, the data file path in the example above is permitted if
any_directory
is a symbolic link. (It is still permitted for data file paths to begin with '../
'.)FILE_BLOCK_SIZE
: This option—which is specific toInnoDB
general tablespaces, and is ignored byNDB
—defines the block size for the tablespace data file. Values can be specified in bytes or kilobytes. For example, an 8 kilobyte file block size can be specified as 8192 or 8K. If you do not specify this option,FILE_BLOCK_SIZE
defaults to theinnodb_page_size
value.FILE_BLOCK_SIZE
is required when you intend to use the tablespace for storing compressedInnoDB
tables (ROW_FORMAT=COMPRESSED
). In this case, you must define the tablespaceFILE_BLOCK_SIZE
when creating the tablespace.If
FILE_BLOCK_SIZE
is equal theinnodb_page_size
value, the tablespace can contain only tables having an uncompressed row format (COMPACT
,REDUNDANT
, andDYNAMIC
). Tables with aCOMPRESSED
row format have a different physical page size than uncompressed tables. Therefore, compressed tables cannot coexist in the same tablespace as uncompressed tables.For a general tablespace to contain compressed tables,
FILE_BLOCK_SIZE
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=16K
, andFILE_BLOCK_SIZE=8K
, theKEY_BLOCK_SIZE
of the table must be 8. For more information, see Section 15.6.3.3, “General Tablespaces”.USE LOGFILE GROUP
: Required forNDB
, this is the name of a log file group previously created usingCREATE LOGFILE GROUP
. Not supported forInnoDB
, where it fails with an error.EXTENT_SIZE
: This option is specific to NDB, and is not supported by InnoDB, where it fails with an error.EXTENT_SIZE
sets the size, in bytes, of the extents used by any files belonging to the tablespace. The default value is 1M. The minimum size is 32K, and theoretical maximum is 2G, although the practical maximum size depends on a number of factors. In most cases, changing the extent size does not have any measurable effect on performance, and the default value is recommended for all but the most unusual situations.An extent is a unit of disk space allocation. One extent is filled with as much data as that extent can contain before another extent is used. In theory, up to 65,535 (64K) extents may used per data file; however, the recommended maximum is 32,768 (32K). The recommended maximum size for a single data file is 32G—that is, 32K extents × 1 MB per extent. In addition, once an extent is allocated to a given partition, it cannot be used to store data from a different partition; an extent cannot store data from more than one partition. This means, for example that a tablespace having a single datafile whose
INITIAL_SIZE
(described in the following item) is 256 MB and whoseEXTENT_SIZE
is 128M has just two extents, and so can be used to store data from at most two different disk data table partitions.You can see how many extents remain free in a given data file by querying the
INFORMATION_SCHEMA.FILES
table, and so derive an estimate for how much space remains free in the file. For further discussion and examples, see Section 25.11, “The INFORMATION_SCHEMA FILES Table”.INITIAL_SIZE
: This option is specific toNDB
, and is not supported byInnoDB
, where it fails with an error.The
INITIAL_SIZE
parameter sets the total size in bytes of the data file that was specific usingADD DATATFILE
. Once this file has been created, its size cannot be changed; however, you can add more data files to the tablespace usingALTER TABLESPACE ... ADD DATAFILE
.INITIAL_SIZE
is optional; its default value is 134217728 (128 MB).On 32-bit systems, the maximum supported value for
INITIAL_SIZE
is 4294967296 (4 GB).AUTOEXTEND_SIZE
: Currently ignored by MySQL; reserved for possible future use. Has no effect in any release of MySQL 8.0 or MySQL NDB Cluster 8.0, regardless of the storage engine used.MAX_SIZE
: Currently ignored by MySQL; reserved for possible future use. Has no effect in any release of MySQL 8.0 or MySQL NDB Cluster 8.0, regardless of the storage engine used.NODEGROUP
: Currently ignored by MySQL; reserved for possible future use. Has no effect in any release of MySQL 8.0 or MySQL NDB Cluster 8.0, regardless of the storage engine used.WAIT
: Currently ignored by MySQL; reserved for possible future use. Has no effect in any release of MySQL 8.0 or MySQL NDB Cluster 8.0, regardless of the storage engine used.COMMENT
: Currently ignored by MySQL; reserved for possible future use. Has no effect in any release of MySQL 8.0 or MySQL NDB Cluster 8.0, regardless of the storage engine used.The
ENCRYPTION
clause enables or disables page-level data encryption for anInnoDB
general tablespace. Encryption support for general tablespaces was introduced in MySQL 8.0.13.As of MySQL 8.0.16, if the
ENCRYPTION
clause is not specified, thedefault_table_encryption
setting controls whether encryption is enabled. TheENCRYPTION
clause overrides thedefault_table_encryption
setting. However, if thetable_encryption_privilege_check
variable is enabled, theTABLE_ENCRYPTION_ADMIN
privilege is required to use anENCRYPTION
clause setting that differs from thedefault_table_encryption
setting.A keyring plugin must be installed and configured before an encryption-enabled tablespace can be created.
When a general tablespace is encrypted, all tables residing in the tablespace are encrypted. Likewise, a table created in an encrypted tablespace is encrypted.
For more information, see Section 15.6.3.9, “InnoDB Data-at-Rest Encryption”
ENGINE
: Defines the storage engine which uses the tablespace, whereengine_name
is the name of the storage engine. Currently, only theInnoDB
storage engine is supported by standard MySQL 8.0 releases. MySQL NDB Cluster supports bothNDB
andInnoDB
tablespaces. The value of thedefault_storage_engine
system variable is used forENGINE
if the option is not specified.
Notes
For the rules covering the naming of MySQL tablespaces, see Section 9.2, “Schema Object Names”. In addition to these rules, the slash character (“/”) is not permitted, nor can you use names beginning with
innodb_
, as this prefix is reserved for system use.Creation of temporary general tablespaces is not supported.
General tablespaces do not support temporary tables.
The
TABLESPACE
option may be used withCREATE TABLE
orALTER TABLE
to assign anInnoDB
table partition or subpartition to a file-per-table tablespace. All partitions must belong to the same storage engine. Assigning table partitions to sharedInnoDB
tablespaces is not supported. Shared tablespaces include theInnoDB
system tablespace and general tablespaces.General tablespaces support the addition of tables of any row format using
CREATE TABLE ... TABLESPACE
.innodb_file_per_table
does not need to be enabled.innodb_strict_mode
is not applicable to general tablespaces. Tablespace management rules are strictly enforced independently ofinnodb_strict_mode
. IfCREATE TABLESPACE
parameters are incorrect or incompatible, the operation fails regardless of theinnodb_strict_mode
setting. When a table is added to a general tablespace usingCREATE TABLE ... TABLESPACE
orALTER TABLE ... TABLESPACE
,innodb_strict_mode
is ignored but the statement is evaluated as ifinnodb_strict_mode
is enabled.Use
DROP TABLESPACE
to remove a tablespace. All tables must be dropped from a tablespace usingDROP TABLE
prior to dropping the tablespace. Before dropping an NDB Cluster tablespace you must also remove all its data files using one or moreALTER TABLESPACE ... DROP DATATFILE
statements. See Section 22.5.13.1, “NDB Cluster Disk Data Objects”.All parts of an
InnoDB
table added to anInnoDB
general tablespace reside in the general tablespace, including indexes andBLOB
pages.For an
NDB
table assigned to a tablespace, only those columns which are not indexed are stored on disk, and actually use the tablespace data files. Indexes and indexed columns for allNDB
tables are always kept in memory.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 new
InnoDB
data. Space is not released back to the operating system as it is for file-per-table tablespaces.A general tablespace is not associated with any database or schema.
ALTER TABLE ... DISCARD TABLESPACE
andALTER TABLE ...IMPORT TABLESPACE
are not supported for tables that belong to a general tablespace.The server uses tablespace-level metadata locking for DDL that references general tablespaces. By comparison, the server uses table-level metadata locking for DDL that references file-per-table tablespaces.
A generated or existing tablespace cannot be changed to a general tablespace.
There is no conflict between general tablespace names and file-per-table tablespace names. The “/” character, which is present in file-per-table tablespace names, is not permitted in general tablespace names.
mysqldump and mysqlpump do not dump
InnoDB
CREATE TABLESPACE
statements.
InnoDB Examples
This example demonstrates creating a general tablespace and adding three uncompressed tables of different row formats.
This example demonstrates creating a general tablespace and adding
a compressed table. The example assumes a default
innodb_page_size
value of 16K.
The FILE_BLOCK_SIZE
of 8192 requires that the
compressed table have a KEY_BLOCK_SIZE
of 8.
This example demonstrates creating a general tablespace without
specifying the ADD DATAFILE
clause, which is
optional as of MySQL 8.0.14.
This example demonstrates creating an undo tablespace.
NDB Example
Suppose that you wish to create an NDB Cluster Disk Data
tablespace named myts
using a datafile named
mydata-1.dat
. An NDB
tablespace always requires the use of a log file group consisting
of one or more undo log files. For this example, we first create a
log file group named mylg
that contains one
undo long file named myundo-1.dat
, using the
CREATE LOGFILE GROUP
statement
shown here:
- mysql> CREATE LOGFILE GROUP myg1
- Query OK, 0 rows affected (3.29 sec)
Now you can create the tablespace previously described using the following statement:
- -> USE LOGFILE GROUP mylg
- Query OK, 0 rows affected (2.98 sec)
You can now create a Disk Data table using a
CREATE TABLE
statement with the
TABLESPACE
and STORAGE DISK
options, similar to what is shown here:
- -> )
- -> TABLESPACE myts STORAGE DISK
- Query OK, 0 rows affected (1.41 sec)
It is important to note that only the dob
and
joined
columns from mytable
are actually stored on disk, due to the fact that the
id
, lname
, and
fname
columns are all indexed.
As mentioned previously, when CREATE TABLESPACE
is used with ENGINE [=] NDB
, a tablespace and
associated data file are created on each NDB Cluster data node.
You can verify that the data files were created and obtain
information about them by querying the
INFORMATION_SCHEMA.FILES
table, as
shown here:
- -> FROM INFORMATION_SCHEMA.FILES
- +--------------+------------+--------------------+--------+----------------+
- +--------------+------------+--------------------+--------+----------------+
- | mydata-1.dat | DATAFILE | mylg | NORMAL | CLUSTER_NODE=5 |
- | mydata-1.dat | DATAFILE | mylg | NORMAL | CLUSTER_NODE=6 |
- +--------------+------------+--------------------+--------+----------------+
For additional information and examples, see Section 22.5.13.1, “NDB Cluster Disk Data Objects”.
Nederlandse vertaling
U hebt gevraagd om deze site in het Nederlands te bezoeken. Voor nu wordt alleen de interface vertaald, maar nog niet alle inhoud.Als je me wilt helpen met vertalingen, is je bijdrage welkom. Het enige dat u hoeft te doen, is u op de site registreren en mij een bericht sturen waarin u wordt gevraagd om u toe te voegen aan de groep vertalers, zodat u de gewenste pagina's kunt vertalen. Een link onderaan elke vertaalde pagina geeft aan dat u de vertaler bent en heeft een link naar uw profiel.
Bij voorbaat dank.
Document heeft de 26/06/2006 gemaakt, de laatste keer de 26/10/2018 gewijzigd
Bron van het afgedrukte document:https://www.gaudry.be/nl/mysql-rf-create-tablespace.html
De infobrol is een persoonlijke site waarvan de inhoud uitsluitend mijn verantwoordelijkheid is. De tekst is beschikbaar onder CreativeCommons-licentie (BY-NC-SA). Meer info op de gebruiksvoorwaarden en de auteur.
Referenties
Deze verwijzingen en links verwijzen naar documenten die geraadpleegd zijn tijdens het schrijven van deze pagina, of die aanvullende informatie kunnen geven, maar de auteurs van deze bronnen kunnen niet verantwoordelijk worden gehouden voor de inhoud van deze pagina.
De auteur Deze site is als enige verantwoordelijk voor de manier waarop de verschillende concepten, en de vrijheden die met de referentiewerken worden genomen, hier worden gepresenteerd. Vergeet niet dat u meerdere broninformatie moet doorgeven om het risico op fouten te verkleinen.