13.1.17 CREATE TABLE Syntax CREATE TABLE ... LIKE Syntax CREATE TABLE ... SELECT Syntax Using FOREIGN KEY Constraints Silent Column Specification Changes


    { LIKE old_tbl_name | (LIKE old_tbl_name) }

    col_name column_definition
  | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
      [index_option] ...
  | {INDEX|KEY} [index_name] [index_type] (index_col_name,...)
      [index_option] ...
      [index_name] [index_type] (index_col_name,...)
      [index_option] ...
  | {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (index_col_name,...)
      [index_option] ...
      [index_name] (index_col_name,...) reference_definition
  | CHECK (expr)

    data_type [NOT NULL | NULL] [DEFAULT default_value]
      [COMMENT 'string']

  | INT[(length)] [UNSIGNED] [ZEROFILL]
  | REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
  | DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
  | FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
  | DECIMAL[(length[,decimals])] [UNSIGNED] [ZEROFILL]
  | NUMERIC[(length[,decimals])] [UNSIGNED] [ZEROFILL]
  | DATE
  | TIME
  | YEAR
  | CHAR[(length)] [BINARY]
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | VARCHAR(length) [BINARY]
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | BINARY[(length)]
  | VARBINARY(length)
  | BLOB
      [CHARACTER SET charset_name] [COLLATE collation_name]
      [CHARACTER SET charset_name] [COLLATE collation_name]
      [CHARACTER SET charset_name] [COLLATE collation_name]
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | ENUM(value1,value2,value3,...)
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | SET(value1,value2,value3,...)
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | spatial_type

    col_name [(length)] [ASC | DESC]


    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name

    REFERENCES tbl_name (index_col_name,...)
      [ON DELETE reference_option]
      [ON UPDATE reference_option]


    table_option [[,] 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'
  | KEY_BLOCK_SIZE [=] value
  | MAX_ROWS [=] value
  | MIN_ROWS [=] value
  | PACK_KEYS [=] {0 | 1 | DEFAULT}
  | PASSWORD [=] 'string'
  | UNION [=] (tbl_name[,tbl_name]...)

        { [LINEAR] HASH(expr)
        | [LINEAR] KEY(column_list)
        | RANGE(expr)
        | LIST(expr) }
    [PARTITIONS num]
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY(column_list) }
    [(partition_definition [, partition_definition] ...)]

    PARTITION partition_name
            {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 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]

    [IGNORE | REPLACE] [AS] SELECT ...   (Some valid select statement)

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

Rules for permissible table names are given in Section 9.2, “Schema Object Names”. By default, the table is created in the default database. An error occurs if the table exists, if there is no default database, or if the database does not exist.

The table name can be specified as db_name.tbl_name to create the table in a specific database. This works regardless of whether there is a default database, assuming that the database exists. If you use quoted identifiers, quote the database and table names separately. For example, write `mydb`.`mytbl`, not `mydb.mytbl`.

Cloning or Copying a Table

Use CREATE TABLE ... LIKE to create an empty table based on the definition of another table, including any column attributes and indexes defined in the original table:

CREATE TABLE new_tbl LIKE orig_tbl;

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

To create one table from another, add a SELECT statement at the end of the CREATE TABLE statement:

CREATE TABLE new_tbl SELECT * FROM orig_tbl;

For more information, see Section, “CREATE TABLE ... SELECT Syntax”.

Temporary Tables

You can use the TEMPORARY keyword when creating a table. A TEMPORARY table is visible only to the current session, and is dropped automatically when the session is closed. This means that two different sessions can use the same temporary table name without conflicting with each other or with an existing non-TEMPORARY table of the same name. (The existing table is hidden until the temporary table is dropped.) To create temporary tables, you must have the CREATE TEMPORARY TABLES privilege.


CREATE TABLE does not automatically commit the current active transaction if you use the TEMPORARY keyword.


TEMPORARY tables have a very loose relationship with databases (schemas). Dropping a database does not automatically drop any TEMPORARY tables created within that database. Also, you can create a TEMPORARY table in a nonexistent database if you qualify the table name with the database name in the CREATE TABLE statement. In this case, all subsequent references to the table must be qualified with the database name.

Existing Table with Same Name

The keywords IF NOT EXISTS prevent an error from occurring if the table exists. However, there is no verification that the existing table has a structure identical to that indicated by the CREATE TABLE statement.

Physical Representation

MySQL represents each table by an .frm table format (definition) file in the database directory. The storage engine for the table might create other files as well.

For InnoDB tables, the file storage is controlled by the innodb_file_per_table configuration option. For each InnoDB table created when this option is turned on, the table data and all associated indexes are stored in a .ibd file located inside the database directory. When this option is turned off, all InnoDB tables and indexes are stored in the system tablespace, represented by one or more ibdata* files.

For MyISAM tables, the storage engine creates data and index files. Thus, for each MyISAM table tbl_name, there are three disk files.

tbl_name.frmTable format (definition) file
tbl_name.MYDData file
tbl_name.MYIIndex file

Chapter 14, Storage Engines, describes what files each storage engine creates to represent tables. If a table name contains special characters, the names for the table files contain encoded versions of those characters as described in Section 9.2.3, “Mapping of Identifiers to File Names”.

Data Types and Attributes for Columns

data_type represents the data type in a column definition. spatial_type represents a spatial data type. The data type syntax shown is representative only. For a full description of the syntax available for specifying column data types, as well as information about the properties of each type, see Chapter 11, Data Types, and Section 11.5, “Extensions for Spatial Data”.

Some attributes do not apply to all data types. AUTO_INCREMENT applies only to integer and floating-point types. DEFAULT does not apply to the BLOB or TEXT types.

The TABLESPACE and STORAGE table options were both introduced in MySQL 5.1.6. In MySQL 5.1, they are employed only with NDBCLUSTER tables. The tablespace named tablespace_name must already have been created using CREATE TABLESPACE. STORAGE determines the type of storage used (disk or memory), and can be one of DISK, MEMORY, or DEFAULT.

TABLESPACE ... STORAGE DISK assigns a table to a MySQL Cluster Disk Data tablespace. See Section 17.5.12, “MySQL Cluster Disk Data Tables”, for more information.


A STORAGE clause cannot be used in a CREATE TABLE statement without a TABLESPACE clause.

The ENGINE table option specifies the storage engine for the table.

Storage Engines

The ENGINE table option specifies the storage engine for the table, using one of the names shown in the following table. The engine name can be unquoted or quoted. The quoted name 'DEFAULT' is equivalent to specifying the default storage engine name.

Storage EngineDescription
ARCHIVEThe archiving storage engine. See Section 14.12, “The ARCHIVE Storage Engine”.
CSVTables that store rows in comma-separated values format. See Section 14.13, “The CSV Storage Engine”.
EXAMPLEAn example engine. See Section 14.10, “The EXAMPLE Storage Engine”.
FEDERATEDStorage engine that accesses remote tables. See Section 14.11, “The FEDERATED Storage Engine”.
HEAPThis is a synonym for MEMORY.
InnoDBTransaction-safe tables with row locking and foreign keys. See Section 14.6, “The InnoDB Storage Engine”.
MEMORYThe data for this storage engine is stored only in memory. See Section 14.9, “The MEMORY Storage Engine”.
MERGEA collection of MyISAM tables used as one table. Also known as MRG_MyISAM. See Section 14.8, “The MERGE Storage Engine”.
MyISAMThe binary portable storage engine that is the default storage engine used by MySQL. See Section 14.5, “The MyISAM Storage Engine”.
NDBCLUSTERClustered, fault-tolerant, memory-based tables. Also known as NDB. See Chapter 17, MySQL Cluster NDB 6.1 - 7.1.

If a storage engine is specified that is not available, MySQL uses the default engine instead. Normally, this is MyISAM. For example, if a table definition includes the ENGINE=INNODB option but the MySQL server does not support INNODB tables, the table is created as a MyISAM table. This makes it possible to have a replication setup where you have transactional tables on the master but tables created on the slave are nontransactional (to get more speed). In MySQL 5.1, a warning occurs if the storage engine specification is not honored.

Engine substitution can be controlled by the setting of the NO_ENGINE_SUBSTITUTION SQL mode, as described in Section 5.1.7, “Server SQL Modes”.


The older TYPE option was synonymous with ENGINE. TYPE has been deprecated since MySQL 4.0 but is still supported for backward compatibility in MySQL 5.1 (excepting MySQL 5.1.7). Since MySQL 5.1.8, it produces a warning. It is removed in MySQL 5.5. You should not use TYPE in any new applications, and you should immediately begin conversion of existing applications to use ENGINE instead. (See the Release Notes for MySQL 5.1.8.)

Optimizing Performance

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

Creating Partitioned Tables

partition_options can be used to control partitioning of the table created with CREATE TABLE.


Not all options shown in the syntax for partition_options 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 18, 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.

If used, a partition_options clause begins with PARTITION BY. This clause contains the function that is used to determine the partition; the function returns an integer value ranging from 1 to num, where num is the number of partitions. (The maximum number of user-defined partitions which a table may contain is 1024; the number of subpartitions—discussed later in this section—is included in this maximum.) The choices that are available for this function in MySQL 5.1 are shown in the following list:


The expression (expr) used in a PARTITION BY clause cannot refer to any columns not in the table being created; beginning with MySQL 5.1.23, such references are specifically not permitted and cause the statement to fail with an error. (Bug #29444)

Each partition may be individually defined using a partition_definition clause. The individual parts making up this clause are as follows:

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 13.1.7, “ALTER TABLE Syntax”. For more detailed descriptions and examples, see Section 18.3, “Partition Management”.


The original CREATE TABLE statement, including all specifications and table options are stored by MySQL when the table is created. The information is retained so that if you change storage engines, collations or other settings using an ALTER TABLE statement, the original table options specified are retained. This enables you to change between InnoDB and MyISAM table types even though the row formats supported by the two engines are different.

Because the text of the original statement is retained, but due to the way that certain values and options may be silently reconfigured (such as the ROW_FORMAT), the active table definition (accessible through DESCRIBE or with SHOW TABLE STATUS) and the table creation string (accessible through SHOW CREATE TABLE) will report different values.