MySQL :: MySQL 8.4 Reference Manual :: 15.1.20 CREATE TABLE Statement (original) (raw)

15.1.20 CREATE TABLE Statement

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    [table_options]
    [partition_options]
    [IGNORE | REPLACE]
    [AS] query_expression

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    { LIKE old_tbl_name | (LIKE old_tbl_name) }

create_definition: {
    col_name column_definition
  | {INDEX | KEY} [index_name] [index_type] (key_part,...)
      [index_option] ...
  | {FULLTEXT | SPATIAL} [INDEX | KEY] [index_name] (key_part,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] PRIMARY KEY
      [index_type] (key_part,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY]
      [index_name] [index_type] (key_part,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] FOREIGN KEY
      [index_name] (col_name,...)
      reference_definition
  | check_constraint_definition
}

column_definition: {
    data_type [NOT NULL | NULL] [DEFAULT {literal | (expr)} ]
      [VISIBLE | INVISIBLE]
      [AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY]
      [COMMENT 'string']
      [COLLATE collation_name]
      [COLUMN_FORMAT {FIXED | DYNAMIC | DEFAULT}]
      [ENGINE_ATTRIBUTE [=] 'string']
      [SECONDARY_ENGINE_ATTRIBUTE [=] 'string']
      [STORAGE {DISK | MEMORY}]
      [reference_definition]
      [check_constraint_definition]
  | data_type
      [COLLATE collation_name]
      [GENERATED ALWAYS] AS (expr)
      [VIRTUAL | STORED] [NOT NULL | NULL]
      [VISIBLE | INVISIBLE]
      [UNIQUE [KEY]] [[PRIMARY] KEY]
      [COMMENT 'string']
      [reference_definition]
      [check_constraint_definition]
}

data_type:
    (see Chapter 13, Data Types)

key_part: {col_name [(length)] | (expr)} [ASC | DESC]

index_type:
    USING {BTREE | HASH}

index_option: {
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'
  | {VISIBLE | INVISIBLE}
  |ENGINE_ATTRIBUTE [=] 'string'
  |SECONDARY_ENGINE_ATTRIBUTE [=] 'string'
}

check_constraint_definition:
    [CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]

reference_definition:
    REFERENCES tbl_name (key_part,...)
      [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
      [ON DELETE reference_option]
      [ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

table_options:
    table_option [[,] table_option] ...

table_option: {
    AUTOEXTEND_SIZE [=] value
  | AUTO_INCREMENT [=] value
  | AVG_ROW_LENGTH [=] value
  | [DEFAULT] CHARACTER SET [=] charset_name
  | CHECKSUM [=] {0 | 1}
  | [DEFAULT] COLLATE [=] collation_name
  | COMMENT [=] 'string'
  | COMPRESSION [=] {'ZLIB' | 'LZ4' | 'NONE'}
  | CONNECTION [=] 'connect_string'
  | {DATA | INDEX} DIRECTORY [=] 'absolute path to directory'
  | DELAY_KEY_WRITE [=] {0 | 1}
  | ENCRYPTION [=] {'Y' | 'N'}
  | ENGINE [=] engine_name
  | ENGINE_ATTRIBUTE [=] 'string'
  | 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}
  | START TRANSACTION 
  | SECONDARY_ENGINE_ATTRIBUTE [=] 'string'
  | STATS_AUTO_RECALC [=] {DEFAULT | 0 | 1}
  | STATS_PERSISTENT [=] {DEFAULT | 0 | 1}
  | STATS_SAMPLE_PAGES [=] value
  | tablespace_option
  | UNION [=] (tbl_name[,tbl_name]...)
}

partition_options:
    PARTITION BY
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY [ALGORITHM={1 | 2}] (column_list)
        | RANGE{(expr) | COLUMNS(column_list)}
        | LIST{(expr) | COLUMNS(column_list)} }
    [PARTITIONS num]
    [SUBPARTITION BY
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY [ALGORITHM={1 | 2}] (column_list) }
      [SUBPARTITIONS num]
    ]
    [(partition_definition [, partition_definition] ...)]

partition_definition:
    PARTITION partition_name
        [VALUES
            {LESS THAN {(expr | value_list) | MAXVALUE}
            |
            IN (value_list)}]
        [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'string' ]
        [DATA DIRECTORY [=] 'data_dir']
        [INDEX DIRECTORY [=] 'index_dir']
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] tablespace_name]
        [(subpartition_definition [, subpartition_definition] ...)]

subpartition_definition:
    SUBPARTITION logical_name
        [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'string' ]
        [DATA DIRECTORY [=] 'data_dir']
        [INDEX DIRECTORY [=] 'index_dir']
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] tablespace_name]

tablespace_option:
    TABLESPACE tablespace_name [STORAGE DISK]
  | [TABLESPACE tablespace_name] STORAGE MEMORY

query_expression:
    SELECT ...   (Some valid select or union statement)

CREATE TABLE creates a table with the given name. You must have theCREATE privilege for the table.

By default, tables are created in the default database, using theInnoDB storage engine. An error occurs if the table exists, if there is no default database, or if the database does not exist.

MySQL has no limit on the number of tables. The underlying file system may have a limit on the number of files that represent tables. Individual storage engines may impose engine-specific constraints. InnoDB permits up to 4 billion tables.

For information about the physical representation of a table, seeSection 15.1.20.1, “Files Created by CREATE TABLE”.

There are several aspects to the CREATE TABLE statement, described under the following topics in this section:

Table Name

Temporary Tables

You can use the TEMPORARY keyword when creating a table. A TEMPORARY table is visible only within the current session, and is dropped automatically when the session is closed. For more information, seeSection 15.1.20.2, “CREATE TEMPORARY TABLE Statement”.

Table Cloning and Copying

CREATE TABLE new_tbl LIKE orig_tbl;

For more information, see Section 15.1.20.3, “CREATE TABLE ... LIKE Statement”.

CREATE TABLE new_tbl AS SELECT * FROM orig_tbl;

For more information, seeSection 15.1.20.4, “CREATE TABLE ... SELECT Statement”.

Column Data Types and Attributes

There is a hard limit of 4096 columns per table, but the effective maximum may be less for a given table and depends on the factors discussed in Section 10.4.7, “Limits on Table Column Count and Row Size”.

SELECT * FROM tbl_name WHERE auto_col IS NULL

This method requires thatsql_auto_is_null variable is not set to 0. See Section 7.1.8, “Server System Variables”.
For information about InnoDB andAUTO_INCREMENT, seeSection 17.6.1.6, “AUTO_INCREMENT Handling in InnoDB”. For information about AUTO_INCREMENT and MySQL Replication, seeSection 19.5.1.1, “Replication and AUTO_INCREMENT”.

CREATE TABLE t1 (c1 INT ENGINE_ATTRIBUTE='{"key":"value"}');

ENGINE_ATTRIBUTE andSECONDARY_ENGINE_ATTRIBUTE values can be repeated without error. In this case, the last specified value is used.
ENGINE_ATTRIBUTE andSECONDARY_ENGINE_ATTRIBUTE values are not checked by the server, nor are they cleared when the table's storage engine is changed.

mysql> CREATE TABLE t1 (  
    ->     c1 INT STORAGE DISK,  
    ->     c2 INT STORAGE MEMORY  
    -> ) ENGINE NDB;  
ERROR 1005 (HY000): Can't create table 'c.t1' (errno: 140)  
mysql> CREATE TABLE t1 (  
    ->     c1 INT STORAGE DISK,  
    ->     c2 INT STORAGE MEMORY  
    -> ) TABLESPACE ts_1 ENGINE NDB;  
Query OK, 0 rows affected (1.06 sec)

For NDB tables, STORAGE DEFAULT is equivalent to STORAGE MEMORY.
The STORAGE clause has no effect on tables using storage engines other thanNDB. TheSTORAGE keyword is supported only in the build of mysqld that is supplied with NDB Cluster; it is not recognized in any other version of MySQL, where any attempt to use the STORAGE keyword causes a syntax error.

Indexes, Foreign Keys, and CHECK Constraints

Several keywords apply to creation of indexes, foreign keys, andCHECK constraints. For general background in addition to the following descriptions, seeSection 15.1.15, “CREATE INDEX Statement”,Section 15.1.20.5, “FOREIGN KEY Constraints”, andSection 15.1.20.6, “CHECK Constraints”.

CREATE TABLE lookup  
  (id INT, INDEX USING BTREE (id)  
) ENGINE = MEMORY;

The preferred position for USING is after the index column list. It can be given before the column list, but support for use of the option in that position is deprecated and you should expect it to be removed in a future MySQL release.

Table Options

Table options are used to optimize the behavior of the table. In most cases, you do not have to specify any of them. These options apply to all storage engines unless otherwise indicated. Options that do not apply to a given storage engine may be accepted and remembered as part of the table definition. Such options then apply if you later use ALTER TABLE to convert the table to use a different storage engine.

CREATE TABLE t1 (  
    c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,  
    c2 VARCHAR(100),  
    c3 VARCHAR(100) )  
ENGINE=NDB  
COMMENT="NDB_TABLE=READ_BACKUP=0,PARTITION_BALANCE=FOR_RP_BY_NODE";

Spaces are not permitted within the quoted string. The string is case-insensitive.
The comment is displayed as part of the output ofSHOW CREATE TABLE. The text of the comment is also available as the TABLE_COMMENT column of the MySQL Information SchemaTABLES table.
This comment syntax is also supported withALTER TABLE statements forNDB tables. Keep in mind that a table comment used with ALTER TABLE replaces any existing comment which the table might have had previously.
Setting the MERGE_THRESHOLD option in table comments is not supported for NDB tables (it is ignored).
For complete syntax information and examples, seeSection 15.1.20.12, “Setting NDB Comment Options”.

CREATE TABLE t1 (c1 INT) ENGINE_ATTRIBUTE='{"key":"value"}';

ENGINE_ATTRIBUTE andSECONDARY_ENGINE_ATTRIBUTE values can be repeated without error. In this case, the last specified value is used.
ENGINE_ATTRIBUTE andSECONDARY_ENGINE_ATTRIBUTE values are not checked by the server, nor are they cleared when the table's storage engine is changed.

CREATE TABLE tbl_name ... TABLESPACE [=] tablespace_name

The general tablespace that you specify must exist prior to using the TABLESPACE clause. For information about general tablespaces, seeSection 17.6.3.3, “General Tablespaces”.
The_`tablespacename`_ is a case-sensitive identifier. It may be quoted or unquoted. The forward slash character (“/”) is not permitted. Names beginning with “innodb_” are reserved for special use.
To create a table in the system tablespace, specifyinnodb_system as the tablespace name. 

CREATE TABLE tbl_name ... TABLESPACE [=] innodb_system

Using TABLESPACE [=] innodb_system, you can place a table of any uncompressed row format in the system tablespace regardless of theinnodb_file_per_table setting. For example, you can add a table withROW_FORMAT=DYNAMIC to the system tablespace using TABLESPACE [=] innodb_system.
To create a table in a file-per-table tablespace, specifyinnodb_file_per_table as the tablespace name. 

CREATE TABLE tbl_name ... TABLESPACE [=] innodb_file_per_table

Note
If innodb_file_per_table is enabled, you need not specifyTABLESPACE=innodb_file_per_table to create an InnoDB file-per-table tablespace. InnoDB tables are created in file-per-table tablespaces by default wheninnodb_file_per_table is enabled.
The DATA DIRECTORY clause is permitted withCREATE TABLE ... TABLESPACE=innodb_file_per_table but is otherwise not supported for use in combination with theTABLESPACE clause. The directory specified in a DATA DIRECTORY clause must be known toInnoDB. For more information, seeUsing the DATA DIRECTORY Clause.
Note
Support for TABLESPACE = innodb_file_per_table and TABLESPACE = innodb_temporary clauses withCREATE TEMPORARY TABLE is deprecated; expect it to be removed in a future version of MySQL.
The STORAGE table option is employed only with NDB tables.STORAGE determines the type of storage used, and can be either of DISK orMEMORY.
TABLESPACE ... STORAGE DISK assigns a table to an NDB Cluster Disk Data tablespace. STORAGE DISK cannot be used in CREATE TABLE unless preceded byTABLESPACE tablespacename.
For STORAGE MEMORY, the tablespace name is optional, thus, you can use TABLESPACE_`tablespacename`_ STORAGE MEMORY or simply STORAGE MEMORY to specify explicitly that the table is in-memory.
See Section 25.6.11, “NDB Cluster Disk Data Tables”, for more information.

Table Partitioning

partitionoptions can be used to control partitioning of the table created withCREATE TABLE.

Not all options shown in the syntax for_partitionoptions_ at the beginning of this section are available for all partitioning types. Please see the listings for the following individual types for information specific to each type, and see Chapter 26, Partitioning, for more complete information about the workings of and uses for partitioning in MySQL, as well as additional examples of table creation and other statements relating to MySQL partitioning.

Partitions can be modified, merged, added to tables, and dropped from tables. For basic information about the MySQL statements to accomplish these tasks, see Section 15.1.9, “ALTER TABLE Statement”. For more detailed descriptions and examples, seeSection 26.3, “Partition Management”.

CREATE TABLE t1 (col1 INT, col2 CHAR(5))  
    PARTITION BY HASH(col1);  
CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATETIME)  
    PARTITION BY HASH ( YEAR(col3) );

You may not use either VALUES LESS THAN orVALUES IN clauses with PARTITION BY HASH.
PARTITION BY HASH uses the remainder of_expr_ divided by the number of partitions (that is, the modulus). For examples and additional information, see Section 26.2.4, “HASH Partitioning”.
The LINEAR keyword entails a somewhat different algorithm. In this case, the number of the partition in which a row is stored is calculated as the result of one or more logical AND operations. For discussion and examples of linear hashing, seeSection 26.2.4.1, “LINEAR HASH Partitioning”.

CREATE TABLE tk (col1 INT, col2 CHAR(5), col3 DATE)  
    PARTITION BY KEY(col3)  
    PARTITIONS 4;

For tables that are partitioned by key, you can employ linear partitioning by using the LINEAR keyword. This has the same effect as with tables that are partitioned by HASH. That is, the partition number is found using the& operator rather than the modulus (seeSection 26.2.4.1, “LINEAR HASH Partitioning”, andSection 26.2.5, “KEY Partitioning”, for details). This example uses linear partitioning by key to distribute data between 5 partitions: 

CREATE TABLE tk (col1 INT, col2 CHAR(5), col3 DATE)  
    PARTITION BY LINEAR KEY(col3)  
    PARTITIONS 5;

The ALGORITHM={1 | 2} option is supported with [SUB]PARTITION BY [LINEAR] KEY.ALGORITHM=1 causes the server to use the same key-hashing functions as MySQL 5.1;ALGORITHM=2 means that the server employs the key-hashing functions implemented and used by default for new KEY partitioned tables in MySQL 5.5 and later. (Partitioned tables created with the key-hashing functions employed in MySQL 5.5 and later cannot be used by a MySQL 5.1 server.) Not specifying the option has the same effect as using ALGORITHM=2. This option is intended for use chiefly when upgrading or downgrading[LINEAR] KEY partitioned tables between MySQL 5.1 and later MySQL versions, or for creating tables partitioned by KEY or LINEAR KEY on a MySQL 5.5 or later server which can be used on a MySQL 5.1 server. For more information, seeSection 15.1.9.1, “ALTER TABLE Partition Operations”.
mysqldump writes this option encased in versioned comments.
ALGORITHM=1 is shown when necessary in the output of SHOW CREATE TABLE using versioned comments in the same manner asmysqldump. ALGORITHM=2 is always omitted from SHOW CREATE TABLE output, even if this option was specified when creating the original table.
You may not use either VALUES LESS THAN orVALUES IN clauses with PARTITION BY KEY.

CREATE TABLE t1 (  
    year_col  INT,  
    some_data INT  
)  
PARTITION BY RANGE (year_col) (  
    PARTITION p0 VALUES LESS THAN (1991),  
    PARTITION p1 VALUES LESS THAN (1995),  
    PARTITION p2 VALUES LESS THAN (1999),  
    PARTITION p3 VALUES LESS THAN (2002),  
    PARTITION p4 VALUES LESS THAN (2006),  
    PARTITION p5 VALUES LESS THAN MAXVALUE  
);

PARTITION ... VALUES LESS THAN ... statements work in a consecutive fashion. VALUES LESS THAN MAXVALUE works to specify“leftover” values that are greater than the maximum value otherwise specified.
VALUES LESS THAN clauses work sequentially in a manner similar to that of the case portions of a switch ... case block (as found in many programming languages such as C, Java, and PHP). That is, the clauses must be arranged in such a way that the upper limit specified in each successive VALUES LESS THAN is greater than that of the previous one, with the one referencing MAXVALUE coming last of all in the list.

CREATE TABLE rc (  
    a INT NOT NULL,  
    b INT NOT NULL  
)  
PARTITION BY RANGE COLUMNS(a,b) (  
    PARTITION p0 VALUES LESS THAN (10,5),  
    PARTITION p1 VALUES LESS THAN (20,10),  
    PARTITION p2 VALUES LESS THAN (50,MAXVALUE),  
    PARTITION p3 VALUES LESS THAN (65,MAXVALUE),  
    PARTITION p4 VALUES LESS THAN (MAXVALUE,MAXVALUE)  
);

Each value used in a VALUES LESS THAN value list must match the type of the corresponding column exactly; no conversion is made. For example, you cannot use the string'1' for a value that matches a column that uses an integer type (you must use the numeral1 instead), nor can you use the numeral1 for a value that matches a column that uses a string type (in such a case, you must use a quoted string: '1').
For more information, seeSection 26.2.1, “RANGE Partitioning”, andSection 26.4, “Partition Pruning”.

CREATE TABLE client_firms (  
    id   INT,  
    name VARCHAR(35)  
)  
PARTITION BY LIST (id) (  
    PARTITION r0 VALUES IN (1, 5, 9, 13, 17, 21),  
    PARTITION r1 VALUES IN (2, 6, 10, 14, 18, 22),  
    PARTITION r2 VALUES IN (3, 7, 11, 15, 19, 23),  
    PARTITION r3 VALUES IN (4, 8, 12, 16, 20, 24)  
);

When using list partitioning, you must define at least one partition using VALUES IN. You cannot useVALUES LESS THAN with PARTITION BY LIST.
Note
For tables partitioned by LIST, the value list used with VALUES IN must consist of integer values only. In MySQL 8.4, you can overcome this limitation using partitioning by LIST COLUMNS, which is described later in this section.

CREATE TABLE lc (  
    a INT NULL,  
    b INT NULL  
)  
PARTITION BY LIST COLUMNS(a,b) (  
    PARTITION p0 VALUES IN( (0,0), (NULL,NULL) ),  
    PARTITION p1 VALUES IN( (0,1), (0,2), (0,3), (1,1), (1,2) ),  
    PARTITION p2 VALUES IN( (1,0), (2,0), (2,1), (3,0), (3,1) ),  
    PARTITION p3 VALUES IN( (1,3), (2,2), (2,3), (3,2), (3,3) )  
);

Partitioning by Generated Columns

Partitioning by generated columns is permitted. For example:

CREATE TABLE t1 (
  s1 INT,
  s2 INT AS (EXP(s1)) STORED
)
PARTITION BY LIST (s2) (
  PARTITION p1 VALUES IN (1)
);

Partitioning sees a generated column as a regular column, which enables workarounds for limitations on functions that are not permitted for partitioning (seeSection 26.6.3, “Partitioning Limitations Relating to Functions”). The preceding example demonstrates this technique:EXP() cannot be used directly in the PARTITION BY clause, but a generated column defined using EXP() is permitted.