== Syntax: ==
<<fixed>>
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
([[#table-definitions|create_definition]],...) {{{[}}}[[#table-options|table_option]]{{{]}}}... {{{[}}}[[#partitions|partition_options]]{{{]}}}
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[([[#table-definitions|create_definition]],...)] {{{[}}}[[#table-options|table_option]]{{{]}}}... {{{[}}}[[#partitions|partition_options]]{{{]}}}
select_statement
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name { LIKE old_tbl_name | (LIKE old_tbl_name) }
select_statement:
[IGNORE | REPLACE] [AS] SELECT ... (Some legal select statement)
<</fixed>>
== Description: ==
Use the ##CREATE TABLE## statement to create a table with the given name.
You must have the ##[[grant#table-privileges|CREATE]]## privilege for the
table or on the database to create a table.
In its most basic form, the ##CREATE TABLE## statement provides a table name
followed by a list of columns, indexes, and constraints. By default, the table
is created in the default database. Specify a database with ##//db_name//.//tbl_name//##.
If you quote the table name, you must quote the database name and table name
separately as ##`//db_name//`.`//tbl_name//`##.
If a table with the same name exists, error 1050 results. Use ##IF NOT EXISTS##
to suppress this error and issue a note instead. Use ##[[show-warnings|SHOW WARNINGS]]##
to see notes.
Use the ##TEMPORARY## keyword to create a temporary table that is only available
to your current session. Temporary tables are dropped when the your session ends.
Temporary table names are specific to your session. They may conflict with other
temporary tables from other sessions or shadow names of non-temporary tables.
You must have the ##[[grant#database-privileges|CREATE TEMPORARY TABLES]]##
privilege on the database to create temporary tables.
Use the ##LIKE## clause instead of a full table definition to create a table
with the same definition as another table, including columns, indexes, and
table options.
The ##CREATE TABLE## statement automatically commits the current transaction,
except when using the ##TEMPORARY## keyword.
== New in MariaDB 5.3
[[microseconds-in-mariadb|Microsecond precision]] can be between 0-6. If no precision is specified it is assumed to be 0, for backward compatibility reasons.
== Table Definitions
<<fixed>>
create_definition:
{ col_name column_definition {{{|}}} [[#indexes|index_definition]] {{{|}}} CHECK (expr) }
column_definition:
data_type
[NOT NULL | NULL] [DEFAULT default_value]
[AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
[COMMENT 'string']
[COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
[STORAGE {DISK|MEMORY|DEFAULT}]
{{{[}}}[[#indexes|reference_definition]]{{{]}}}
{{{|}}} data_type [GENERATED ALWAYS] AS (expression) {VIRTUAL | PERSISTENT}
[UNIQUE [KEY]] [COMMENT 'string']
<</fixed>>
Each definition either creates a column in the table or specifies and index or
constraint on one or more columns. See [[#indexes|Indexes]] below for details
on creating indexes.
Create a column by specifying a column name and a data type, optionally
followed by column options. See [[data-types|Data Types]] for a full list
of data types allowed in MariaDB.
Use the ##NULL## or ##NOT NULL## options to specify that values in the column
may or may not be ##NULL##, respectively. By default, values may be ##NULL##.
Specify a default value using the ##DEFAULT## clause. The default value will
be used if you ##[[insert|INSERT]]## a row without specifying a value for that
column, or if you specify ##[[default|DEFAULT]]## for that column.
You cannot usually provide an expression or function to evaluate at
insertion time. You must provide a constant default value instead. The one
exception is that you may use ##[[current-timestamp|CURRENT_TIMESTAMP]]## as
the default value for a ##[[timestamp|TIMESTAMP]]## column to use the current
timestamp at insertion time.
Use ##[[auto_increment|AUTO_INCREMENT]]## to create a column whose value can
can be set automatically from a simple counter. You can only use ##AUTO_INCREMENT##
on a column with an integer type. The column must be a key, and there can only be
one ##AUTO_INCREMENT## column in a table. If you insert a row without specifying
a value for that column (or if you specify ##0##, ##NULL##, or ##[[default|DEFAULT]]##
as the value), the actual value will be taken from the counter, with each insertion
incrementing the counter by one. You can still insert a value explicitly. If you
insert a value that is greater than the current counter value, the counter is
set based on the new value. An ##AUTO_INCREMENT## column is implicitly ##NOT NULL##.
Use ##[[last_insert_id|LAST_INSERT_ID]]## to get the ##[[AUTO_INCREMENT]]## value
most recently used by an ##[[INSERT]]## statement.
Use ##UNIQUE KEY## (or just ##UNIQUE##) to specify that all values in the column
must be distinct from each other. Unless the column is ##NOT NULL##, there may be
multiple rows with ##NULL## in the column. Use ##PRIMARY KEY## (or just ##KEY##)
to make a column a primary key. A primary key is a special type of a unique key.
There can be at most one primary key per table, and it is implicitly ##NOT NULL##.
Specifying a column as a primary or unique key creates an index on that column.
Specifying a key in the column definition is equivalent to specifying a
single-column key separately. See ##[[#indexes|Indexes]]## below.
You can provide a comment for each column using the ##COMMENT## clause. Use
the ##[[show-columns|SHOW FULL COLUMNS]]## statement to see column comments.
== Indexes
<<fixed>>
index_definition:
{INDEX|KEY} [index_name] [index_type] (index_col_name,...) [index_option] ...
{{{|}}} {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (index_col_name,...) [index_option] ...
{{{|}}} [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...) [index_option] ...
{{{|}}} [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...) [index_option] ...
{{{|}}} [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) reference_definition
index_col_name:
col_name [(length)] [ASC | DESC]
index_type:
USING {BTREE | HASH | RTREE}
index_option:
KEY_BLOCK_SIZE [=] value
{{{|}}} index_type
{{{|}}} WITH PARSER parser_name
reference_definition:
REFERENCES tbl_name (index_col_name,...)
[MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
[ON DELETE reference_option]
[ON UPDATE reference_option]
reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION
<</fixed>>
== Table Options
<<fixed>>
table_option:
ENGINE [=] engine_name
{{{|}}} AUTO_INCREMENT [=] value
{{{|}}} AVG_ROW_LENGTH [=] value
{{{|}}} [DEFAULT] CHARACTER SET [=] [[character-sets-and-collations|charset_name]]
{{{|}}} CHECKSUM [=] {0 | 1}
{{{|}}} [DEFAULT] COLLATE [=] [[character-sets-and-collations|collation_name]]
{{{|}}} COMMENT [=] 'string'
{{{|}}} CONNECTION [=] 'connect_string'
{{{|}}} DATA DIRECTORY [=] 'absolute path to directory'
{{{|}}} DELAY_KEY_WRITE [=] {0 | 1}
{{{|}}} 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}
{{{|}}} PASSWORD [=] 'string'
{{{|}}} ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
{{{|}}} TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}]
{{{|}}} UNION [=] (tbl_name[,tbl_name]...)
<</fixed>>
== Partitions
<<fixed>>>
partition_options:
PARTITION BY
{ [LINEAR] HASH(expr)
{{{|}}} [LINEAR] KEY(column_list)
{{{|}}} RANGE(expr)
{{{|}}} LIST(expr) }
[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]
<</fixed>>
