CREATE

The CREATE syntax can be one of the following:

CREATE DATABASE [IF NOT EXISTS] database_name CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table_name (field _name[(length)],... ) CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table_name [(create _definition,...)]  [table_options] [select_statement]

The create_definition syntax can be any of the following:

field_name type [NOT NULL | NULL] [DEFAULT default_value]    [AUTO_INCREMENT] [PRIMARY KEY] [reference_definition] PRIMARY KEY (index_field_name,...) KEY [index_name] (index_field_name,...) INDEX [index_name] (index_field_name,...) UNIQUE [INDEX] [index_name] (index_field_name,...) FULLTEXT [INDEX] [index_name] (index_field_name,...) [CONSTRAINT symbol] FOREIGN KEY [index_name] (index_field_name,...)  [reference_definition] CHECK (expr)

The type syntax can be any of the following:

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

The index_field_name syntax is as follows:

field_name [(length)]

The reference_definition syntax is as follows:

REFERENCES table_name [(index_field_name,...)] [MATCH FULL    | MATCH PARTIAL] [ON DELETE reference_option] [ON UPDATE  reference_option]

The reference_option syntax is as follows:

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

The table_options syntax can be any of the following:

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"

The select_statement syntax can be as follows:

[IGNORE | REPLACE] SELECT ...  (select statement)

The CREATE statement creates a database, table, or index.

MySQL returns an error if the database or table already exists unless the IF NOT EXISTS clause is used.

TEMPORARY tables exist only for as long as the connection is active. You need to have CREATE TEMPORARY TABLES permission to do this.

Fields definitions default to NULL. Numeric fields default to 0 (except with AUTO_ INCREMENT), and string fields default to an empty string (except for ENUM fields, which default to the first option). Date and time fields by default fill the field with zeros.

AUTO_INCREMENT fields begin counting at 1 by default and increment by one each time a new record is added.

KEY and INDEX are synonyms in this context.

A PRIMARY KEY specifies that the index cannot contain duplicates, and the field (or combination of fields) must be specified as NOT NULL.

UNIQUE specifies that the index cannot contain duplicates.

The RAID_TYPE option helps operating systems that cannot support large files to overcome the file size limit. The STRIPED option is the only one currently used. For MyISAM tables, this creates subdirectories inside the database directory, each containing a portion of the data file. The first 1024 * RAID_CHUNKSIZE bytes go into the first portion, the next 1024 * RAID_CHUNKSIZE bytes go into the next portion, and so on.

The DATA DIRECTORY="directory" and INDEX DIRECTORY="directory" options specify absolute paths to where the data or index file is stored.

The PACK_KEYS=1 option packs numeric fields in the index for MyISAM tables (as well as strings, which it does by default). This is only useful if you have indexes with many duplicate numbers.

Use AVG_ROW_LENGTH to give MySQL an idea of the average row length for the table. This is only useful where the table is large and has variable size records.

CHECKSUM can be set to 1 for MyISAM tables if you want to keep a checksum for all rows, which makes it easier to repair the table if it becomes corrupted but does slow down the table.

COMMENT is a comment of up to 60 characters for the table.

MAX_ROWS and MIN_ROWS specify the maximum and minimum rows, respectively, that you plan to store in the table.

PASSWORD encrypts the data definition file (.frm) with a password.

DELAY_KEY_WRITE causes MySQL to wait until a MyISAM table is closed before updating the index, which speeds up mass UPDATEs and INSERTs.

ROW_FORMAT specifies whether a MyISAM table should be FIXED or DYNAMIC.