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

Description: Creates a table called table_name. The table name can be specified as database_name.table_name to create a table in a database not currently selected.

TEMPORARY option creates a table that will be dropped automatically when the database connection closes (version 3.23 and later).

IF NOT EXISTS option suppresses the error that will be returned if you try to create a table when another table with that name currently exists (version 3.23 and later).

The create_definition for each column can contain the following for defining a column:

• column_name column_type [NOT NULL|NULL] [DEFAULT default_value] [AUTO_INCREMENT] [PRIMARY KEY] [reference_definition]

and any of the following for defining an index on a column:

• PRIMARY KEY (column_list)

• {INDEX|KEY} [index_name] (column_list)

• UNIQUE [index_name] (column_list)

• FULLTEXT [index_name] (column_list)

• [CONSTRAINT symbol] FOREIGN KEY index_name (column_list) [reference_definition]

• CHECK (expression)

For each column definition

• column_type can be any valid column type as detailed in Appendix A, “Data Type Reference.”

• If neither NULL nor NOT NULL is specified, behavior will be as if NULL had been specified.

• If DEFAULT is specified, whenever an insert occurs, default_value will be inserted unless another value is specified.

• If DEFAULT is not specified, on nullable columns, MySQL will apply a default value of NULL, and on NOT NULL defined columns will apply a default of 0 for numeric types, the current date and time on TIMESTAMP columns, the zero value for other temporal columns, and the empty string for string types (apart from ENUM, which will default to the first member).

• AUTO_INCREMENT on an integer column will cause the previous largest value plus one to be inserted whenever an insert occurs, provided that that column's value is not set, or NULL or 0 is inserted into it; a table can have only one AUTO_INCREMENT column, and it must be an index.

For index definitions, column_list is one or more column names (comma-separated if more than one). The first column name will be used as the index name if index_name is not specified.

PRIMARY KEY specifies a primary key (unique and with no values set to NULL). INDEX and KEY are synonymous, creating a non-unique index. UNIQUE specifies a unique index, and on versions 3.23.23 and later, and with MyISAM tables, you can use FULLTEXT to specify a full-text index.

The reference_definition, FOREIGN KEY, and CHECK do nothing and are included for compatibility of SQL with other systems.

table_options allow you to specify a number of options for the table. This can include the table type, specified as TYPE=table_type, where table_type is one of

• ISAM— The original type of MySQL table

• MyISAM— The default, the newer storage type replacing ISAM

• MERGE or the synonym MRG_MERGE— A collection of MyISAM tables treated as one

• BDB— The Berkeley transaction-safe table type with page locking

• InnoDB— The transaction-safe table type with row locking

• HEAP— A table stored only in memory

Other table_options can include the following:

• AUTO_INCREMENT=value— Sets the starting value of the AUTO_INCREMENT column (MyISAM tables only)

• AVG_ROW_LENGTH=value— Sets the approximate length of a table row on tables with variable-sized columns and of large size (rarely used)

• CHECKSUM={0|1}— Set to 1 causes MySQL to maintain a checksum on the data in all rows, making it easier to spot corrupted tables but adding a small performance overhead

• COMMENT=”comment_text”— Specifies a comment for the table, up to 60 characters long

• MAX_ROWS=value— Sets the maximum number of rows allowed to be stored in a table (may help performance)

• MIN_ROWS=value— Sets the minimum number of rows you expect to store in a table (may help performance)

• PACK_KEYS={0|1|DEFAULT}— If set to 1 (MyISAM and ISAM tables only), makes indexes smaller and faster but slows down updates; if set to DEFAULT (versions 4.0 and later), causes only long CHAR and VARCHAR column indexes to be packed

• PASSWORD=”password”— Encrypts the .frm file with a password (does nothing on standard MySQL versions)

• DELAY_KEY_WRITE={0|1}— If set to 1, delays the updating of keys until the table is not in use (MyISAM tables only)

• ROW_FORMAT={DEFAULT|DYNAMIC|FIXED|COMPRESSED}— Defines how rows should be stored (MyISAM tables only)

• RAID_TYPE=STRIPED— Helps get around the size limitation imposed by the operating system (typically 2GB or 4GB) for MyISAM data files by splitting up the data file into chunks

• UNION=(table_name[,table_name...])— Works on a MERGE table to combine several other tables into one

• INSERT_METHOD={NO|FIRST|LAST}— Must be specified on a MERGE table if you intend to insert data into it, to define how inserts should occur

• DATA DIRECTORY=”/path/to/directory”— Specifies where data files should be stored in the file system (version 4.0 and later, with MyISAM tables only)

• INDEX DIRECTORY=”/path/to/directory”— Specifies where index files should be stored in the file system (version 4.0 and later, with MyISAM tables only)

By using a select_statement, the table will be created and populated using the resultset from a SELECT query (the resulting columns and data). The new table will have any columns and indexes specified in the create_definition list, followed by any columns created as a result of the SELECT. Without a create_definition, only the columns of the SELECT resultset will be created in the new table.

Description in: Day 6

See also: ALTER TABLE, DROP TABLE, DESCRIBE, SELECT