CREATE TABLE
Syntax
CREATE [OR REPLACE] [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name (create_definition,...)
[
table_options]
...[
partition_options]
CREATE [OR REPLACE] [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)][
table_options]
...[
partition_options]
select_statement CREATE [OR REPLACE] [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name { LIKE old_table_name | (LIKE old_table_name) }select_statement: [IGNORE | REPLACE] [AS] SELECT ... (Some legal select statement)
Contents
- Syntax
- Description
- Column and Index Definitions
- Indexes
- Periods
- Constraint Expressions
- Table Options
- [STORAGE] ENGINE
- AUTO_INCREMENT
- AVG_ROW_LENGTH
- [DEFAULT] CHARACTER SET/CHARSET
- CHECKSUM/TABLE_CHECKSUM
- [DEFAULT] COLLATE
- COMMENT
- CONNECTION
- DATA DIRECTORY/INDEX DIRECTORY
- DELAY_KEY_WRITE
- ENCRYPTION/ENCRYPTION_KEY_ID
- IETF_QUOTES
- INSERT_METHOD
- KEY_BLOCK_SIZE
- MIN_ROWS/MAX_ROWS
- PACK_KEYS
- PAGE_CHECKSUM
- PASSWORD
- RAID_TYPE
- ROW_FORMAT
- STATS_AUTO_RECALC
- STATS_PERSISTENT
- STATS_SAMPLE_PAGES
- TRANSACTIONAL
- UNION
- WITH SYSTEM VERSIONING
- Partitions
- CREATE ... SELECT
- CREATE OR REPLACE
- Sequences ==
- Examples
- See Also
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`
. This is particularly useful for CREATE TABLE ... SELECT
, because it allows to create a table into a database, which contains data from other databases. See Identifier Qualifiers.
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.
The CREATE TABLE
statement automatically commits the current transaction,
except when using the TEMPORARY
keyword.
MariaDB starting with 5.3
Microsecond precision can be between 0-6. If no precision is specified it is assumed to be 0, for backward compatibility reasons.
MariaDB starting with 10.0.8
The OR REPLACE
syntax was added in 10.0.8.
For valid identifiers to use as table names, see Identifier Names.
Note: if the default_storage_engine is set to ColumnStore then it needs setting on all UMs. Otherwise when the tables using the default engine are replicated across UMs they will use the wrong engine. You should therefore not use this option as a session variable with ColumnStore.
TEMPORARY
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 will not conflict with other temporary tables from other session even if they share the same name. They will shadow names of non-temporary tables or views, if they are identical. A temporary table can have the same name as a non-temporary table which is located in the same database. In that case, their name will reference the temporary table when used in SQL statements. You must have the CREATE TEMPORARY TABLES
privilege on the database to create temporary tables. If no storage engine is specified, the default_tmp_storage_engine setting will determine the engine.
LIKE
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. Foreign key definitions, as well as any DATA DIRECTORY or INDEX DIRECTORY table options specified on the original table, will not be created.
Column and Index Definitions
create_definition: { col_name column_definition
|
index_definition|
period_definition|
CHECK (expr) }column_definition: data_type [NOT NULL | NULL] [DEFAULT default_value | (expression)] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [INVISIBLE] [{WITH|WITHOUT} SYSTEM VERSIONING] [COMMENT 'string'] [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
[
reference_definition]
|
data_type [GENERATED ALWAYS] AS { { ROW {START|
END} } | { (expression) [VIRTUAL | PERSISTENT] } } [UNIQUE [KEY]] [COMMENT 'string']constraint_definition: CONSTRAINT [constraint_name] CHECK (expression)
Note: MariaDB accepts the REFERENCES
clause in ALTER TABLE
and CREATE TABLE
column definitions, but that syntax does nothing. MariaDB simply parses it without returning any error or warning, for compatibility with other DBMS's. Before MariaDB 10.2.1 this was also true for CHECK
constraints. Only the syntax for indexes described below creates foreign keys.
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.
NULL and NOT NULL
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
. See also NULL Values in MariaDB.
DEFAULT
Specify a default value using the DEFAULT
clause. If you don't specify DEFAULT
then the following rules apply:
- If the column is not defined with
NOT NULL
,AUTO_INCREMENT
orTIMESTAMP
, an explicitDEFAULT NULL
will be added. Note that in MySQL and in MariaDB before 10.1.6, you may get an explicitDEFAULT
for primary key parts, if not specified with NOT NULL.
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.
Before MariaDB 10.2.1 you couldn't usually provide an expression or function to evaluate at
insertion time. You had to 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.
MariaDB starting with 10.0.1
CURRENT_TIMESTAMP
may also be used as
the default value for a DATETIME
From MariaDB 10.2.1 you can use most functions in DEFAULT
. Expressions should have parentheses around them. If you use a non deterministic function in DEFAULT
then all inserts to the table will be replicated in row mode. You can even refer to earlier columns in the DEFAULT
expression:
CREATE TABLE t1 (a int DEFAULT (1+1), b int DEFAULT (a+1)); CREATE TABLE t2 (a bigint primary key DEFAULT UUID_SHORT());
The DEFAULT
clause cannot contain any stored functions or subqueries, and a column used in the clause must already have been defined earlier in the statement.
Since MariaDB 10.2.1, it is possible to assign BLOB or TEXT columns a DEFAULT
value. In earlier versions, assigning a default to these columns was not possible.
MariaDB starting with 10.3.3
Starting from 10.3.3 you can also use DEFAULT (NEXT VALUE FOR sequence)
AUTO_INCREMENT
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.
UNIQUE KEY
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.
COMMENT
You can provide a comment for each column using the COMMENT
clause. The maximum length is 1024 characters (it was 255 characters before MariaDB 5.5). Use
the SHOW FULL COLUMNS
statement to see column comments.
VIRTUAL COLUMNS
To define virtual columns (from MariaDB 5.2), you can specify the virtual column's type: VIRTUAL
or PERSISTENT
. The default is VIRTUAL
, which means that the column is calculated on the fly when a command names it; PERSISTENT
means that the value is physically stored in the table. The AS
keyword makes clear that the column is virtual, but you can also use the GENERATED ALWAYS
keyword. The expression must be deterministic. For a complete description about virtual columns and their limitations, see virtual columns.
COLUMN_FORMAT
COLUMN_FORMAT
is only used by MySQL Cluster, and is silently ignored in MariaDB.
COMPRESSED
MariaDB starting with 10.3.3
Certain columns may be compressed. See Storage-Engine Independent Column Compression.
INVISIBLE
MariaDB starting with 10.3.3
Columns may be made invisible, and hidden in certain contexts. See Invisible Columns.
WITH SYSTEM VERSIONING and WITHOUT SYSTEM VERSIONING
MariaDB starting with 10.3.4
Columns may be explicitly marked as included or excluded from system versioning. See System-versioned tables for details.
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
INDEX
and KEY
are synonyms.
Index names are optional, if not specified an automatic name will be assigned. Index name are needed to drop indexes and appear in error messages when a constraint is violated.
INDEX TYPES
Each storage engine supports some or all index types. Different types are optimized for different kind of operations:
BTREE
is the default type, 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 =, >, >=, <, <=,BETWEEN
, andLIKE
operators.BTREE
can also be used to findNULL
values. Searches against an index prefix are possible.HASH
is only supported by the MEMORY storage engine.HASH
indexes can only be used for =, <=, and >= comparisons. It can not be used for theORDER BY
clause. Searches against an index prefix are not possible.RTREE
is the default forSPATIAL
indexes, but if the storage engine does not support itBTREE
can be used.
Index columns names are listed between parenthesis. After each column, a prefix length can be specified. If no length is specified, the whole column will be indexed. ASC
and DESC
can be specified for compatibility with are DBMS's, but have no meaning in MariaDB.
The WITH PARSER
index option only applies to FULLTEXT indexes and contains the fulltext parser name. The fulltext parser must be an installed plugin.
Use the SPATIAL
or FULLTEXT
keywords to create geometric or fulltext indexes.
UNIQUE
The UNIQUE
keyword means that the index will not accept duplicated values, except for NULL
s. An error will raise if you try to insert duplicate values in a UNIQUE
index.
For UNIQUE
keys and FOREIGN KEY
s, you can specify a name for the constraint, using the CONSTRAINT
keyword. That name will be used in error messages.
For PRIMARY KEY
a name can be specified too, but is silently ignored by the server and in error messages and system tables the name of the key is always PRIMARY
.
KEY_BLOCK_SIZE
For the KEY_BLOCK_SIZE
index option, see the table options below. This option can be specified at table level or at index level. Note that InnoDB ignores it at index level, although SHOW CREATE TABLE may report it for each index. With InnoDB, specifying a KEY_BLOCK_SIZE different from 0 at table level, implies ROW_FORMAT=COMPRESSED.
FOREIGN KEY
For FOREIGN KEY
s, a reference definition must be provided. First, you have to specify the name of the target (parent) table and a column or a column list which must be indexed and whose values must match to the foreign key's values. The MATCH
clause is accepted to improve the compatibility with other DBMS's, but has no meaning in MariaDB. The ON DELETE
and ON UPDATE
clauses specify what must be done when a DELETE
(or a REPLACE
) statements attempts to delete a referenced row from the parent table, and when an UPDATE
statement attempts to modify the referenced foreign key columns in a parent table row, respectively. The following options are allowed:
RESTRICT
: The delete/update operation is not performed. The statement terminates with a 1451 error (SQLSTATE '2300').NO ACTION
: Synonym forRESTRICT
.CASCADE
: The delete/update operation is performed in both tables.SET NULL
: The update or delete goes ahead in the parent table, and the corresponding foreign key fields in the child table are set toNULL
. (They must not be defined asNOT NULL
for this to succeed).
MariaDB until 5.3
SET DEFAULT
: This option is currently implemented only for the PBXT storage engine, which is disabled by default and no longer maintained. It sets the child table's foreign key fields to theirDEFAULT
values when the referenced parent table key entries are updated or deleted.
If either clause is omitted, the default behavior for the omitted clause is RESTRICT
.
See foreign keys for details.
Periods
MariaDB starting with 10.3.4
period_definition: PERIOD FOR SYSTEM_TIME (start_column_name, end_column_name)
MariaDB supports a subset of the standard syntax for periods. At the moment it's only used for creating System-versioned tables. Both columns must be created, must be either of a TIMESTAMP(6)
or BIGINT UNSIGNED
type, and be generated as ROW START
and ROW END
accordingly. See System-versioned tables for details.
The table must also have the WITH SYSTEM VERSIONING
clause.
Constraint Expressions
MariaDB starting with 10.2.1
MariaDB 10.2.1 introduced new ways to define a constraint.
Note: Before MariaDB 10.2.1, constraint expressions were accepted in the syntax but ignored.
MariaDB 10.2.1 introduced two ways to define a constraint:
CHECK(expression)
given as part of a column definition.CONSTRAINT [constraint_name] CHECK (expression)
Before a row is inserted or updated, all constraints are evaluated in the order they are defined. If any constraints fails, then the row will not be updated. One can use most deterministic functions in a constraint, including UDFs.
create table t1 (a int check(a>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.
Table Options
For each individual table you create (or alter), you can set some table options. The general syntax for setting options is:
<OPTION_NAME> = <option_value>, [<OPTION_NAME> = <option_value> ...]
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}|
PASSWORD [=] 'string'|
ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT|PAGE}|
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, and is only useful for tables using the 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
.
ENCRYPTION/ENCRYPTION_KEY_ID
ENCRYPTION
and ENCRYPTION_KEY_ID
MariaDB starting with 10.1.4
The table encryption options were added in MariaDB 10.1.4
See Table Encryption.
IETF_QUOTES
MariaDB starting with 10.1.8
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 INSERT
s 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. Using it with an InnoDB table will also result in that table being compressed - see XtraDB/InnoDB Storage Formats for limitations and warnings with this usage.
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.
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
ROW_FORMAT
specifies the format for the data file. Possible values are engine-dependent. For MyISAM formats are: FIXED
, DYNAMIC
; COMPRESSED
can only be set by the myisampack command line tool. For Aria formats are: PAGE
, FIXED
, DYNAMIC
. For InnoDB/XtraDB, formats are: COMPACT
, REDUNDANT
, COMPRESSED
, DYNAMIC
. If ROW_FORMAT=FIXED
is specified on an InnoDB table, the server will either return an error or a warning depending on the value of innodb_strict_mode. In this case, if innodb_strict_mode
is off, and a warning is issued, MariaDB will create the table using the COMPACT format until MariaDB 10.2.1, or DYNAMIC format from MariaDB 10.2.2. Other storage engines do not support the ROW_FORMAT
option. See MyISAM Storage Formats, Aria Storage Formats and XtraDB/InnoDB Storage Formats for more details.
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 anINTEGER
number. Thus, it is possible to use theHASH
method on an integer column, or on functions which accept integer columns as an argument. However,VALUES LESS THAN
andVALUES IN
clauses can not be used withHASH
. 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 toHASH
, 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
andVALUES IN
clauses can not be used withKEY
.RANGE
partitions the rows using on a range of values, using theVALUES LESS THAN
operator.VALUES IN
is not allowed withRANGE
. 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 toRANGE
, butVALUES IN
must be used for at least 1 columns, andVALUES 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 PARTITION
s must equal count.
Also see Partitioning Types Overview.
CREATE ... SELECT
You can create a table containing data from other tables using the CREATE ... SELECT
statement. Columns will be created in the table for each field returned by the SELECT
query.
You can also define some columns normally and add other columns from a SELECT
. You can also create columns in the normal way and assign them some values using the query, this is done to force a certain type or other field characteristics. The columns that are not named in the query will be placed before the others. For example:
CREATE TABLE test (a INT NOT NULL, b CHAR(10)) ENGINE=MyISAM SELECT 5 AS b, c, d FROM another_table;
Remember that the query just returns data. If you want to use the same indexes, or the same columns attributes ([NOT] NULL
, DEFAULT
, AUTO_INCREMENT
) in the new table, you need to specify them manually. Types and sizes are not automatically preserved if no data returned by the SELECT
requires the full size, and VARCHAR
could be converted into CHAR
. The CAST() function can be used to forcee the new table to use certain types.
Aliases (AS
) are taken into account, and they should always be used when you SELECT
an expression (function, arithmetical operation, etc).
If an error occurs during the query, the table will not be created at all.
If the new table has a primary key or UNIQUE
indexes, you can use the IGNORE or REPLACE
keywords to handle duplicate key errors during the query. IGNORE
means that the newer values must not be inserted an identical value exists in the index. REPLACE
means that older values must be overwritten.
If the columns in the new table are more than the rows returned by the query, the columns populated by the query will be placed after other columns. Note that if the strict SQL_MODE
is on, and the columns that are not names in the query do not have a DEFAULT
value, an error will raise and no rows will be copied.
Concurrent inserts are not used during the execution of a CREATE ... SELECT
.
If the table already exists, an error similar to the following will be returned:
ERROR 1050 (42S01): Table 't' already exists
If the IF NOT EXISTS
clause is used and the table exists, a note will be produced instead of an error.
To insert rows from a query into an existing table, INSERT ... SELECT can be used.
CREATE OR REPLACE
The CREATE OR REPLACE TABLE
syntax was added in MariaDB 10.0.8 to make replication more robust if it has to rollback and repeat statements such as CREATE ... SELECT
on slaves.
CREATE OR REPLACE TABLE table_name (a int);
is basically the same as:
DROP TABLE IF EXISTS table_name; CREATE TABLE table_name (a int);
with the following exceptions:
- If
table_name
was locked with LOCK TABLES it will continue to be locked after the statement. - Temporary tables are only dropped if the
TEMPORARY
keyword was used. (With DROP TABLE, temporary tables are preferred to be dropped before normal tables).
Things to be Aware of With CREATE OR REPLACE
- The table is dropped first (if it existed), after that the
CREATE
is done. Because of this, if theCREATE
fails, then the table will not exist anymore after the statement. If the table was used withLOCK TABLES
it will be unlocked. - One can't use
OR REPLACE
together withIF EXISTS
. - Slaves in replication will by default use
CREATE OR REPLACE
when replicatingCREATE
statements that don''t useIF EXISTS
. This can be changed by setting the variable slave-ddl-exec-mode toSTRICT
.
MariaDB starting with 10.3
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() );
See Also
- Identifier Names
- ALTER TABLE
- DROP TABLE
- Character Sets and Collations
- SHOW CREATE TABLE
- Since MariaDB 5.2, storage engines can add their own attributes for columns, indexes and tables.
- Variable slave-ddl-exec-mode.