
    | [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 1024 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.

Sequences
---------

MariaDB starting with 10.3
--------------------------
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
OR REPLACE, which is only crash safe.

Examples
--------

create table if not exists test (
a bigint auto_increment primary key,
name varchar(128) charset utf8,
key name (name(32))
) engine=InnoDB default charset latin1;

This example shows a couple of things:

* Usage of IF NOT EXISTS; If the table already existed, it will not be
created.  There will not be any error for the client, just a warning.
* How to create a PRIMARY KEY that is automatically generated.
* How to specify a table-specific character set and another for a column.
* How to create an index (name) that is only partly indexed (to save space).

The following clauses will work from MariaDB 10.2.1 only.

CREATE TABLE t1(
 a int DEFAULT (1+1),
 b int DEFAULT (a+1),
 expires DATETIME DEFAULT(NOW() + INTERVAL 1 YEAR),
 x BLOB DEFAULT USER()
);

URL: https://mariadb.com/kb/en/create-table/