You are viewing an old version of this article. View the current version here.

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_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

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.

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} | 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]...)

[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 (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 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 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 (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.

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 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.

COMMENT is a comment for the table. Maximum length is 60 characters.

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.

CONNECTION is used to specify a server name or a connection string for a Federated or FederatedX table.

DATA DIRECTORY and INDEX DIRECTORY is only supported for MyISAM and Aria, and specifies 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 this 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).

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.

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]

Comments

Comments loading...