
where an explicit character set is not specified. If this option is omitted or
DEFAULT is specified, database's default character set will be used. See
Setting Character Sets and Collations for details on setting the character
sets.

CHECKSUM/TABLE_CHECKSUM
-----------------------

CHECKSUM (or TABLE_CHECKSUM) can be set to 1 to maintain a live checksum for
all table's rows. This makes write operations slower, but CHECKSUM TABLE will
be very fast. This option is only supported for MyISAM and Aria tables.

[DEFAULT] COLLATE
-----------------

[DEFAULT] COLLATE is used to set a default collation for the table. This is
the collation used for all columns where an explicit character set is not
specified. If this option is omitted or DEFAULT is specified, database's
default option will be used. See Setting Character Sets and Collations for
details on setting the collations

COMMENT
-------

COMMENT is a comment for the table. The maximum length is 2048 characters.
Also used to define table parameters when creating a Spider table.

CONNECTION
----------

CONNECTION is used to specify a server name or a connection string for a
Spider, CONNECT, Federated or FederatedX table.

DATA DIRECTORY/INDEX DIRECTORY
------------------------------

DATA DIRECTORY and INDEX DIRECTORY are supported for MyISAM and Aria, and DATA
DIRECTORY is also supported by InnoDB if the innodb_file_per_table server
system variable is enabled, but only in CREATE TABLE, not in ALTER TABLE. So,
carefully choose a path for InnoDB tables at creation time, because it cannot
be changed without dropping and re-creating the table. These options specify
the paths for data files and index files, respectively. If these options are
omitted, the database's directory will be used to store data files and index
files. Note that these table options do not work for partitioned tables (use
the partition options instead), or if the server has been invoked with the
--skip-symbolic-links startup option. To avoid the overwriting of old files
with the same name that could be present in the directories, you can use the
--keep_files_on_create option (an error will be issued if files already
exist). These options are ignored if the NO_DIR_IN_CREATE SQL_MODE is enabled
(useful for replication slaves). Also note that symbolic links cannot be used
for InnoDB tables.

DATA DIRECTORY works by creating symlinks from where the table would normally
have been (inside the datadir) to where the option specifies. For security
reasons, to avoid bypassing the privilege system, the server does not permit
symlinks inside the datadir. Therefore, DATA DIRECTORY cannot be used to
specify a location inside the datadir. An attempt to do so will result in an
error 1210 (HY000) Incorrect arguments to DATA DIRECTORY.

DELAY_KEY_WRITE
---------------

DELAY_KEY_WRITE is supported by MyISAM and Aria, and can be set to 1 to speed
up write operations. In that case, when data are modified, the indexes are not
updated until the table is closed. Writing the changes to the index file
altogether can be much faster. However, note that this option is applied only
if the delay_key_write server variable is set to 'ON'. If it is 'OFF' the
delayed index writes are always disabled, and if it is 'ALL' the delayed index
writes are always used, disregarding the value of DELAY_KEY_WRITE.

ENCRYPTED
---------

The ENCRYPTED table option can be used to manually set the encryption status
of an InnoDB table. See InnoDB Encryption for more information.

Aria does not support the ENCRYPTED table option. See MDEV-18049.

See Data-at-Rest Encryption for more information.

ENCRYPTION_KEY_ID
-----------------

The ENCRYPTION_KEY_ID table option can be used to manually set the encryption
key of an InnoDB table. See InnoDB Encryption for more information.

Aria does not support the ENCRYPTION_KEY_ID table option. See MDEV-18049.

See Data-at-Rest Encryption for more information.

IETF_QUOTES
-----------

For the CSV storage engine, the IETF_QUOTES option, when set to YES, enables
IETF-compatible parsing of embedded quote and comma characters. Enabling this
option for a table improves compatibility with other tools that use CSV, but
is not compatible with MySQL CSV tables, or MariaDB CSV tables created without
this option. Disabled by default.

INSERT_METHOD
-------------

INSERT_METHOD is only used with MERGE tables. This option determines in which
underlying table the new rows should be inserted. If you set it to 'NO' (which
is the default) no new rows can be added to the table (but you will still be
able to perform INSERTs directly against the underlying tables). FIRST means
that the rows are inserted into the first table, and LAST means that thet are
inserted into the last table.

KEY_BLOCK_SIZE
--------------

KEY_BLOCK_SIZE is used to determine the size of key blocks, in bytes or
kilobytes. However, this value is just a hint, and the storage engine could
modify or ignore it. If KEY_BLOCK_SIZE is set to 0, the storage engine's
default value will be used.

With the InnoDB storage engine, if you specify a non-zero value for the
KEY_BLOCK_SIZE table option for the whole table, then the table will
implicitly be created with the ROW_FORMAT table option set to COMPRESSED.

MIN_ROWS/MAX_ROWS
-----------------

MIN_ROWS and MAX_ROWS let the storage engine know how many rows you are
planning to store as a minimum and as a maximum. These values will not be used
as real limits, but they help the storage engine to optimize the table.
MIN_ROWS is only used by MEMORY storage engine to decide the minimum memory
that is always allocated. MAX_ROWS is used to decide the minimum size for
indexes.

PACK_KEYS
---------

PACK_KEYS can be used to determine whether the indexes will be compressed. Set
it to 1 to compress all keys. With a value of 0, compression will not be used.
With the DEFAULT value, only long strings will be compressed. Uncompressed
keys are faster.

PAGE_CHECKSUM
-------------

PAGE_CHECKSUM is only applicable to Aria tables, and determines whether
indexes and data should use page checksums for extra safety.

PAGE_COMPRESSED
---------------

PAGE_COMPRESSED is used to enable InnoDB page compression for InnoDB tables.

PAGE_COMPRESSION_LEVEL
----------------------

PAGE_COMPRESSION_LEVEL is used to set the compression level for InnoDB page
compression for InnoDB tables. The table must also have the PAGE_COMPRESSED
table option set to 1.

Valid values for PAGE_COMPRESSION_LEVEL are 1 (the best speed) through 9 (the
best compression), .

PASSWORD
--------

PASSWORD is unused.

RAID_TYPE
---------

RAID_TYPE is an obsolete option, as the raid support has been disabled since
MySQL 5.0.

ROW_FORMAT
----------

The ROW_FORMAT table option specifies the row format for the data file.
Possible values are engine-dependent.

Supported MyISAM Row Formats
----------------------------

For MyISAM, the supported row formats are:

* FIXED
* DYNAMIC
* COMPRESSED

The COMPRESSED row format can only be set by the myisampack command line tool.

See MyISAM Storage Formats for more information.

Supported Aria Row Formats
--------------------------

For Aria, the supported row formats are:

* PAGE
* FIXED
* DYNAMIC.

See Aria Storage Formats for more information.

Supported InnoDB Row Formats
----------------------------

For InnoDB, the supported row formats are:

* COMPACT
* REDUNDANT
* COMPRESSED
* DYNAMIC.

If the ROW_FORMAT table option is set to FIXED for an InnoDB table, then the
server will either return an error or a warning depending on the value of the
innodb_strict_mode system variable. If the innodb_strict_mode system variable
is set to OFF, then a warning is issued, and MariaDB will create the table
using the default row format for the specific MariaDB server version. If the
innodb_strict_mode system variable is set to ON, then an error will be raised.

See InnoDB Storage Formats for more information.

Other Storage Engines and ROW_FORMAT
------------------------------------

Other storage engines do not support the ROW_FORMAT table option.

SEQUENCE
--------

If the table is a sequence, then it will have the SEQUENCE set to 1.

STATS_AUTO_RECALC
-----------------

STATS_AUTO_RECALC indicates whether to automatically recalculate persistent
statistics (see STATS_PERSISTENT, below) for an InnoDB table. If set to 1,
statistics will be recalculated when more than 10% of the data has changed.
When set to 0, stats will be recalculated only when an ANALYZE TABLE is run.
If set to DEFAULT, or left out, the value set by the innodb_stats_auto_recalc
system variable applies. See InnoDB Persistent Statistics.

STATS_PERSISTENT
----------------

STATS_PERSISTENT indicates whether the InnoDB statistics created by ANALYZE
TABLE will remain on disk or not. It can be set to 1 (on disk), 0 (not on
disk, the pre-MariaDB 10 behavior), or DEFAULT (the same as leaving out the
option), in which case the value set by the innodb_stats_persistent system
variable will apply. Persistent statistics stored on disk allow the statistics
to survive server restarts, and provide better query plan stability. See
InnoDB Persistent Statistics.

STATS_SAMPLE_PAGES
------------------

STATS_SAMPLE_PAGES indicates how many pages are used to sample index
statistics. If 0 or DEFAULT, the default value, the innodb_stats_sample_pages
value is used. See InnoDB Persistent Statistics.

TRANSACTIONAL
-------------

TRANSACTIONAL is only applicable for Aria tables. In future Aria tables
created with this option will be fully transactional, but currently this
provides a form of crash protection. See Aria Storage Engine for more details.

UNION
-----

UNION must be specified when you create a MERGE table. This option contains a
comma-separated list of MyISAM tables which are accessed by the new table. The
list is enclosed between parenthesis. Example: UNION = (t1,t2)

WITH SYSTEM VERSIONING
----------------------

WITH SYSTEM VERSIONING is used for creating System-versioned tables.

Partitions
----------

partition_options:
  PARTITION BY
    { [LINEAR] HASH(expr)
    | [LINEAR] KEY(column_list)
    | RANGE(expr)
    | LIST(expr)
    | SYSTEM_TIME [INTERVAL time_quantity time_unit] [LIMIT num] }
  [PARTITIONS num]
  [SUBPARTITION BY
    { [LINEAR] HASH(expr)
    | [LINEAR] KEY(column_list) }
   [SUBPARTITIONS num]
  ]
  [(partition_definition [, partition_definition] ...)]
partition_definition:
  [PARTITION] partition_name
    [VALUES {LESS THAN {(expr) | MAXVALUE} | IN (value_list)}]
    [[STORAGE] ENGINE [=] engine_name]
    [COMMENT [=] 'comment_text' ]
    [DATA DIRECTORY [=] 'data_dir']
    [INDEX DIRECTORY [=] 'index_dir']
    [MAX_ROWS [=] max_number_of_rows]
    [MIN_ROWS [=] min_number_of_rows]
    [TABLESPACE [=] tablespace_name]
    [NODEGROUP [=] node_group_id]
    [(subpartition_definition [, subpartition_definition] ...)]
subpartition_definition:
  SUBPARTITION logical_name
    [[STORAGE] ENGINE [=] engine_name]
    [COMMENT [=] 'comment_text' ]
    [DATA DIRECTORY [=] 'data_dir']
    [INDEX DIRECTORY [=] 'index_dir']
    [MAX_ROWS [=] max_number_of_rows]
    [MIN_ROWS [=] min_number_of_rows]
    [TABLESPACE [=] tablespace_name]
    [NODEGROUP [=] node_group_id]
If the PARTITION BY clause is used, the table will be partitioned. A partition
method must be explicitly indicated for partitions and subpartitions.
Partition methods are:

* [LINEAR] HASH creates a hash key which will be used to read and write rows.
The partition function can be any valid SQL expression which returns an
INTEGER number. Thus, it is possible to use the HASH method on an integer
column, or on functions which accept integer columns as an argument. However,
VALUES LESS THAN and VALUES IN clauses can not be used with HASH. An example:

CREATE TABLE t1 (a INT, b CHAR(5), c DATETIME)
  PARTITION BY HASH ( YEAR(c) );

[LINEAR] HASH can be used for subpartitions, too.

* [LINEAR] KEY is similar to HASH, but the index has an even distribution of
data. Also, the expression can only be a column or a list of columns. VALUES
LESS THAN and VALUES IN clauses can not be used with KEY.
* RANGE partitions the rows using on a range of values, using the VALUES LESS
THAN operator. VALUES IN is not allowed with RANGE. The partition function can
be any valid SQL expression which returns a single value.
* LIST assigns partitions based on a table's column with a restricted set of
possible values. It is similar to RANGE, but VALUES IN must be used for at
least 1 columns, and VALUES LESS THAN is disallowed.
* SYSTEM_TIME partitioning is used for System-versioned tables to store
historical data separately from current data.

Only HASH and KEY can be used for subpartitions, and they can be [LINEAR].

It is possible to define up to 8092 partitions and subpartitions.

The number of defined partitions can be optionally specified as PARTITION
count. This can be done to avoid specifying all partitions individually. But
you can also declare each individual partition and, additionally, specify a
PARTITIONS count clause; in the case, the number of PARTITIONs must equal
count.

Also see Partitioning Types Overview.

MariaDB starting with 10.7.1
----------------------------
From MariaDB 10.7, the PARTITION keyword is now optional as part of the
partition definition, for example, instead of:

create or replace table t1 (x int)
 partition by range(x) (
  partition p1 values less than (10),
  partition p2 values less than (20),
  partition p3 values less than (30),
  partition p4 values less than (40),
  partition p5 values less than (50),
  partition pn values less than maxvalue);

the following can also be used:

create or replace table t1 (x int)
 partition by range(x) (
  p1 values less than (10),
  p2 values less than (20),
  p3 values less than (30),
  p4 values less than (40),
  p5 values less than (50),
  pn values less than maxvalue);

Sequences
---------

CREATE TABLE can also be used to create a SEQUENCE. See CREATE SEQUENCE and
Sequence Overview.

Atomic DDL
----------

MariaDB starting with 10.6.1
----------------------------
MariaDB 10.6.1 supports Atomic DDL. CREATE TABLE is atomic, except for CREATE