CREATE TABLE
Syntax:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name (create_definition,...)
[
table_option]
...[
partition_options]
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)][
table_option]
...[
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)
Description:
Use the CREATE TABLE
statement to create a table with the given name.
You must have the 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
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 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
Microsecond precision can be between 0-6. If no precision is specified it is assumed to be 0, for backward compatibility reasons.
Table Definitions
create_definition: { col_name column_definition
|
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}]
[
reference_definition]
|
data_type [GENERATED ALWAYS] AS (expression) {VIRTUAL | PERSISTENT} [UNIQUE [KEY]] [COMMENT 'string']
Each definition either creates a column in the table or specifies and index or constraint on one or more columns. See 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 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
a row without specifying a value for that
column. You cannot generally 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
as
the default value for a TIMESTAMP
column to use the current
timestamp at insertion time.
Indexes
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_definitionindex_col_name: col_name [(length)] [ASC | DESC]
index_type: USING {BTREE | HASH | RTREE}
index_option: KEY_BLOCK_SIZE [=] value
|
index_type|
WITH PARSER parser_namereference_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
Table Options
table_option: 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}|
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]...)
Partitions
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]