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, or if you specify 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
as
the default value for a TIMESTAMP
column to use the current
timestamp at insertion time.
Use 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
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
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
below.
You can provide a comment for each column using the COMMENT
clause. Use
the SHOW FULL COLUMNS
statement to see column comments.
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]