6.5.3 CREATE TABLE Syntax

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

or

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(] LIKE old_tbl_name [)];

create_definition:
    col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT]
            [PRIMARY KEY] [COMMENT 'string'] [reference_definition]
  | PRIMARY KEY (index_col_name,...)
  | KEY [index_name] (index_col_name,...)
  | INDEX [index_name] (index_col_name,...)
  | UNIQUE [INDEX] [index_name] (index_col_name,...)
  | FULLTEXT [INDEX] [index_name] (index_col_name,...)
  | [CONSTRAINT symbol] FOREIGN KEY [index_name] (index_col_name,...)
            [reference_definition]
  | CHECK (expr)

type:
    TINYINT[(length)] [UNSIGNED] [ZEROFILL]
  | SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
  | MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]
  | INT[(length)] [UNSIGNED] [ZEROFILL]
  | INTEGER[(length)] [UNSIGNED] [ZEROFILL]
  | BIGINT[(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]
  | CHAR(length) [BINARY]
  | VARCHAR(length) [BINARY]
  | DATE
  | TIME
  | TIMESTAMP
  | DATETIME
  | TINYBLOB
  | BLOB
  | MEDIUMBLOB
  | LONGBLOB
  | TINYTEXT
  | TEXT
  | MEDIUMTEXT
  | LONGTEXT
  | ENUM(value1,value2,value3,...)
  | SET(value1,value2,value3,...)

index_col_name:
        col_name [(length)]

reference_definition:
        REFERENCES tbl_name [(index_col_name,...)]
                   [MATCH FULL | MATCH PARTIAL]
                   [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:
    TYPE = {BDB | HEAP | ISAM | InnoDB | MERGE | MRG_MYISAM | MYISAM }
  | AUTO_INCREMENT = #
  | AVG_ROW_LENGTH = #
  | CHECKSUM = {0 | 1}
  | COMMENT = 'string'
  | MAX_ROWS = #
  | MIN_ROWS = #
  | PACK_KEYS = {0 | 1 | DEFAULT}
  | PASSWORD = 'string'
  | DELAY_KEY_WRITE = {0 | 1}
  | ROW_FORMAT= { default | dynamic | fixed | compressed }
  | RAID_TYPE= {1 | STRIPED | RAID0 } RAID_CHUNKS=#  RAID_CHUNKSIZE=#
  | UNION = (table_name,[table_name...])
  | INSERT_METHOD= {NO | FIRST | LAST }
  | DATA DIRECTORY='absolute path to directory'
  | INDEX DIRECTORY='absolute path to directory'

select_statement:
        [IGNORE | REPLACE] SELECT ...  (Some legal select statement)

CREATE TABLE creates a table with the given name. Rules for allowable table names are given in section 6.1.2 Database, Table, Index, Column, and Alias Names. By default, the table is created in the current database. An error occurs if the table already exists, if there is no current database, or if the database does not exist.

In MySQL Version 3.22 or later, 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 current database.

From MySQL Version 3.23, you can use the TEMPORARY keyword when you create a table. The temporary table is visible only to the current connection, and will be deleted automatically when the connection is closed. This means that two different connections can both use the same temporary table name without conflicting with each other or with an existing table of the same name. (The existing table is hidden until the temporary table is deleted.) From MySQL 4.0.2 on, you must have the CREATE TEMPORARY TABLES privilege to be able to create temporary tables.

In MySQL Version 3.23 or later, you can use the keywords IF NOT EXISTS so that an error does not occur if the table already exists. Note that there is no verification that the existing table has a structure identical to that indicated by the CREATE TABLE statement.

From version 4.1.0, the attribute SERIAL can be used as an alias for BIGINT NOT NULL AUTO_INCREMENT UNIQUE. This is compatibility feature.

As of MySQL 3.23, you can create one table from another by adding a SELECT statement at the end of the CREATE TABLE statement:

CREATE TABLE new_tbl SELECT * FROM orig_tbl;

Indexes are not carried over to the new table, and some conversion of column types may occur. For example, the AUTO_INCREMENT attribute is not preserved, and VARCHAR columns may become CHAR columns.

As of MySQL 4.1, you can explicitly specify the type for a generated column:

CREATE TABLE foo (a tinyint not null) SELECT b+1 AS 'a' FROM bar;

In MySQL 4.1, you can also use LIKE to create a table based on the definition of another table, including any column attributes and indexes the original table has:

CREATE TABLE new_tbl LIKE orig_tbl;

Each table tbl_name is represented by some files in the database directory. In the case of MyISAM-type tables you will get:

For more information on the properties of the various column types, see section 6.2 Column Types:

• If neither NULL nor NOT NULL is specified, the column is treated as though NULL had been specified.
• An integer column may have the additional attribute AUTO_INCREMENT. When you insert a value of NULL (recommended) or 0 into an indexed AUTO_INCREMENT column, the column is set to the next sequence value. Typically this is value+1, where value is the largest value for the column currently in the table. AUTO_INCREMENT sequences begin with 1. See section 9.1.3.31 mysql_insert_id(). If you delete the row containing the maximum value for an AUTO_INCREMENT column, the value will be reused for an ISAM or BDB table, but not for a MyISAM or InnoDB table. If you delete all rows in the table with DELETE FROM table_name (without a WHERE) in AUTOCOMMIT mode, the sequence starts over for all table types except InnoDB. Note: there can be only one AUTO_INCREMENT column per table, it must be indexed and it can't have a DEFAULT value. In MySQL Version 3.23, an AUTO_INCREMENT column will work properly only if it contains only positive values. Inserting a negative number is regarded as inserting a very large positive number. This is done to avoid precision problems when numbers ``wrap'' over from positive to negative and also to ensure that one doesn't accidentally get an AUTO_INCREMENT column that contains 0. In MyISAM and BDB tables you can specify AUTO_INCREMENT secondary column in a multiple-column key. See section 3.5.9 Using AUTO_INCREMENT. To make MySQL compatible with some ODBC applications, you can find the AUTO_INCREMENT value for the last inserted row with the following query:

  SELECT * FROM tbl_name WHERE auto_col IS NULL
  

• CREATE TABLE implicitly commits the current InnoDB transaction if MySQL binary logging is used.
• NULL values are handled differently for TIMESTAMP columns than for other column types. You cannot store a literal NULL in a TIMESTAMP column; setting the column to NULL sets it to the current date and time. Because TIMESTAMP columns behave this way, the NULL and NOT NULL attributes do not apply in the normal way and are ignored if you specify them. On the other hand, to make it easier for MySQL clients to use TIMESTAMP columns, the server reports that such columns may be assigned NULL values (which is true), even though TIMESTAMP never actually will contain a NULL value. You can see this when you use DESCRIBE tbl_name to get a description of your table. Note that setting a TIMESTAMP column to 0 is not the same as setting it to NULL, because 0 is a valid TIMESTAMP value.
• A DEFAULT value has to be a constant, it cannot be a function or an expression. If no DEFAULT value is specified for a column, MySQL automatically assigns one, as follows. If the column may take NULL as a value, the default value is NULL. If the column is declared as NOT NULL, the default value depends on the column type:
  • For numeric types other than those declared with the AUTO_INCREMENT attribute, the default is 0. For an AUTO_INCREMENT column, the default value is the next value in the sequence.
  • For date and time types other than TIMESTAMP, the default is the appropriate zero value for the type. For the first TIMESTAMP column in a table, the default value is the current date and time. See section 6.2.2 Date and Time Types.
  • For string types other than ENUM, the default value is the empty string. For ENUM, the default is the first enumeration value.
  Default values must be constants. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE.
• A comment for a column may be specified with the COMMENT option. The comment is displayed by the SHOW CREATE TABLE statement, and by SHOW FULL COLUMNS. This option is available as of MySQL 4.1. (It is allowed but ignored in earlier versions.)
• KEY is a synonym for INDEX.
• In MySQL, a UNIQUE key can have only distinct values. An error occurs if you try to add a new row with a key that matches an existing row.
• A PRIMARY KEY is a unique KEY where all key columns must be defined as NOT NULL. If they are not explicitly declared as NOT NULL, it will be done implicitly (and quietly). In MySQL the key is named PRIMARY. A table can have only one PRIMARY KEY. If you don't have a PRIMARY KEY and some applications ask for the PRIMARY KEY in your tables, MySQL will return the first UNIQUE key, which doesn't have any NULL columns, as the PRIMARY KEY.
• A PRIMARY KEY can be a multiple-column index. However, you cannot create a multiple-column index using the PRIMARY KEY key attibute in a column specification. Doing so will mark only that single column as primary. You must use a separate PRIMARY KEY(index_col_name, ...) clause.
• A UNIQUE index is one in which all values in the index must be distinct. The exception to this is that if a column in the index is allowed to contain NULL values, it may contain multiple NULL values. This exception does not apply to BDB tables, which allow only a single NULL.
• If the PRIMARY or UNIQUE key consists of only one column and this is of type integer, you can also refer to it as _rowid (new in Version 3.23.11).
• If you don't assign a name to an index that is not a PRIMARY KEY, the index will be assigned the same name as the first index_col_name, with an optional suffix (_2, _3, ...) to make it unique. You can see index names for a table using SHOW INDEX FROM tbl_name. See section 4.5.7.1 Retrieving information about Database, Tables, Columns, and Indexes.
• Only the MyISAM, InnoDB, and BDB table types support indexes on columns that can have NULL values. In other cases you must declare such columns NOT NULL or an error results.
• With col_name(length) syntax in an index specification, you can create an index that uses only the first length bytes of a CHAR or VARCHAR column. This can make the index file much smaller. See section 5.4.4 Column Indexes.
• Only the MyISAM table type supports indexing on BLOB and TEXT columns. When putting an index on a BLOB or TEXT column you MUST always specify the length of the index, up to 255 bytes. For example:

  CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));
  

• When you use ORDER BY or GROUP BY with a TEXT or BLOB column, the server sorts values using only the initial number of bytes indicated by the max_sort_length server variable. See section 6.2.3.2 The BLOB and TEXT Types.
• In MySQL Version 3.23.23 or later, you can also create special FULLTEXT indexes. They are used for full-text search. Only the MyISAM table type supports FULLTEXT indexes. They can be created only from CHAR, VARCHAR, and TEXT columns. Indexing always happens over the entire column; partial indexing is not supported. See section 6.8 MySQL Full-text Search for details of operation.
• In MySQL Version 3.23.44 or later, InnoDB tables support checking of foreign key constraints. See section 7.5 InnoDB Tables. Note that the FOREIGN KEY syntax in InnoDB is more restrictive than the syntax presented above: The columns of the referenced table must always be explicitly named. InnoDB supports both ON DELETE and ON UPDATE actions on foreign keys as of MySQL 3.23.50 and 4.0.8, respectively. See the InnoDB manual section for the precise syntax. See section 7.5 InnoDB Tables. For other table types, MySQL Server does parse the FOREIGN KEY, CHECK, and REFERENCES syntax in CREATE TABLE commands, but without further action being taken. See section 1.8.4.5 Foreign Keys.
• For MyISAM and ISAM tables, each NULL column takes one bit extra, rounded up to the nearest byte. The maximum record length in bytes can be calculated as follows:

  row length = 1
               + (sum of column lengths)
               + (number of NULL columns + delete_flag + 7)/8
               + (number of variable-length columns)
  

  delete_flag is 1 for tables with static record format. Static tables use a bit in the row record for a flag that indicates whether the row has been deleted. delete_flag is 0 for dynamic tables because the flag is stored in the dynamic row header. These calculations do not apply for InnoDB tables, for which storage size is not different for NULL columns compared to NOT NULL columns.
• The table_options and SELECT options are only implemented in MySQL Version 3.23 and above. The TYPE option for specifying the table type takes the following values:

  Table type	Description
  BDB or BerkeleyDB	Transaction-safe tables with page locking. See section 7.6 BDB or BerkeleyDB Tables.
  HEAP	The data for this table is only stored in memory. See section 7.4 HEAP Tables.
  ISAM	The original storage engine. See section 7.3 ISAM Tables.
  InnoDB	Transaction-safe tables with row locking. See section 7.5 InnoDB Tables.
  MERGE	A collection of MyISAM tables used as one table. See section 7.2 MERGE Tables.
  MRG_MyISAM	An alias for MERGE.
  MyISAM	The new binary portable storage engine that is the replacement for ISAM. See section 7.1 MyISAM Tables.

  See section 7 MySQL Table Types. If a table type is specified, and that particular type is not available, MySQL will use MyISAM instead. For example, if a table definition includes the TYPE=BDB option but the MySQL server does not support BDB tables, the table will be 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 non-transactional (to get more speed). In MySQL 4.1.1 you get a warning if the specified table type is not honored. The other table options are used to optimise the behaviour of the table. In most cases, you don't have to specify any of them. The options work for all table types, unless otherwise indicated:

  Option	Description
  AUTO_INCREMENT	The next AUTO_INCREMENT value you want to set for your table (MyISAM only; to set the first auto-increment value for an InnoDB table, insert a dummy row with a value one less, and delete the dummy row).
  AVG_ROW_LENGTH	An approximation of the average row length for your table. You only need to set this for large tables with variable size records.
  CHECKSUM	Set this to 1 if you want MySQL to maintain a checksum for all rows (makes the table a little slower to update but makes it easier to find corrupted tables) (MyISAM only).
  COMMENT	A 60-character comment for your table.
  MAX_ROWS	Maximum number of rows you plan to store in the table.
  MIN_ROWS	Minimum number of rows you plan to store in the table.
  PACK_KEYS	Set this to 1 if you want to have a smaller index. This usually makes updates slower and reads faster (MyISAM and ISAM only). Setting this to 0 will disable all packing of keys. Setting this to DEFAULT (MySQL 4.0) will tell the storage engine to only pack long CHAR/VARCHAR columns.
  PASSWORD	Encrypt the `.frm' file with a password. This option doesn't do anything in the standard MySQL version.
  DELAY_KEY_WRITE	Set this to 1 if want to delay key table updates until the table is closed (MyISAM only).
  ROW_FORMAT	Defines how the rows should be stored. Currently this option only works with MyISAM tables, which supports the DYNAMIC and FIXED row formats. See section 7.1.2 MyISAM Table Formats.

  When you use a MyISAM table, MySQL uses the product of MAX_ROWS * AVG_ROW_LENGTH to decide how big the resulting table will be. If you don't specify any of the above options, the maximum size for a table will be 4G (or 2G if your operating systems only supports 2G tables). The reason for this is just to keep down the pointer sizes to make the index smaller and faster if you don't really need big files. If you don't use PACK_KEYS, the default is to only pack strings, not numbers. If you use PACK_KEYS=1, numbers will be packed as well. When packing binary number keys, MySQL will use prefix compression. This means that you will only get a big benefit from this if you have many numbers that are the same. Prefix compression means that every key needs one extra byte to indicate how many bytes of the previous key are the same for the next key (note that the pointer to the row is stored in high-byte-first order directly after the key, to improve compression). This means that if you have many equal keys on two consecutive rows, all following ``same'' keys will usually only take 2 bytes (including the pointer to the row). Compare this to the ordinary case where the following keys will take storage_size_for_key + pointer_size (usually 4). On the other hand, if all keys are totally different, you will use 1 byte more per key, if the key isn't a key that can have NULL values. (In this case the packed key length will be stored in the same byte that is used to mark if a key is NULL.)
• As of MySQL 3.23, if you specify a SELECT after the CREATE statement, MySQL will create new fields for all elements in the SELECT. For example:

  mysql> CREATE TABLE test (a INT NOT NULL AUTO_INCREMENT,
      ->        PRIMARY KEY (a), KEY(b))
      ->        TYPE=MyISAM SELECT b,c FROM test2;
  

  This will create a MyISAM table with three columns, a, b, and c. Notice that the columns from the SELECT statement are appended to the right side of the table, not overlapped onto it. Take the following example:

  mysql> SELECT * FROM foo;
  +---+
  | n |
  +---+
  | 1 |
  +---+
  
  mysql> CREATE TABLE bar (m INT) SELECT n FROM foo;
  Query OK, 1 row affected (0.02 sec)
  Records: 1  Duplicates: 0  Warnings: 0
  
  mysql> SELECT * FROM bar;
  +------+---+
  | m    | n |
  +------+---+
  | NULL | 1 |
  +------+---+
  1 row in set (0.00 sec)
  

  For each row in table foo, a row is inserted in bar with the values from foo and default values for the new columns. CREATE TABLE ... SELECT will not automatically create any indexes for you. This is done intentionally to make the command as flexible as possible. If you want to have indexes in the created table, you should specify these before the SELECT statement:

  mysql> CREATE TABLE bar (UNIQUE (n)) SELECT n FROM foo;
  

  If any errors occur while copying the data to the table, it will automatically be deleted. You can precede the SELECT by IGNORE or REPLACE to indicate how to handle records that duplicate unique key values. With IGNORE, new records that duplicate an existing record on a unique key value are discarded. With REPLACE, new records replace records that have the same unique key value. If neither IGNORE nor REPLACE are specified, duplicate unique key values result in an error. To ensure that the update log/binary log can be used to re-create the original tables, MySQL will not allow concurrent inserts during CREATE TABLE ... SELECT.
• The RAID_TYPE option will help you to exceed the 2G/4G limit for the MyISAM datafile (not the index file) on operating systems that don't support big files. Note that this option is not recommended for filesystem that supports big files! You can get more speed from the I/O bottleneck by putting RAID directories on different physical disks. RAID_TYPE will work on any OS, as long as you have configured MySQL with --with-raid. For now the only allowed RAID_TYPE is STRIPED (1 and RAID0 are aliases for this). If you specify RAID_TYPE=STRIPED for a MyISAM table, MyISAM will create RAID_CHUNKS subdirectories named 00, 01, 02 in the database directory. In each of these directories MyISAM will create a table_name.MYD. When writing data to the datafile, the RAID handler will map the first RAID_CHUNKSIZE *1024 bytes to the first file, the next RAID_CHUNKSIZE *1024 bytes to the next file and so on.
• UNION is used when you want to use a collection of identical tables as one. This only works with MERGE tables. See section 7.2 MERGE Tables. For the moment you need to have SELECT, UPDATE, and DELETE privileges on the tables you map to a MERGE table. All mapped tables must be in the same database as the MERGE table.
• If you want to insert data in a MERGE table, you have to specify with INSERT_METHOD into with table the row should be inserted. See section 7.2 MERGE Tables. This option was introduced in MySQL 4.0.0.
• In the created table the PRIMARY key will be placed first, followed by all UNIQUE keys and then the normal keys. This helps the MySQL optimiser to prioritise which key to use and also more quickly detect duplicated UNIQUE keys.
• By using DATA DIRECTORY="directory" or INDEX DIRECTORY="directory" you can specify where the storage engine should put it's table and index files. Note that the directory should be a full path to the directory (not relative path). This only works for MyISAM tables in MySQL 4.0, when you are not using the --skip-symlink option. See section 5.6.1.2 Using Symbolic Links for Tables.

Subsections

• 6.5.3.1 Silent Column Specification Changes

User Comments

Now that InnoDB tables support this, it would be a welcome addition, to mention which CONSTRAINT 'symbol'(s) is/are supported (or refer to whatever standard applies)

"If you delete all rows in the table with DELETE FROM table_name (without a WHERE) in AUTOCOMMIT mode, the sequence starts over for all table types."

This is not true anymore for Mysql 4.x.

MySQL AB declares "IDENTITY as a synonym for automatically incremented keys" in the manuals at 1.5.1.

How exactly it is used, while table creation or during query is not specified anywhere in the document?

LAST_INSERT_ID(), SELECT LAST_INSERT_ID() is used to stimulate the sequences, with respect to the same how IDENTITY can be used with the AUTO_INCREMENT feature of table creation?

Any answers from MySQL AB or any others......?

Add your own comment.