MENU
Table Definitions
| 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] select_statement |
| CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name |
{ LIKE old_tbl_name | (LIKE old_tbl_name) } |
| create_definition: | 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] ... | [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...) [index_option] ... | {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (index_col_name,...) [index_option] ... | [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) reference_definition | CHECK (expr) |
| column_definition: | data_type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string'] [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}] [reference_definition] |
| data_type: | BIT[(length)] | 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] | DATE | TIME | TIMESTAMP | DATETIME | YEAR | CHAR[(length)] [CHARACTER SET charset_name] [COLLATE collation_name] | VARCHAR(length) [CHARACTER SET charset_name] [COLLATE collation_name] | BINARY[(length)] | VARBINARY(length) | TINYBLOB | BLOB | MEDIUMBLOB | LONGBLOB | TINYTEXT [BINARY] [CHARACTER SET charset_name] [COLLATE collation_name] | TEXT [BINARY] [CHARACTER SET charset_name] [COLLATE collation_name] | MEDIUMTEXT [BINARY] [CHARACTER SET charset_name] [COLLATE collation_name] | LONGTEXT [BINARY] [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 |
| index_col_name: | col_name [(length)] [ASC | DESC] |
| index_type: | USING {BTREE | HASH} |
| index_option: | KEY_BLOCK_SIZE [=] value | index_type | WITH PARSER parser_name | COMMENT 'string' |
| reference_definition: | REFERENCES tbl_name (index_col_name,...) [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE] [ON DELETE reference_option] [ON UPDATE reference_option] |
| reference_option: | RESTRICT | CASCADE | SET NULL | NO ACTION |
| To create a table in a particular database, use db_name.tbl_name. | |
| A TEMPORARY table is valid only during the current connection. Two connections can use the same temporary table name without conflicting with each other. | |
| IF NOT EXISTS prevents an error when the table already exists. If a table of the same name already exists, the new definition is not adopted. | |
| Integer and floating-point types can have the AUTO_INCREMENT attribute. If you insert NULL or 0 into such a type, the column is set to the largest value for the column plus 1. A table can only contain one AUTO_INCREMENT column. It must be indexed. It must not have a DEFAULT value. Inserting a negative number is assumed to be inserting a large positive number. | |
| BLOB or TEXT types should not have the DEFAULT attribute. | |
| NOT NULL requires the column to be filled during an insertion. The default is NULL. | |
| The DEFAULT value must be a constant, not a function or expression. CURRENT_TIMESTAMP is the exception. | |
| The COMMENT value can be up to 1024 characters long. | |
| KEY and PRIMARY KEY are the same. A table can only have one PRIMARY KEY, which is a NOT NULL unique index. A PRIMARY KEY is stored first, followed by the UNIQUE indexes, and then the other indexes. To specify a PRIMARY KEY that spans multiple columns, use a separate PRIMARY KEY(index_col_name,…) clause. The name of a PRIMARY KEY is PRIMARY. If you do not assign a name for other indexes, they will be assigned the same name as the first indexed column, with an optional suffix (_2,_3…). |
|
| An error will be encountered when you try to insert, for a UNIQUE column, a value that already exists. | |
| Some storage engines allow you to be USING an index type when creating an index. The WITH PARSER option, usable only with FULLTEXT indexes, associates a parser plugin with the index if full-text indexing and searching operations need special handling. For CHAR, VARCHAR, BINARY, and VARBINARY, use the col_name(length) syntax to specify an index prefix length. Specifying a prefix length is mandatory for indexes of BLOB and TEXT types. | |
| The CHECK clause is ignored. | |
| INDEX, CONSTRAINT and FOREIGN KEY will be discussed in 5.11. | |
| table_options: | table_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' | 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} | STATS_AUTO_RECALC [=] {DEFAULT|0|1} | STATS_PERSISTENT [=] {DEFAULT|0|1} | STATS_SAMPLE_PAGES [=] value | UNION [=] (tbl_name[,tbl_name]...) |
| AUTO_INCREMENT sets the initial AUTO_INCREMENT value for the table. | |
| AVG_ROW_LENGTH specifies the approximated average row length for the table. For MyISAM, MySQL uses the product of MAX_ROWS and AVG_ROW_LENGTH to decide how big the table is. | |
| Values for CHARACTER SET and COLLATE are listed in 5.2.2. | |
| Setting 1 to CHECKSUM causes MySQL to maintain a live checksum for all rows. This makes it easier to find corrupted tables. | |
| Setting 1 to DELAY_KEY_WRITE delays key updates until the table is closed. (MyISAM only) | |
| If you want to use a MERGE table, specify for INSERT_METHOD FIRST or LAST, to have insertions go to the first or last table respectively. NO prevents insertions. | |
| For compressed InnoDB tables, you can specify the KEY_BLOCK_SIZE in kilobytes to use for pages. 0 causes the default compressed page size to be used. | |
| MAX_ROWS and MIN_ROWS are hints to the storage engine about the maximum and minimum numbers of rows in the table. | |
| Setting 1 to PACK_KEYS in MyISAM tables causes smaller indexes to be used. This makes updates slower and reads faster. The default is to pack strings, but not numbers. Significant benefit is obtained from the prefix compression only if many numbers are the same. | |
| PASSWORD is unused. You need to contact the sales department of Oracle, if you wish to scramble the .frm files. | |
| UNION is used when you want to access a collection of identical MyISAM tables as one. It works only with MERGE tables. | |
| 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 [=] 'comment_text' ] [DATA DIRECTORY [=] 'data_dir'] [INDEX DIRECTORY [=] 'index_dir'] [MAX_ROWS [=] max_number_of_rows] [MIN_ROWS [=] min_number_of_rows] [(subpartition_definition [, 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] |
| select_statement: | [IGNORE | REPLACE] [AS] SELECT ... (Some valid select statement) |
| Partitioning will be described in 5.16. | |