, and normally is the best choice.
It is supported by all storage engines. It can be used to
compare a column's value with a value using the =, >, >=,
0) ,b int check (b> 0), constraint abc check (a>b));
 
If you use the second format and you don't give a name to
the constraint, then the constraint will get a auto
generated name. This is done so that you can later delete
the constraint with ALTER TABLE DROP constraint_name.
 
One can disable all constraint expression checks by setting
the variable check_constraint_checks to OFF. This is useful
for example when loading a table that violates some
constraints that you want to later find and fix in SQL.
 
See CONSTRAINT for more information.
 
Table Options
 
For each individual table you create (or alter), you can set
some table options. The general syntax for setting options
is:
 
  = , [ =  ...]
 
The equal sign is optional.
 
Some options are supported by the server and can be used for
all tables, no matter what storage engine they use; other
options can be specified for all storage engines, but have a
meaning only for some engines. Also, engines can extend
CREATE TABLE with new options.
 
If the IGNORE_BAD_TABLE_OPTIONS SQL_MODE is enabled, wrong
table options generate a warning; otherwise, they generate
an error.
 
table_option: 
 [STORAGE] ENGINE [=] engine_name
 | AUTO_INCREMENT [=] value
 | AVG_ROW_LENGTH [=] value
 | [DEFAULT] CHARACTER SET [=] charset_name
 | CHECKSUM [=] {0 | 1}
 | [DEFAULT] COLLATE [=] collation_name
 | COMMENT [=] 'string'
 | CONNECTION [=] 'connect_string'
 | DATA DIRECTORY [=] 'absolute path to directory'
 | DELAY_KEY_WRITE [=] {0 | 1}
 | ENCRYPTED [=] {YES | NO}
 | ENCRYPTION_KEY_ID [=] value
 | IETF_QUOTES [=] {YES | NO}
 | INDEX DIRECTORY [=] 'absolute path to directory'
 | INSERT_METHOD [=] { NO | FIRST | LAST }
 | KEY_BLOCK_SIZE [=] value
 | MAX_ROWS [=] value
 | MIN_ROWS [=] value
 | PACK_KEYS [=] {0 | 1 | DEFAULT}
 | PAGE_CHECKSUM [=] {0 | 1}
 | PAGE_COMPRESSED [=] {0 | 1}
 | PAGE_COMPRESSION_LEVEL [=] {0 .. 9}
 | PASSWORD [=] 'string'
 | ROW_FORMAT [=]
{DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT|PAGE}
 | SEQUENCE [=] {0|1}
 | STATS_AUTO_RECALC [=] {DEFAULT|0|1}
 | STATS_PERSISTENT [=] {DEFAULT|0|1}
 | STATS_SAMPLE_PAGES [=] {DEFAULT|value}
 | TABLESPACE tablespace_name
 | TRANSACTIONAL [=] {0 | 1}
 | UNION [=] (tbl_name[,tbl_name]...)
 | WITH SYSTEM VERSIONING
 
[STORAGE] ENGINE
 
[STORAGE] ENGINE specifies a storage engine for the table.
If this option is not used, the default storage engine is
used instead. That is, the storage_engine session option
value if it is set, or the value specified for the
--default-storage-engine mysqld startup options, or InnoDB.
If the specified storage engine is not installed and active,
the default value will be used, unless the
NO_ENGINE_SUBSTITUTION SQL MODE is set (default since
MariaDB 10.0). This is only true for CREATE TABLE, not for
ALTER TABLE. For a list of storage engines that are present
in your server, issue a SHOW ENGINES.
 
AUTO_INCREMENT
 
AUTO_INCREMENT specifies the initial value for the
AUTO_INCREMENT primary key. This works for MyISAM, Aria,
InnoDB/XtraDB, MEMORY, and ARCHIVE tables. You can change
this option with ALTER TABLE, but in that case the new value
must be higher than the highest value which is present in
the AUTO_INCREMENT column. If the storage engine does not
support this option, you can insert (and then delete) a row
having the wanted value - 1 in the AUTO_INCREMENT column.
 
AVG_ROW_LENGTH
 
AVG_ROW_LENGTH is the average rows size. It only applies to
tables using MyISAM and Aria storage engines that have the
ROW_FORMAT table option set to FIXED format.
 
MyISAM uses MAX_ROWS and AVG_ROW_LENGTH to decide the
maximum size of a table (default: 256TB, or the maximum file
size allowed by the system).
 
[DEFAULT] CHARACTER SET/CHARSET
 
[DEFAULT] CHARACTER SET (or [DEFAULT] CHARSET) is used to
set a default character set for the table. This is the
character set used for all columns 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. Maximum length is 2048
characters (before mariaDB 5.5 it was 60 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 were only supported for
MyISAM and Aria, before MariaDB 5.5. Since 5.5, DATA
DIRECTORY has also been 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 was added in MariaDB 10.1.4
 
The ENCRYPTED table option can be used to manually set the
encryption status of an InnoDB table. See InnoDB / XtraDB
Encryption for more information.
 
Aria does not currently support the ENCRYPTED table option.
See MDEV-18049 about that.
 
See Data-at-Rest Encryption for more information.
 
ENCRYPTION_KEY_ID
 
The ENCRYPTION_KEY_ID table option was added in MariaDB
10.1.4
 
The ENCRYPTION_KEY_ID table option can be used to manually
set the encryption key of an InnoDB table. See InnoDB /
XtraDB Encryption for more information.
 
Aria does not currently support the ENCRYPTION_KEY_ID table
option. See MDEV-18049 about that.
 
See Data-at-Rest Encryption for more information.
 
IETF_QUOTES
 
The IETF_QUOTES option was added in MariaDB 10.1.8
 
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/XtraDB, 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 XtraDB/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 is available only in MariaDB 10.0+. It
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 is available only in MariaDB 10.0+. It
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 is available only in MariaDB 10.0+. It
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 assignes 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
 
CREATE TABLE can also be used to create a SEQUENCE. See
CREATE SEQUENCE and Sequence Overview.
 
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/