mysql create table statement

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_constraint_definition

}

column_definition: {

data_type [NOT NULL | NULL] [DEFAULT {literal | (expr)} ]

[VISIBLE | INVISIBLE]

[AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY]

[COMMENT 'string']

[COLLATE collation_name]

[COLUMN_FORMAT {FIXED | DYNAMIC | DEFAULT}]

[ENGINE_ATTRIBUTE [=] 'string']

[SECONDARY_ENGINE_ATTRIBUTE [=] 'string']

[STORAGE {DISK | MEMORY}]

[reference_definition]

[check_constraint_definition]

| data_type

[COLLATE collation_name]

[GENERATED ALWAYS] AS (expr)

[VIRTUAL | STORED] [NOT NULL | NULL]

[VISIBLE | INVISIBLE]

[UNIQUE [KEY]] [[PRIMARY] KEY]

[COMMENT 'string']

[reference_definition]

[check_constraint_definition]

}

data_type:

(see Chapter 11, Data Types)

key_part: {col_name [(length)] | (expr)} [ASC | DESC]

index_type:

USING {BTREE | HASH}

index_option: {

KEY_BLOCK_SIZE [=] value

| index_type

| WITH PARSER parser_name

| COMMENT 'string'

| {VISIBLE | INVISIBLE}

|ENGINE_ATTRIBUTE [=] 'string'

|SECONDARY_ENGINE_ATTRIBUTE [=] 'string'

}

check_constraint_definition:

[CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]

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: {

AUTOEXTEND_SIZE [=] value

| 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

| ENGINE_ATTRIBUTE [=] 'string'

| 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}

| SECONDARY_ENGINE_ATTRIBUTE [=] 'string'

| 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.20.1, “Files Created by

CREATE TABLE”.

There are several aspects to the CREATE TABLE statement, described under the following topics in this

section:

ing </

•

Table Name

•

Temporary Tables

•

Table Cloning and Copying

•

Column Data Types and Attributes

•

Indexes, Foreign Keys, and CHECK Constraints

•

Table Options

•

Table Partition

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.

v>

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.20.2, “CREATE TEMPORARY TABLE Statement”.

h. </

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.20.3, “CREATE TABLE ... LIKE Statement”.

•

[AS] query_expression

To create one table from another, add a SELECT statement at the end of the CREATE TABLE statement:

CREATE TABLE new_tbl AS SELECT * FROM orig_tbl;

For more information, see Section 13.1.20.4, “CREATE TABLE ... SELECT Statement”.

•

IGNORE | REPLACE

The IGNORE and REPLACE options indicate how to handle rows that duplicate unique key values when

copying a table using a SELECT statement.

For more information, see Section 13.1.20.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. Prior to MySQL 8.0.13, DEFAULT does not apply to the BLOB, TEXT, GEOMETRY, and

JSON types.

•

Character data types (CHAR, VARCHAR, the TEXT types, ENUM, SET, and any synonyms) can include

CHARACTER SET to specify the character set for the column. CHARSET is a synonym for CHARACTER

SET. A collation for the character set can be specified with the COLLATE 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 8.0 interprets length specifications in character column definitions in characters. Lengths for

BINARY and VARBINARY are in bytes.

•

For CHAR, VARCHAR, BINARY, and VARBINARY columns, indexes can be created that use only the

leading part of column values, using col_name(length) syntax to specify an index prefix lengt

.

BLOB and TEXT 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 first length characters of each column value for CHAR, VARCHAR, and TEXT columns,

and the first length bytes of each column value for BINARY, VARBINARY, and BLOB 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.15, “CREATE INDEX Statement”.

Only the InnoDB and MyISAM storage engines support indexing on BLOB and TEXT columns. For

example:

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

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 the JSON column. See Indexing a Generated

Column to Provide a JSON Column Index, for a detailed example.

•

NOT NULL | NULL

If neither NULL nor NOT NULL is specified, the column is treated as though NULL had been specified.

In MySQL 8.0, only the InnoDB, MyISAM, and MEMORY storage engines support indexes on columns

that can have NULL values. In other cases, you must declare indexed columns as NOT 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 or NO_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, with NO_ZERO_IN_DATE enabled, c1 DATE

DEFAULT '2010-00-00' produces a warning.

•

VISIBLE, INVISIBLE

Specify column visibility. The default is VISIBLE if neither keyword is present. A table must have at least

one visible column. Attempting to make all columns invisible produces an error. For more information,

see Section 13.1.20.10, “Invisible Columns”.

The VISIBLE and INVISIBLE keywords are available as of MySQL 8.0.23. Prior to MySQL 8.0.23, all

columns are visible

n> <

•

AUTO_INCREMENT

An integer or floating-point column can 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.

To retrieve an AUTO_INCREMENT value after inserting a row, use the LAST_INSERT_ID() SQL

function or the mysql_insert_id() C API function. See Section 12.16, “Information Functions”, and

mysql_insert_id().

If the NO_AUTO_VALUE_ON_ZERO SQL mode is enabled, you can store 0 in AUTO_INCREMENT columns

as 0 without generating a new sequence value. See Section 5.1.11, “Server SQL Modes”.

There can be only one AUTO_INCREMENT column per table, it must be indexed, and it cannot have a

DEFAULT value. An AUTO_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 an AUTO_INCREMENT column that contains 0.

For MyISAM tables, you can specify an AUTO_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.8, “Server

System Variables”.

For information about InnoDB and AUTO_INCREMENT, see Section 15.6.1.6, “AUTO_INCREMENT

Handling in InnoDB”. For information about AUTO_INCREMENT and MySQL Replication, see

Section 17.5.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 the SHOW CREATE TABLE and SHOW FULL COLUMNS statements.

•

COLUMN_FORMAT

In NDB Cluster, it is also possible to specify a data storage format for individual columns of NDB tables

using COLUMN_FORMAT. Permissible column formats are FIXED, DYNAMIC, and DEFAULT. FIXED is

used to specify fixed-width storage, DYNAMIC permits the column to be variable-width, and DEFAULT

causes the column to use fixed-width or variable-width storage as determined by the column's data type

(possibly overridden by a ROW_FORMAT specifier).

For NDB tables, the default value for COLUMN_FORMAT is FIXED.

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 23.2.7.5, “Limits Associated

with Database Objects in NDB Cluster”.

COLUMN_FORMAT currently has no effect on columns of tables using storage engines other than NDB.

MySQL 8.0 silently ignores COLUMN_FORMAT.

•

ENGINE_ATTRIBUTE and SECONDARY_ENGINE_ATTRIBUTE options (available as of MySQL 8.0.21)

are used to specify column attributes for primary and secondary storage engines. The options are

reserved for future use.

Permitted values are a string literal containing a valid JSON document or an empty string (''). Invalid

JSON is rejected.

CREATE TABLE t1 (c1 INT ENGINE_ATTRIBUTE='{"key":"value"}');

ENGINE_ATTRIBUTE and SECONDARY_ENGINE_ATTRIBUTE values can be repeated without error. In

this case, the last specified value is used.

ENGINE_ATTRIBUTE and SECONDARY_ENGINE_ATTRIBUTE values are not checked by the server, nor

are they cleared when the table's storage engine is changed.

•

STORAGE

For NDB tables, it is possible to specify whether the column is stored on disk or in memory by using

a STORAGE clause. STORAGE DISK causes the column to be stored on disk, and STORAGE MEMORY

causes in-memory storage to be used. The CREATE TABLE statement used must still include a

TABLESPACE clause:

v>

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)

div>

For NDB tables, STORAGE DEFAULT is equivalent to STORAGE MEMORY.

The STORAGE clause has no effect on tables using storage engines other than NDB. The STORAGE

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 the STORAGE keyword causes a syntax error.

•

GENERATED ALWAYS

Used to specify a generated column expression. For information about generated columns, see

Section 13.1.20.8, “CREATE TABLE and Generated Columns”.

Stored generated columns can be indexed. InnoDB supports secondary indexes on virtual generated

columns. See Section 13.1.20.9, “Secondary Indexes and Generated Columns”. </

div>

Indexes, Foreign Keys, and CHECK Constraints

Several keywords apply to creation of indexes, foreign keys, and CHECK constraints. For general

background in addition to the following descriptions, see Section 13.1.15, “CREATE INDEX Statement”,

Section 13.1.20.5, “FOREIGN KEY Constraints”, and Section 13.1.20.6, “CHECK Constraints”.

• CONSTRAINT symbol

The CONSTRAINT symbol clause may be given to name a constraint. If the clause is not given, or

a symbol is not included following the CONSTRAINT keyword, MySQL automatically generates a

constraint name, with the exception noted below. The symbol value, if used, must be unique per

schema (database), per constraint type. A duplicate symbol results in an error. See also the discussion

about length limits of generated constraint identifiers at Section 9.2.1, “Identifier Length Limits”.

v>

Note

If the CONSTRAINT symbol clause is not given in a foreign key definition, or

a symbol is not included following the CONSTRAINT keyword, MySQL uses

the foreign key index name up to MySQL 8.0.15, and automatically generates a

constraint name thereafter.

n> <

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, but constraints of different

types can have the same name.

•

PRIMARY KEY

A unique index where all key columns must be defined as NOT NULL. If they are not explicitly declared

as NOT NULL, MySQL declares them so implicitly (and silently). A table can have only one PRIMARY

KEY. The name of a PRIMARY KEY is always PRIMARY, 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 the PRIMARY KEY in your tables,

MySQL returns the first UNIQUE index that has no NULL columns as the PRIMARY KEY.

In InnoDB tables, keep the PRIMARY 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 15.6.2.1, “Clustered and Secondary Indexes”.)

In the created table, a PRIMARY KEY is placed first, followed by all UNIQUE indexes, and then the

nonunique indexes. This helps the MySQL optimizer to prioritize which index to use and also more

quickly to detect duplicated UNIQUE keys.

A PRIMARY KEY can be a multiple-column index. However, you cannot create a multiple-column index

using the PRIMARY KEY key attribute in a column specification. Doing so only marks that single column

as primary. You must use a separate PRIMARY KEY(key_part, ...) clause.

If a table has a PRIMARY KEY or UNIQUE NOT NULL index that consists of a single column that has an

integer type, you can use _rowid to refer to the indexed column in SELECT statements, as described in

Unique Indexes.

In MySQL, the name of a PRIMARY KEY is PRIMARY. 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 using SHOW INDEX FROM tbl_name. See

Section 13.7.7.22, “SHOW INDEX Statement”.

•

KEY | INDEX

KEY is normally a synonym for INDEX. The key attribute PRIMARY KEY can also be specified as just

KEY 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, a UNIQUE

xes.

index permits multiple NULL values for columns that can contain NULL. If you specify a prefix value for a

column in a UNIQUE index, the column values must be unique within the prefix length.

If a table has a PRIMARY KEY or UNIQUE NOT NULL index that consists of a single column that has an

integer type, you can use _rowid to refer to the indexed column in SELECT statements, as described in

Unique Indexes.

•

FULLTEXT

A FULLTEXT index is a special type of index used for full-text searches. Only the InnoDB and MyISAM

storage engines support FULLTEXT indexes. They can be created only from CHAR, VARCHAR, and TEXT

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.10, “Full-Text Search Functions”, for details of

operation. A WITH PARSER clause can be specified as an index_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 for FULLTEXT indexes. InnoDB and MyISAM support full-text parser plugins. See Full-Text

Parser Plugins and Writing Full-Text Parser Plugins for more information.

•

SPATIAL

You can create SPATIAL indexes on spatial data types. Spatial types are supported only for InnoDB

and MyISAM tables, and indexed columns must be declared as NOT 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, and reference_option.

Partitioned tables employing the InnoDB storage engine do not support foreign keys. See Section 24.6,

“Restrictions and Limitations on Partitioning”, for more information.

•

CHECK

The CHECK clause enables the creation of constraints to be checked for data values in table rows. See

Section 13.1.20.6, “CHECK Constraints”.

•

key_part

•

A key_part specification can end with ASC or DESC to specify whether index values are stored in

ascending or descending order. The default is ascending if no order specifier is given.

•

Prefixes, defined by the length attribute, can be up to 767 bytes long for InnoDB tables that use the

REDUNDANT or COMPACT row format. The prefix length limit is 3072 bytes for InnoDB tables that use

the DYNAMIC or COMPRESSED row format. For MyISAM 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, and CREATE 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.

•

Beginning with MySQL 8.0.17, the expr for a key_part specification can take the form (CAST

json_path AS type ARRAY) to create a multi-valued index on a JSON column. Multi-Valued

Indexes, provides detailed information regarding creation of, usage of, and restrictions and limitations

on multi-valued indexes.

>2591

•

index_type

Some storage engines permit you to specify an index type when creating an index. The syntax for the

index_type specifier is USING 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 you should expect it to 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. A KEY_BLOCK_SIZE

value specified for an individual index definition overrides the table-level KEY_BLOCK_SIZE value.

For information about the table-level KEY_BLOCK_SIZE attribute, see Table Options.

•

WITH PARSER

The WITH PARSER option can be used only with FULLTEXT indexes. It associates a parser plugin with

the index if full-text indexing and searching operations need special handling. InnoDB and MyISAM

support full-text parser plugins. If you have a MyISAM table with an associated full-text parser plugin,

you can convert the table to InnoDB using ALTER TABLE.

•

COMMENT

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 the index_option

COMMENT clause. See Section 15.8.11, “Configuring the Merge Threshold for Index Pages”.

•

VISIBLE, INVISIBLE

Specify index visibility. Indexes are visible by default. An invisible index is not used by the optimizer.

Specification of index visibility applies to indexes other than primary keys (either explicit or implicit).

For more information, see Section 8.3.12, “Invisible Indexes”.

•

ENGINE_ATTRIBUTE and SECONDARY_ENGINE_ATTRIBUTE options (available as of MySQL 8.0.21)

are used to specify index attributes for primary and secondary storage engines. The options are

reserved for future use.

For more information about permissible index_option values, see Section 13.1.15, “CREATE INDEX

Statement”. For more information about indexes, see Section 8.3.1, “How MySQL Uses Indexes”.

•

reference_definition

For reference_definition syntax details and examples, see Section 13.1.20.5, “FOREIGN KEY

Constraints”.

v>

table must always be explicitly named. Both ON DELETE and ON UPDATE actions on foreign keys

are supported. For more detailed information and examples, see Section 13.1.20.5, “FOREIGN KEY

Constraints”.

v>

For other storage engines, MySQL Server parses and ignores the FOREIGN KEY and REFERENCES

syntax in CREATE TABLE statements. See Section 1.7.2.3, “FOREIGN KEY Constraint Differences”.

v>

Important

For users familiar with the ANSI/ISO SQL Standard, please note that no storage

engine, including InnoDB, recognizes or enforces the MATCH clause used in

referential integrity constraint definitions. Use of an explicit MATCH clause does

not have the specified effect, and also causes ON DELETE and ON UPDATE

clauses to be ignored. For these reasons, specifying MATCH should be avoided.

The MATCH clause in the SQL standard controls how NULL values in a composite

(multiple-column) foreign key are handled when comparing to a primary key.

InnoDB essentially implements the semantics defined by MATCH SIMPLE, which

permit a foreign key to be all or partially NULL. 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 declared UNIQUE or NOT NULL. The handling of foreign

key references to nonunique keys or keys that contain NULL values is not well

defined for operations such as UPDATE or DELETE CASCADE. You are advised

to use foreign keys that reference only keys that are both UNIQUE (or PRIMARY)

and NOT 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 accepts REFERENCES clauses only when specified as part

of a separate FOREIGN KEY specification.

div>

• reference_option

For information about the RESTRICT, CASCADE, SET NULL, NO ACTION, and SET DEFAULT options,

see Section 13.1.20.5, “FOREIGN KEY Constraints”. </

div>

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. </