13.1.18 CREATE TABLE Statement
- 13.1.18.1 Files Created by CREATE TABLE
- 13.1.18.2 CREATE TEMPORARY TABLE Statement
- 13.1.18.3 CREATE TABLE ... LIKE Statement
- 13.1.18.4 CREATE TABLE ... SELECT Statement
- 13.1.18.5 FOREIGN KEY Constraints
- 13.1.18.6 Silent Column Specification Changes
- 13.1.18.7 CREATE TABLE and Generated Columns
- 13.1.18.8 Secondary Indexes and Generated Columns
- 13.1.18.9 Setting NDB_TABLE Options
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]
[IGNORE | REPLACE]
[AS] query_expression
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
{ LIKE old_tbl_name | (LIKE old_tbl_name) }
create_definition: {
col_name column_definition
| {INDEX | KEY} [index_name] [index_type] (key_part,...)
[index_option] ...
| {FULLTEXT | SPATIAL} [INDEX | KEY] [index_name] (key_part,...)
[index_option] ...
| [CONSTRAINT [symbol]] PRIMARY KEY
[index_type] (key_part,...)
[index_option] ...
| [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY]
[index_name] [index_type] (key_part,...)
[index_option] ...
| [CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (col_name,...)
reference_definition
| CHECK (expr)
}
column_definition: {
data_type [NOT NULL | NULL] [DEFAULT default_value]
[AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY]
[COMMENT 'string']
[COLLATE collation_name]
[COLUMN_FORMAT {FIXED | DYNAMIC | DEFAULT}]
[STORAGE {DISK | MEMORY}]
[reference_definition]
| data_type
[COLLATE collation_name]
[GENERATED ALWAYS] AS (expr)
[VIRTUAL | STORED] [NOT NULL | NULL]
[UNIQUE [KEY]] [[PRIMARY] KEY]
[COMMENT 'string']
[reference_definition]
}
data_type:
(see Chapter 11, Data Types)
key_part:
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 (key_part,...)
[MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
[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: {
AUTO_INCREMENT [=] value
| AVG_ROW_LENGTH [=] value
| [DEFAULT] CHARACTER SET [=] charset_name
| CHECKSUM [=] {0 | 1}
| [DEFAULT] COLLATE [=] collation_name
| COMMENT [=] 'string'
| COMPRESSION [=] {'ZLIB' | 'LZ4' | 'NONE'}
| CONNECTION [=] 'connect_string'
| {DATA | INDEX} DIRECTORY [=] 'absolute path to directory'
| DELAY_KEY_WRITE [=] {0 | 1}
| ENCRYPTION [=] {'Y' | 'N'}
| ENGINE [=] engine_name
| 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
| TABLESPACE tablespace_name [STORAGE {DISK | MEMORY}]
| UNION [=] (tbl_name[,tbl_name]...)
}
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 [=] 'string' ]
[DATA DIRECTORY [=] 'data_dir']
[INDEX DIRECTORY [=] 'index_dir']
[MAX_ROWS [=] max_number_of_rows]
[MIN_ROWS [=] min_number_of_rows]
[TABLESPACE [=] tablespace_name]
[(subpartition_definition [, subpartition_definition] ...)]
subpartition_definition:
SUBPARTITION logical_name
[[STORAGE] ENGINE [=] engine_name]
[COMMENT [=] 'string' ]
[DATA DIRECTORY [=] 'data_dir']
[INDEX DIRECTORY [=] 'index_dir']
[MAX_ROWS [=] max_number_of_rows]
[MIN_ROWS [=] min_number_of_rows]
[TABLESPACE [=] tablespace_name]
query_expression:
SELECT ... (Some valid select or union statement)
CREATE TABLE
creates a table with the given name. You must have the CREATE
privilege for the table.
By default, tables are created in the default database, using the InnoDB
storage engine. An error occurs if the table exists, if there is no default database, or if the database does not exist.
MySQL has no limit on the number of tables. The underlying file system may have a limit on the number of files that represent tables. Individual storage engines may impose engine-specific constraints. InnoDB
permits up to 4 billion tables.
For information about the physical representation of a table, see Section 13.1.18.1, “Files Created by CREATE TABLE”.
There are several aspects to the CREATE TABLE
statement, described under the following topics in this section:
Table Name
tbl_name
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`
.Rules for permissible table names are given in Section 9.2, “Schema Object Names”.
IF NOT EXISTS
Prevents 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.
Temporary Tables
You can use the TEMPORARY
keyword when creating a table. A TEMPORARY
table is visible only within the current session, and is dropped automatically when the session is closed. For more information, see Section 13.1.18.2, “CREATE TEMPORARY TABLE Statement”.
Table Cloning and Copying
LIKE
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 13.1.18.3, “CREATE TABLE ... LIKE Statement”.
[AS]
query_expression
To create one table from another, add a
SELECT
statement at the end of theCREATE TABLE
statement:CREATE TABLE new_tbl AS SELECT * FROM orig_tbl;
For more information, see Section 13.1.18.4, “CREATE TABLE ... SELECT Statement”.
IGNORE | REPLACE
The
IGNORE
andREPLACE
options indicate how to handle rows that duplicate unique key values when copying a table using aSELECT
statement.For more information, see Section 13.1.18.4, “CREATE TABLE ... SELECT Statement”.
Column Data Types and Attributes
There is a hard limit of 4096 columns per table, but the effective maximum may be less for a given table and depends on the factors discussed in Section 8.4.7, “Limits on Table Column Count and Row Size”.
data_type
data_type
represents the data type in a column definition. 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.Some attributes do not apply to all data types.
AUTO_INCREMENT
applies only to integer and floating-point types.DEFAULT
does not apply to theBLOB
,TEXT
,GEOMETRY
, andJSON
types.Character data types (
CHAR
,VARCHAR
, theTEXT
types,ENUM
,SET
, and any synonyms) can includeCHARACTER SET
to specify the character set for the column.CHARSET
is a synonym forCHARACTER SET
. A collation for the character set can be specified with theCOLLATE
attribute, along with any other attributes. For details, see Chapter 10, Character Sets, Collations, Unicode. Example:CREATE TABLE t (c CHAR(20) CHARACTER SET utf8 COLLATE utf8_bin);
MySQL 5.7 interprets length specifications in character column definitions in characters. Lengths for
BINARY
andVARBINARY
are in bytes.For
CHAR
,VARCHAR
,BINARY
, andVARBINARY
columns, indexes can be created that use only the leading part of column values, using
syntax to specify an index prefix length.col_name
(length
)BLOB
andTEXT
columns also can be indexed, but a prefix length must be given. Prefix lengths are given in characters for nonbinary string types and in bytes for binary string types. That is, index entries consist of the firstlength
characters of each column value forCHAR
,VARCHAR
, andTEXT
columns, and the firstlength
bytes of each column value forBINARY
,VARBINARY
, andBLOB
columns. Indexing only a prefix of column values like this can make the index file much smaller. For additional information about index prefixes, see Section 13.1.14, “CREATE INDEX Statement”.Only the
InnoDB
andMyISAM
storage engines support indexing onBLOB
andTEXT
columns. For example:CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));
As of MySQL 5.7.17, if a specified index prefix exceeds the maximum column data type size,
CREATE TABLE
handles the index as follows:For a nonunique index, either an error occurs (if strict SQL mode is enabled), or the index length is reduced to lie within the maximum column data type size and a warning is produced (if strict SQL mode is not enabled).
For a unique index, an error occurs regardless of SQL mode because reducing the index length might enable insertion of nonunique entries that do not meet the specified uniqueness requirement.
JSON
columns cannot be indexed. You can work around this restriction by creating an index on a generated column that extracts a scalar value from theJSON
column. See Indexing a Generated Column to Provide a JSON Column Index, for a detailed example.
NOT NULL | NULL
If neither
NULL
norNOT NULL
is specified, the column is treated as thoughNULL
had been specified.In MySQL 5.7, only the
InnoDB
,MyISAM
, andMEMORY
storage engines support indexes on columns that can haveNULL
values. In other cases, you must declare indexed columns asNOT NULL
or an error results.DEFAULT
Specifies a default value for a column. For more information about default value handling, including the case that a column definition includes no explicit
DEFAULT
value, see Section 11.6, “Data Type Default Values”.If the
NO_ZERO_DATE
orNO_ZERO_IN_DATE
SQL mode is enabled and a date-valued default is not correct according to that mode,CREATE TABLE
produces a warning if strict SQL mode is not enabled and an error if strict mode is enabled. For example, withNO_ZERO_IN_DATE
enabled,c1 DATE DEFAULT '2010-00-00'
produces a warning.AUTO_INCREMENT
An integer or floating-point column can have the additional attribute
AUTO_INCREMENT
. When you insert a value ofNULL
(recommended) or0
into an indexedAUTO_INCREMENT
column, the column is set to the next sequence value. Typically this is
, wherevalue
+1value
is the largest value for the column currently in the table.AUTO_INCREMENT
sequences begin with1
.To retrieve an
AUTO_INCREMENT
value after inserting a row, use theLAST_INSERT_ID()
SQL function or themysql_insert_id()
C API function. See Section 12.15, “Information Functions”, and Section 27.7.6.38, “mysql_insert_id()”.If the
NO_AUTO_VALUE_ON_ZERO
SQL mode is enabled, you can store0
inAUTO_INCREMENT
columns as0
without generating a new sequence value. See Section 5.1.10, “Server SQL Modes”.There can be only one
AUTO_INCREMENT
column per table, it must be indexed, and it cannot have aDEFAULT
value. AnAUTO_INCREMENT
column works 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 you do not accidentally get anAUTO_INCREMENT
column that contains0
.For
MyISAM
tables, you can specify anAUTO_INCREMENT
secondary column in a multiple-column key. See Section 3.6.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
This method requires that
sql_auto_is_null
variable is not set to 0. See Section 5.1.7, “Server System Variables”.For information about
InnoDB
andAUTO_INCREMENT
, see Section 14.6.1.6, “AUTO_INCREMENT Handling in InnoDB”. For information aboutAUTO_INCREMENT
and MySQL Replication, see Section 16.4.1.1, “Replication and AUTO_INCREMENT”.COMMENT
A comment for a column can be specified with the
COMMENT
option, up to 1024 characters long. The comment is displayed by theSHOW CREATE TABLE
andSHOW FULL COLUMNS
statements.COLUMN_FORMAT
In NDB Cluster, it is also possible to specify a data storage format for individual columns of
NDB
tables usingCOLUMN_FORMAT
. Permissible column formats areFIXED
,DYNAMIC
, andDEFAULT
.FIXED
is used to specify fixed-width storage,DYNAMIC
permits the column to be variable-width, andDEFAULT
causes the column to use fixed-width or variable-width storage as determined by the column's data type (possibly overridden by aROW_FORMAT
specifier).Beginning with MySQL NDB Cluster 7.5.4, for
NDB
tables, the default value forCOLUMN_FORMAT
isFIXED
. (The default had been switched toDYNAMIC
in MySQL NDB Cluster 7.5.1, but this change was reverted to maintain backwards compatibility with existing GA release series.) (Bug #24487363)In NDB Cluster, the maximum possible offset for a column defined with
COLUMN_FORMAT=FIXED
is 8188 bytes. For more information and possible workarounds, see Section 21.1.7.5, “Limits Associated with Database Objects in NDB Cluster”.COLUMN_FORMAT
currently has no effect on columns of tables using storage engines other thanNDB
. In MySQL 5.7 and later,COLUMN_FORMAT
is silently ignored.STORAGE
For
NDB
tables, it is possible to specify whether the column is stored on disk or in memory by using aSTORAGE
clause.STORAGE DISK
causes the column to be stored on disk, andSTORAGE MEMORY
causes in-memory storage to be used. TheCREATE TABLE
statement used must still include aTABLESPACE
clause:mysql> CREATE TABLE t1 ( -> c1 INT STORAGE DISK, -> c2 INT STORAGE MEMORY -> ) ENGINE NDB; ERROR 1005 (HY000): Can't create table 'c.t1' (errno: 140) mysql> CREATE TABLE t1 ( -> c1 INT STORAGE DISK, -> c2 INT STORAGE MEMORY -> ) TABLESPACE ts_1 ENGINE NDB; Query OK, 0 rows affected (1.06 sec)
For
NDB
tables,STORAGE DEFAULT
is equivalent toSTORAGE MEMORY
.The
STORAGE
clause has no effect on tables using storage engines other thanNDB
. TheSTORAGE
keyword is supported only in the build of mysqld that is supplied with NDB Cluster; it is not recognized in any other version of MySQL, where any attempt to use theSTORAGE
keyword causes a syntax error.GENERATED ALWAYS
Used to specify a generated column expression. For information about generated columns, see Section 13.1.18.7, “CREATE TABLE and Generated Columns”.
Stored generated columns can be indexed.
InnoDB
supports secondary indexes on virtual generated columns. See Section 13.1.18.8, “Secondary Indexes and Generated Columns”.
Indexes and Foreign Keys
Several keywords apply to creation of indexes and foreign keys. For general background in addition to the following descriptions, see Section 13.1.14, “CREATE INDEX Statement”, and Section 13.1.18.5, “FOREIGN KEY Constraints”.
CONSTRAINT
symbol
The
CONSTRAINT
clause may be given to name a constraint. If the clause is not given, or asymbol
symbol
is not included following theCONSTRAINT
keyword, MySQL automatically generates a constraint name, with the exception noted below. Thesymbol
value, if used, must be unique per schema (database), per constraint type. A duplicatesymbol
results in an error. See also the discussion about length limits of generated constraint identifiers at Section 9.2.1, “Identifier Length Limits”.NoteIf the
CONSTRAINT
clause is not given in a foreign key definition, or asymbol
symbol
is not included following theCONSTRAINT
keyword,NDB
uses the foreign key index name.The SQL standard specifies that all types of constraints (primary key, unique index, foreign key, check) belong to the same namespace. In MySQL, each constraint type has its own namespace per schema. Consequently, names for each type of constraint must be unique per schema.
PRIMARY KEY
A unique index where all key columns must be defined as
NOT NULL
. If they are not explicitly declared asNOT NULL
, MySQL declares them so implicitly (and silently). A table can have only onePRIMARY KEY
. The name of aPRIMARY KEY
is alwaysPRIMARY
, which thus cannot be used as the name for any other kind of index.If you do not have a
PRIMARY KEY
and an application asks for thePRIMARY KEY
in your tables, MySQL returns the firstUNIQUE
index that has noNULL
columns as thePRIMARY KEY
.In
InnoDB
tables, keep thePRIMARY KEY
short to minimize storage overhead for secondary indexes. Each secondary index entry contains a copy of the primary key columns for the corresponding row. (See Section 14.6.2.1, “Clustered and Secondary Indexes”.)In the created table, a
PRIMARY KEY
is placed first, followed by allUNIQUE
indexes, and then the nonunique indexes. This helps the MySQL optimizer to prioritize which index to use and also more quickly to detect duplicatedUNIQUE
keys.A
PRIMARY KEY
can be a multiple-column index. However, you cannot create a multiple-column index using thePRIMARY KEY
key attribute in a column specification. Doing so only marks that single column as primary. You must use a separatePRIMARY KEY(
clause.key_part
, ...)If a table has a
PRIMARY KEY
orUNIQUE NOT NULL
index that consists of a single column that has an integer type, you can use_rowid
to refer to the indexed column inSELECT
statements, as described in Unique Indexes.In MySQL, the name of a
PRIMARY KEY
isPRIMARY
. For other indexes, if you do not assign a name, the index is assigned the same name as the first indexed column, with an optional suffix (_2
,_3
,...
) to make it unique. You can see index names for a table usingSHOW INDEX FROM
. See Section 13.7.5.22, “SHOW INDEX Statement”.tbl_name
KEY | INDEX
KEY
is normally a synonym forINDEX
. The key attributePRIMARY KEY
can also be specified as justKEY
when given in a column definition. This was implemented for compatibility with other database systems.UNIQUE
A
UNIQUE
index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. For all engines, aUNIQUE
index permits multipleNULL
values for columns that can containNULL
. If you specify a prefix value for a column in aUNIQUE
index, the column values must be unique within the prefix length.If a table has a
PRIMARY KEY
orUNIQUE NOT NULL
index that consists of a single column that has an integer type, you can use_rowid
to refer to the indexed column inSELECT
statements, as described in Unique Indexes.FULLTEXT
A
FULLTEXT
index is a special type of index used for full-text searches. Only theInnoDB
andMyISAM
storage engines supportFULLTEXT
indexes. They can be created only fromCHAR
,VARCHAR
, andTEXT
columns. Indexing always happens over the entire column; column prefix indexing is not supported and any prefix length is ignored if specified. See Section 12.9, “Full-Text Search Functions”, for details of operation. AWITH PARSER
clause can be specified as anindex_option
value to associate a parser plugin with the index if full-text indexing and searching operations need special handling. This clause is valid only forFULLTEXT
indexes. BothInnoDB
andMyISAM
support full-text parser plugins. See Full-Text Parser Plugins and Section 28.2.4.4, “Writing Full-Text Parser Plugins” for more information.SPATIAL
You can create
SPATIAL
indexes on spatial data types. Spatial types are supported only forMyISAM
andInnoDB
tables, and indexed columns must be declared asNOT NULL
. See Section 11.4, “Spatial Data Types”.FOREIGN KEY
MySQL supports foreign keys, which let you cross-reference related data across tables, and foreign key constraints, which help keep this spread-out data consistent. For definition and option information, see
reference_definition
, andreference_option
.Partitioned tables employing the
InnoDB
storage engine do not support foreign keys. See Section 22.6, “Restrictions and Limitations on Partitioning”, for more information.CHECK
The
CHECK
clause is parsed but ignored by all storage engines.key_part
A
key_part
specification can end withASC
orDESC
. These keywords are permitted for future extensions for specifying ascending or descending index value storage. Currently, they are parsed but ignored; index values are always stored in ascending order.Prefixes, defined by the
length
attribute, can be up to 767 bytes long forInnoDB
tables or 3072 bytes if theinnodb_large_prefix
option is enabled. ForMyISAM
tables, the prefix length limit is 1000 bytes.Prefix limits are measured in bytes. However, prefix lengths for index specifications in
CREATE TABLE
,ALTER TABLE
, andCREATE INDEX
statements are interpreted as number of characters for nonbinary string types (CHAR
,VARCHAR
,TEXT
) and number of bytes for binary string types (BINARY
,VARBINARY
,BLOB
). Take this into account when specifying a prefix length for a nonbinary string column that uses a multibyte character set.
index_type
Some storage engines permit you to specify an index type when creating an index. The syntax for the
index_type
specifier isUSING
.type_name
Example:
CREATE TABLE lookup (id INT, INDEX USING BTREE (id)) ENGINE = MEMORY;
The preferred position for
USING
is after the index column list. It can be given before the column list, but support for use of the option in that position is deprecated and will be removed in a future MySQL release.index_option
index_option
values specify additional options for an index.KEY_BLOCK_SIZE
For
MyISAM
tables,KEY_BLOCK_SIZE
optionally specifies the size in bytes to use for index key blocks. The value is treated as a hint; a different size could be used if necessary. AKEY_BLOCK_SIZE
value specified for an individual index definition overrides the table-levelKEY_BLOCK_SIZE
value.For information about the table-level
KEY_BLOCK_SIZE
attribute, see Table Options.WITH PARSER
The
WITH PARSER
option can only be used withFULLTEXT
indexes. It associates a parser plugin with the index if full-text indexing and searching operations need special handling. BothInnoDB
andMyISAM
support full-text parser plugins. If you have aMyISAM
table with an associated full-text parser plugin, you can convert the table toInnoDB
usingALTER TABLE
.COMMENT
In MySQL 5.7, index definitions can include an optional comment of up to 1024 characters.
You can set the
InnoDB
MERGE_THRESHOLD
value for an individual index using theindex_option
COMMENT
clause. See Section 14.8.12, “Configuring the Merge Threshold for Index Pages”.
For more information about permissible
index_option
values, see Section 13.1.14, “CREATE INDEX Statement”. For more information about indexes, see Section 8.3.1, “How MySQL Uses Indexes”.For
reference_definition
syntax details and examples, see Section 13.1.18.5, “FOREIGN KEY Constraints”.InnoDB
andNDB
tables support checking of foreign key constraints. The columns of the referenced table must always be explicitly named. BothON DELETE
andON UPDATE
actions on foreign keys are supported. For more detailed information and examples, see Section 13.1.18.5, “FOREIGN KEY Constraints”.For other storage engines, MySQL Server parses and ignores the
FOREIGN KEY
andREFERENCES
syntax inCREATE TABLE
statements. See Section 1.8.2.3, “FOREIGN KEY Constraint Differences”.ImportantFor users familiar with the ANSI/ISO SQL Standard, please note that no storage engine, including
InnoDB
, recognizes or enforces theMATCH
clause used in referential integrity constraint definitions. Use of an explicitMATCH
clause will not have the specified effect, and also causesON DELETE
andON UPDATE
clauses to be ignored. For these reasons, specifyingMATCH
should be avoided.The
MATCH
clause in the SQL standard controls howNULL
values in a composite (multiple-column) foreign key are handled when comparing to a primary key.InnoDB
essentially implements the semantics defined byMATCH SIMPLE
, which permit a foreign key to be all or partiallyNULL
. In that case, the (child table) row containing such a foreign key is permitted to be inserted, and does not match any row in the referenced (parent) table. It is possible to implement other semantics using triggers.Additionally, MySQL requires that the referenced columns be indexed for performance. However,
InnoDB
does not enforce any requirement that the referenced columns be declaredUNIQUE
orNOT NULL
. The handling of foreign key references to nonunique keys or keys that containNULL
values is not well defined for operations such asUPDATE
orDELETE CASCADE
. You are advised to use foreign keys that reference only keys that are bothUNIQUE
(orPRIMARY
) andNOT NULL
.MySQL parses but ignores “inline
REFERENCES
specifications” (as defined in the SQL standard) where the references are defined as part of the column specification. MySQL acceptsREFERENCES
clauses only when specified as part of a separateFOREIGN KEY
specification.For information about the
RESTRICT
,CASCADE
,SET NULL
,NO ACTION
, andSET DEFAULT
options, see Section 13.1.18.5, “FOREIGN KEY Constraints”.
Table Options
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.
ENGINE
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 recognized but ignored.Storage Engine Description InnoDB
Transaction-safe tables with row locking and foreign keys. The default storage engine for new tables. See Chapter 14, The InnoDB Storage Engine, and in particular Section 14.1, “Introduction to InnoDB” if you have MySQL experience but are new to InnoDB
.MyISAM
The binary portable storage engine that is primarily used for read-only or read-mostly workloads. See Section 15.2, “The MyISAM Storage Engine”. MEMORY
The data for this storage engine is stored only in memory. See Section 15.3, “The MEMORY Storage Engine”. CSV
Tables that store rows in comma-separated values format. See Section 15.4, “The CSV Storage Engine”. ARCHIVE
The archiving storage engine. See Section 15.5, “The ARCHIVE Storage Engine”. EXAMPLE
An example engine. See Section 15.9, “The EXAMPLE Storage Engine”. FEDERATED
Storage engine that accesses remote tables. See Section 15.8, “The FEDERATED Storage Engine”. HEAP
This is a synonym for MEMORY
.MERGE
A collection of MyISAM
tables used as one table. Also known asMRG_MyISAM
. See Section 15.7, “The MERGE Storage Engine”.NDB
Clustered, fault-tolerant, memory-based tables, supporting transactions and foreign keys. Also known as NDBCLUSTER
. See Chapter 21, MySQL NDB Cluster 7.5 and NDB Cluster 7.6.By default, if a storage engine is specified that is not available, the statement fails with an error. You can override this behavior by removing
NO_ENGINE_SUBSTITUTION
from the server SQL mode (see Section 5.1.10, “Server SQL Modes”) so that MySQL allows substitution of the specified engine with the default storage engine instead. Normally in such cases, this isInnoDB
, which is the default value for thedefault_storage_engine
system variable. WhenNO_ENGINE_SUBSTITUTION
is disabled, a warning occurs if the storage engine specification is not honored.AUTO_INCREMENT
The initial
AUTO_INCREMENT
value for the table. In MySQL 5.7, this works forMyISAM
,MEMORY
,InnoDB
, andARCHIVE
tables. To set the first auto-increment value for engines that do not support theAUTO_INCREMENT
table option, insert a “dummy” row with a value one less than the desired value after creating the table, and then delete the dummy row.For engines that support the
AUTO_INCREMENT
table option inCREATE TABLE
statements, you can also useALTER TABLE
to reset thetbl_name
AUTO_INCREMENT =N
AUTO_INCREMENT
value. The value cannot be set lower than the maximum value currently in the column.AVG_ROW_LENGTH
An approximation of the average row length for your table. You need to set this only for large tables with variable-size rows.
When you create a
MyISAM
table, MySQL uses the product of theMAX_ROWS
andAVG_ROW_LENGTH
options to decide how big the resulting table is. If you don't specify either option, the maximum size forMyISAM
data and index files is 256TB by default. (If your operating system does not support files that large, table sizes are constrained by the file size limit.) If you want to keep down the pointer sizes to make the index smaller and faster and you don't really need big files, you can decrease the default pointer size by setting themyisam_data_pointer_size
system variable. (See Section 5.1.7, “Server System Variables”.) If you want all your tables to be able to grow above the default limit and are willing to have your tables slightly slower and larger than necessary, you can increase the default pointer size by setting this variable. Setting the value to 7 permits table sizes up to 65,536TB.[DEFAULT] CHARACTER SET
Specifies a default character set for the table.
CHARSET
is a synonym forCHARACTER SET
. If the character set name isDEFAULT
, the database character set is used.CHECKSUM
Set this to 1 if you want MySQL to maintain a live checksum for all rows (that is, a checksum that MySQL updates automatically as the table changes). This makes the table a little slower to update, but also makes it easier to find corrupted tables. The
CHECKSUM TABLE
statement reports the checksum. (MyISAM
only.)[DEFAULT] COLLATE
Specifies a default collation for the table.
COMMENT
A comment for the table, up to 2048 characters long.
You can set the
InnoDB
MERGE_THRESHOLD
value for a table using thetable_option
COMMENT
clause. See Section 14.8.12, “Configuring the Merge Threshold for Index Pages”.Setting NDB_TABLE options. In MySQL NDB Cluster 7.5.2 and later, the table comment in a
CREATE TABLE
orALTER TABLE
statement can also be used to specify one to four of theNDB_TABLE
optionsNOLOGGING
,READ_BACKUP
,PARTITION_BALANCE
, orFULLY_REPLICATED
as a set of name-value pairs, separated by commas if need be, immediately following the stringNDB_TABLE=
that begins the quoted comment text. An example statement using this syntax is shown here (emphasized text):CREATE TABLE t1 ( c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, c2 VARCHAR(100), c3 VARCHAR(100) ) ENGINE=NDB COMMENT="NDB_TABLE=READ_BACKUP=0,PARTITION_BALANCE=FOR_RP_BY_NODE";
Spaces are not permitted within the quoted string. The string is case-insensitive.
The comment is displayed as part of the ouput of
SHOW CREATE TABLE
. The text of the comment is also available as the TABLE_COMMENT column of the MySQL Information SchemaTABLES
table.This comment syntax is also supported with
ALTER TABLE
statements forNDB
tables. Keep in mind that a table comment used withALTER TABLE
replaces any existing comment which the table might have had perviously.Setting the
MERGE_THRESHOLD
option in table comments is not supported forNDB
tables (it is ignored).For complete syntax information and examples, see Section 13.1.18.9, “Setting NDB_TABLE Options”.
COMPRESSION
The compression algorithm used for page level compression for
InnoDB
tables. Supported values includeZlib
,LZ4
, andNone
. TheCOMPRESSION
attribute was introduced with the transparent page compression feature. Page compression is only supported withInnoDB
tables that reside in file-per-table tablespaces, and is only available on Linux and Windows platforms that support sparse files and hole punching. For more information, see Section 14.9.2, “InnoDB Page Compression”.CONNECTION
The connection string for a
FEDERATED
table.NoteOlder versions of MySQL used a
COMMENT
option for the connection string.DATA DIRECTORY
,INDEX DIRECTORY
For
InnoDB
, theDATA DIRECTORY='
clause permits creating a table outside of the data directory. Thedirectory
'innodb_file_per_table
variable must be enabled to use theDATA DIRECTORY
clause. The full directory path must be specified. For more information, see Section 14.6.1.2, “Creating Tables Externally”.When creating
MyISAM
tables, you can use theDATA DIRECTORY='
clause, thedirectory
'INDEX DIRECTORY='
clause, or both. They specify where to put adirectory
'MyISAM
table's data file and index file, respectively. UnlikeInnoDB
tables, MySQL does not create subdirectories that correspond to the database name when creating aMyISAM
table with aDATA DIRECTORY
orINDEX DIRECTORY
option. Files are created in the directory that is specified.As of MySQL 5.7.17, you must have the
FILE
privilege to use theDATA DIRECTORY
orINDEX DIRECTORY
table option.ImportantTable-level
DATA DIRECTORY
andINDEX DIRECTORY
options are ignored for partitioned tables. (Bug #32091)These options work only when you are not using the
--skip-symbolic-links
option. Your operating system must also have a working, thread-saferealpath()
call. See Section 8.12.3.2, “Using Symbolic Links for MyISAM Tables on Unix”, for more complete information.If a
MyISAM
table is created with noDATA DIRECTORY
option, the.MYD
file is created in the database directory. By default, ifMyISAM
finds an existing.MYD
file in this case, it overwrites it. The same applies to.MYI
files for tables created with noINDEX DIRECTORY
option. To suppress this behavior, start the server with the--keep_files_on_create
option, in which caseMyISAM
will not overwrite existing files and returns an error instead.If a
MyISAM
table is created with aDATA DIRECTORY
orINDEX DIRECTORY
option and an existing.MYD
or.MYI
file is found, MyISAM always returns an error. It will not overwrite a file in the specified directory.ImportantYou cannot use path names that contain the MySQL data directory with
DATA DIRECTORY
orINDEX DIRECTORY
. This includes partitioned tables and individual table partitions. (See Bug #32167.)DELAY_KEY_WRITE
Set this to 1 if you want to delay key updates for the table until the table is closed. See the description of the
delay_key_write
system variable in Section 5.1.7, “Server System Variables”. (MyISAM
only.)ENCRYPTION
Set the
ENCRYPTION
option to'Y'
to enable page-level data encryption for anInnoDB
table created in a file-per-table tablespace. Option values are not case-sensitive. TheENCRYPTION
option was introduced with theInnoDB
tablespace encryption feature; see Section 14.14, “InnoDB Data-at-Rest Encryption”. Akeyring
plugin must be installed and configured before encryption can be enabled.INSERT_METHOD
If you want to insert data into a
MERGE
table, you must specify withINSERT_METHOD
the table into which the row should be inserted.INSERT_METHOD
is an option useful forMERGE
tables only. Use a value ofFIRST
orLAST
to have inserts go to the first or last table, or a value ofNO
to prevent inserts. See Section 15.7, “The MERGE Storage Engine”.KEY_BLOCK_SIZE
For
MyISAM
tables,KEY_BLOCK_SIZE
optionally specifies the size in bytes to use for index key blocks. The value is treated as a hint; a different size could be used if necessary. AKEY_BLOCK_SIZE
value specified for an individual index definition overrides the table-levelKEY_BLOCK_SIZE
value.For
InnoDB
tables,KEY_BLOCK_SIZE
specifies the page size in kilobytes to use for compressedInnoDB
tables. TheKEY_BLOCK_SIZE
value is treated as a hint; a different size could be used byInnoDB
if necessary.KEY_BLOCK_SIZE
can only be less than or equal to theinnodb_page_size
value. A value of 0 represents the default compressed page size, which is half of theinnodb_page_size
value. Depending oninnodb_page_size
, possibleKEY_BLOCK_SIZE
values include 0, 1, 2, 4, 8, and 16. See Section 14.9.1, “InnoDB Table Compression” for more information.Oracle recommends enabling
innodb_strict_mode
when specifyingKEY_BLOCK_SIZE
forInnoDB
tables. Wheninnodb_strict_mode
is enabled, specifying an invalidKEY_BLOCK_SIZE
value returns an error. Ifinnodb_strict_mode
is disabled, an invalidKEY_BLOCK_SIZE
value results in a warning, and theKEY_BLOCK_SIZE
option is ignored.The
Create_options
column in response toSHOW TABLE STATUS
reports the originally specifiedKEY_BLOCK_SIZE
option, as doesSHOW CREATE TABLE
.InnoDB
only supports