Movatterモバイル変換

Table Name

Temporary Tables

Table Cloning and Copying

Column Data Types and Attributes

Indexes, Foreign Keys, and CHECK Constraints

Table Options

Table Partitioning

tbl_name

The table name can be specified asdb_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 inSection 11.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 theCREATE TABLE statement.

LIKE

UseCREATE 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 TABLEnew_tbl LIKEorig_tbl;

For more information, seeSection 15.1.21.3, “CREATE TABLE ... LIKE Statement”.

[AS]query_expression

To create one table from another, add aSELECT statement at the end of theCREATE TABLE statement:

CREATE TABLEnew_tbl AS SELECT * FROMorig_tbl;

For more information, seeSection 15.1.21.4, “CREATE TABLE ... SELECT Statement”.

IGNORE | REPLACE

TheIGNORE andREPLACE options indicate how to handle rows that duplicate unique key values when copying a table using aSELECT statement.

For more information, seeSection 15.1.21.4, “CREATE TABLE ... SELECT Statement”.

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, seeChapter 13,Data Types.

NOT NULL | NULL

If neitherNULL norNOT NULL is specified, the column is treated as thoughNULL had been specified.

In MySQL 9.2, only theInnoDB,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 explicitDEFAULT value, seeSection 13.6, “Data Type Default Values”.

If theNO_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.

VISIBLE,INVISIBLE

Specify column visibility. The default isVISIBLE 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, seeSection 15.1.21.10, “Invisible Columns”.

AUTO_INCREMENT

An integer column can have the additional attributeAUTO_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 isvalue+1, wherevalue is the largest value for the column currently in the table.AUTO_INCREMENT sequences begin with1.

To retrieve anAUTO_INCREMENT value after inserting a row, use theLAST_INSERT_ID() SQL function or themysql_insert_id() C API function. SeeSection 14.15, “Information Functions”, andmysql_insert_id().

If theNO_AUTO_VALUE_ON_ZERO SQL mode is enabled, you can store0 inAUTO_INCREMENT columns as0 without generating a new sequence value. SeeSection 7.1.11, “Server SQL Modes”.

There can be only oneAUTO_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.

ForMyISAM tables, you can specify anAUTO_INCREMENT secondary column in a multiple-column key. SeeSection 5.6.9, “Using AUTO_INCREMENT”.

To make MySQL compatible with some ODBC applications, you can find theAUTO_INCREMENT value for the last inserted row with the following query:

SELECT * FROMtbl_name WHEREauto_col IS NULL

This method requires thatsql_auto_is_null variable is not set to 0. SeeSection 7.1.8, “Server System Variables”.

For information aboutInnoDB andAUTO_INCREMENT, seeSection 17.6.1.6, “AUTO_INCREMENT Handling in InnoDB”. For information aboutAUTO_INCREMENT and MySQL Replication, seeSection 19.5.1.1, “Replication and AUTO_INCREMENT”.

COMMENT

A comment for a column can be specified with theCOMMENT option, up to 1024 characters long. The comment is displayed by theSHOW CREATE TABLE andSHOW FULL COLUMNS statements. It is also shown in theCOLUMN_COMMENT column of the Information SchemaCOLUMNS table.

COLUMN_FORMAT

In NDB Cluster, it is also possible to specify a data storage format for individual columns ofNDB 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).

ForNDB tables, the default value forCOLUMN_FORMAT isFIXED.

In NDB Cluster, the maximum possible offset for a column defined withCOLUMN_FORMAT=FIXED is 8188 bytes. For more information and possible workarounds, seeSection 25.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 thanNDB. MySQL 9.2 silently ignoresCOLUMN_FORMAT.

ENGINE_ATTRIBUTE andSECONDARY_ENGINE_ATTRIBUTE options are used to specify column attributes for primary and secondary storage engines. The options are reserved for future use.

The value assigned to this option is 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 andSECONDARY_ENGINE_ATTRIBUTE values can be repeated without error. In this case, the last specified value is used.

ENGINE_ATTRIBUTE andSECONDARY_ENGINE_ATTRIBUTE values are not checked by the server, nor are they cleared when the table's storage engine is changed.

STORAGE

ForNDB 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)

ForNDB tables,STORAGE DEFAULT is equivalent toSTORAGE MEMORY.

TheSTORAGE clause has no effect on tables using storage engines other thanNDB. TheSTORAGE keyword is supported only in the build ofmysqld 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 aboutgenerated columns, seeSection 15.1.21.8, “CREATE TABLE and Generated Columns”.

Stored generated columns can be indexed.InnoDB supports secondary indexes onvirtual generated columns. SeeSection 15.1.21.9, “Secondary Indexes and Generated Columns”.

CONSTRAINTsymbol

TheCONSTRAINTsymbol clause may be given to name a constraint. If the clause is not given, or asymbol 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 atSection 11.2.1, “Identifier Length Limits”.

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 asNOT 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 aPRIMARY KEY and an application asks for thePRIMARY KEY in your tables, MySQL returns the firstUNIQUE index that has noNULL columns as thePRIMARY KEY.

InInnoDB 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. (SeeSection 17.6.2.1, “Clustered and Secondary Indexes”.)

In the created table, aPRIMARY 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.

APRIMARY 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(key_part, ...) clause.

If a table has aPRIMARY 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 inUnique Indexes.

In MySQL, the name of aPRIMARY 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 FROMtbl_name. SeeSection 15.7.7.24, “SHOW INDEX Statement”.

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

AUNIQUE 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 aPRIMARY 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 inUnique Indexes.

FULLTEXT

AFULLTEXT 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. SeeSection 14.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.InnoDB andMyISAM support full-text parser plugins. SeeFull-Text Parser Plugins andWriting Full-Text Parser Plugins for more information.

SPATIAL

You can createSPATIAL indexes on spatial data types. Spatial types are supported only forInnoDB andMyISAM tables, and indexed columns must be declared asNOT NULL. SeeSection 13.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, seereference_definition, andreference_option.

Partitioned tables employing theInnoDB storage engine do not support foreign keys. SeeSection 26.6, “Restrictions and Limitations on Partitioning”, for more information.

CHECK

TheCHECK clause enables the creation of constraints to be checked for data values in table rows. SeeSection 15.1.21.6, “CHECK Constraints”.

key_part

index_type

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

Example:

CREATE TABLE lookup  (id INT, INDEX USING BTREE (id)) ENGINE = MEMORY;

The preferred position forUSING 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.

For more information about permissibleindex_option values, seeSection 15.1.15, “CREATE INDEX Statement”. For more information about indexes, seeSection 10.3.1, “How MySQL Uses Indexes”.

reference_definition

Forreference_definition syntax details and examples, seeSection 15.1.21.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, seeSection 15.1.21.5, “FOREIGN KEY Constraints”.

For other storage engines, MySQL Server parses and ignores theFOREIGN KEY syntax inCREATE TABLE statements.

reference_option

For information about theRESTRICT,CASCADE,SET NULL,NO ACTION, andSET DEFAULT options, seeSection 15.1.21.5, “FOREIGN KEY Constraints”.

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.

By default, if a storage engine is specified that is not available, the statement fails with an error. You can override this behavior by removingNO_ENGINE_SUBSTITUTION from the server SQL mode (seeSection 7.1.11, “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.

AUTOEXTEND_SIZE

Defines the amount by whichInnoDB extends the size of the tablespace when it becomes full. The setting must be a multiple of 4MB. The default setting is 0, which causes the tablespace to be extended according to the implicit default behavior. For more information, seeSection 17.6.3.9, “Tablespace AUTOEXTEND_SIZE Configuration”.

AUTO_INCREMENT

The initialAUTO_INCREMENT value for the table. In MySQL 9.2, 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 theAUTO_INCREMENT table option inCREATE TABLE statements, you can also useALTER TABLEtbl_name AUTO_INCREMENT =N to reset theAUTO_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 aMyISAM 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. (SeeSection 7.1.8, “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. TheCHECKSUM 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 theInnoDBMERGE_THRESHOLD value for a table using thetable_optionCOMMENT clause. SeeSection 17.8.11, “Configuring the Merge Threshold for Index Pages”.

Setting NDB_TABLE options.  The table comment in aCREATE TABLE that creates anNDB table or anALTER TABLE statement which alters one 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=NDBCOMMENT="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 output ofSHOW 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 withALTER TABLE statements forNDB tables. Keep in mind that a table comment used withALTER TABLE replaces any existing comment which the table might have had previously.

Setting theMERGE_THRESHOLD option in table comments is not supported forNDB tables (it is ignored).

For complete syntax information and examples, seeSection 15.1.21.12, “Setting NDB Comment Options”.

COMPRESSION

The compression algorithm used for page level compression forInnoDB 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 infile-per-table tablespaces, and is only available on Linux and Windows platforms that support sparse files and hole punching. For more information, seeSection 17.9.2, “InnoDB Page Compression”.

CONNECTION

The connection string for aFEDERATED table.

DATA DIRECTORY,INDEX DIRECTORY

ForInnoDB, theDATA DIRECTORY='directory' clause permits creating tables outside of the data directory. Theinnodb_file_per_table variable must be enabled to use theDATA DIRECTORY clause. The full directory path must be specified, and known toInnoDB. For more information, seeSection 17.6.1.2, “Creating Tables Externally”.

When creatingMyISAM tables, you can use theDATA DIRECTORY='directory' clause, theINDEX DIRECTORY='directory' clause, or both. They specify where to put aMyISAM 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.

You must have theFILE privilege to use theDATA DIRECTORY orINDEX DIRECTORY table option.

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. SeeSection 10.12.2.2, “Using Symbolic Links for MyISAM Tables on Unix”, for more complete information.

If aMyISAM 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 does not overwrite existing files and returns an error instead.

If aMyISAM table is created with aDATA DIRECTORY orINDEX DIRECTORY option and an existing.MYD or.MYI file is found,MyISAM always returns an error, and does not overwrite a file in the specified directory.

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 thedelay_key_write system variable inSection 7.1.8, “Server System Variables”. (MyISAM only.)

ENCRYPTION

TheENCRYPTION clause enables or disables page-level data encryption for anInnoDB table. A keyring plugin must be installed and configured before encryption can be enabled. TheENCRYPTION clause can be specified when creating a table in an a file-per-table tablespace, or when creating a table in a general tablespace.

TheENCRYPTION option is supported only by theInnoDB storage engine; thus it works only if the default storage engine isInnoDB, or if theCREATE TABLE statement also specifiesENGINE=InnoDB. Otherwise the statement is rejected withER_CHECK_NOT_IMPLEMENTED.

A table inherits the default schema encryption if anENCRYPTION clause is not specified. If thetable_encryption_privilege_check variable is enabled, theTABLE_ENCRYPTION_ADMIN privilege is required to create a table with anENCRYPTION clause setting that differs from the default schema encryption. When creating a table in a general tablespace, table and tablespace encryption must match.

Specifying anENCRYPTION clause with a value other than'N' or'' is not permitted when using a storage engine that does not support encryption.

For more information, seeSection 17.13, “InnoDB Data-at-Rest Encryption”.

TheENGINE_ATTRIBUTE andSECONDARY_ENGINE_ATTRIBUTE options are used to specify table attributes for primary and secondary storage engines. The options are reserved for future use.

The value assigned to either of these options must be 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 andSECONDARY_ENGINE_ATTRIBUTE values can be repeated without error. In this case, the last specified value is used.

ENGINE_ATTRIBUTE andSECONDARY_ENGINE_ATTRIBUTE values are not checked by the server, nor are they cleared when the table's storage engine is changed.

INSERT_METHOD

If you want to insert data into aMERGE 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. SeeSection 18.7, “The MERGE Storage Engine”.

KEY_BLOCK_SIZE

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

ForInnoDB tables,KEY_BLOCK_SIZE specifies thepage size in kilobytes to use forcompressedInnoDB 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. SeeSection 17.9.1, “InnoDB Table Compression” for more information.

Oracle recommends enablinginnodb_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.

TheCreate_options column in response toSHOW TABLE STATUS reports the actualKEY_BLOCK_SIZE used by the table, as doesSHOW CREATE TABLE.

InnoDB only supportsKEY_BLOCK_SIZE at the table level.

KEY_BLOCK_SIZE is not supported with 32KB and 64KBinnodb_page_size values.InnoDB table compression does not support these pages sizes.

InnoDB does not support theKEY_BLOCK_SIZE option when creating temporary tables.

MAX_ROWS

The maximum number of rows you plan to store in the table. This is not a hard limit, but rather a hint to the storage engine that the table must be able to store at least this many rows.

TheNDB storage engine treats this value as a maximum. If you plan to create very large NDB Cluster tables (containing millions of rows), you should use this option to insure thatNDB allocates sufficient number of index slots in the hash table used for storing hashes of the table's primary keys by settingMAX_ROWS = 2 *rows, whererows is the number of rows that you expect to insert into the table.

The maximumMAX_ROWS value is 4294967295; larger values are truncated to this limit.

MIN_ROWS

The minimum number of rows you plan to store in the table. TheMEMORY storage engine uses this option as a hint about memory use.

PACK_KEYS

Takes effect only withMyISAM tables. Set this option to 1 if you want to have smaller indexes. This usually makes updates slower and reads faster. Setting the option to 0 disables all packing of keys. Setting it toDEFAULT tells the storage engine to pack only longCHAR,VARCHAR,BINARY, orVARBINARY columns.

If you do not usePACK_KEYS, the default is to pack strings, but not numbers. If you usePACK_KEYS=1, numbers are packed as well.

When packing binary number keys, MySQL uses prefix compression:

This means that if you have many equal keys on two consecutive rows, all following“same” keys usually only take two bytes (including the pointer to the row). Compare this to the ordinary case where the following keys takesstorage_size_for_key + pointer_size (where the pointer size is usually 4). Conversely, you get a significant benefit from prefix compression only if you have many numbers that are the same. If all keys are totally different, you use one byte more per key, if the key is not a key that can haveNULL values. (In this case, the packed key length is stored in the same byte that is used to mark if a key isNULL.)

PASSWORD

This option is unused.

ROW_FORMAT

Defines the physical format in which the rows are stored.

When creating a table withstrict mode disabled, the storage engine's default row format is used if the specified row format is not supported. The actual row format of the table is reported in theRow_format column in response toSHOW TABLE STATUS. TheCreate_options column shows the row format that was specified in theCREATE TABLE statement, as doesSHOW CREATE TABLE.

Row format choices differ depending on the storage engine used for the table.

ForInnoDB tables:

ForMyISAM tables, the option value can beFIXED orDYNAMIC for static or variable-length row format.myisampack sets the type toCOMPRESSED. SeeSection 18.2.3, “MyISAM Table Storage Formats”.

ForNDB tables, the defaultROW_FORMAT isDYNAMIC.

START TRANSACTION

This is an internal-use table option, used to permitCREATE TABLE ... SELECT to be logged as a single, atomic transaction in the binary log when using row-based replication with a storage engine that supports atomic DDL. OnlyBINLOG,COMMIT, andROLLBACK statements are permitted afterCREATE TABLE ... START TRANSACTION. For related information, seeSection 15.1.1, “Atomic Data Definition Statement Support”.

STATS_AUTO_RECALC

Specifies whether to automatically recalculatepersistent statistics for anInnoDB table. The valueDEFAULT causes the persistent statistics setting for the table to be determined by theinnodb_stats_auto_recalc configuration option. The value1 causes statistics to be recalculated when 10% of the data in the table has changed. The value0 prevents automatic recalculation for this table; with this setting, issue anANALYZE TABLE statement to recalculate the statistics after making substantial changes to the table. For more information about the persistent statistics feature, seeSection 17.8.10.1, “Configuring Persistent Optimizer Statistics Parameters”.

STATS_PERSISTENT

Specifies whether to enablepersistent statistics for anInnoDB table. The valueDEFAULT causes the persistent statistics setting for the table to be determined by theinnodb_stats_persistent configuration option. The value1 enables persistent statistics for the table, while the value0 turns off this feature. After enabling persistent statistics through aCREATE TABLE orALTER TABLE statement, issue anANALYZE TABLE statement to calculate the statistics, after loading representative data into the table. For more information about the persistent statistics feature, seeSection 17.8.10.1, “Configuring Persistent Optimizer Statistics Parameters”.

STATS_SAMPLE_PAGES

The number of index pages to sample when estimating cardinality and other statistics for an indexed column, such as those calculated byANALYZE TABLE. For more information, seeSection 17.8.10.1, “Configuring Persistent Optimizer Statistics Parameters”.

TABLESPACE

TheTABLESPACE clause can be used to create anInnoDB table in an existing general tablespace, a file-per-table tablespace, or the system tablespace.

CREATE TABLEtbl_name ... TABLESPACE [=]tablespace_name

The general tablespace that you specify must exist prior to using theTABLESPACE clause. For information about general tablespaces, seeSection 17.6.3.3, “General Tablespaces”.

Thetablespace_name is a case-sensitive identifier. It may be quoted or unquoted. The forward slash character (“/”) is not permitted. Names beginning with“innodb_” are reserved for special use.

To create a table in the system tablespace, specifyinnodb_system as the tablespace name.

CREATE TABLEtbl_name ... TABLESPACE [=] innodb_system

UsingTABLESPACE [=] innodb_system, you can place a table of any uncompressed row format in the system tablespace regardless of theinnodb_file_per_table setting. For example, you can add a table withROW_FORMAT=DYNAMIC to the system tablespace usingTABLESPACE [=] innodb_system.

To create a table in a file-per-table tablespace, specifyinnodb_file_per_table as the tablespace name.

CREATE TABLEtbl_name ... TABLESPACE [=] innodb_file_per_table

TheDATA DIRECTORY clause is permitted withCREATE TABLE ... TABLESPACE=innodb_file_per_table but is otherwise not supported for use in combination with theTABLESPACE clause. The directory specified in aDATA DIRECTORY clause must be known toInnoDB. For more information, seeUsing the DATA DIRECTORY Clause.

TheSTORAGE table option is employed only withNDB tables.STORAGE determines the type of storage used, and can be either ofDISK orMEMORY.

TABLESPACE ... STORAGE DISK assigns a table to an NDB Cluster Disk Data tablespace.STORAGE DISK cannot be used inCREATE TABLE unless preceded byTABLESPACEtablespace_name.

ForSTORAGE MEMORY, the tablespace name is optional, thus, you can useTABLESPACEtablespace_name STORAGE MEMORY or simplySTORAGE MEMORY to specify explicitly that the table is in-memory.

SeeSection 25.6.11, “NDB Cluster Disk Data Tables”, for more information.

UNION

Used to access a collection of identicalMyISAM tables as one. This works only withMERGE tables. SeeSection 18.7, “The MERGE Storage Engine”.

You must haveSELECT,UPDATE, andDELETE privileges for the tables you map to aMERGE table.

PARTITION BY

If used, apartition_options clause begins withPARTITION BY. This clause contains the function that is used to determine the partition; the function returns an integer value ranging from 1 tonum, wherenum is the number of partitions. (The maximum number of user-defined partitions which a table may contain is 1024; the number of subpartitions—discussed later in this section—is included in this maximum.)

HASH(expr)

Hashes one or more columns to create a key for placing and locating rows.expr is an expression using one or more table columns. This can be any valid MySQL expression (including MySQL functions) that yields a single integer value. For example, these are both validCREATE TABLE statements usingPARTITION BY HASH:

CREATE TABLE t1 (col1 INT, col2 CHAR(5))    PARTITION BY HASH(col1);CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATETIME)    PARTITION BY HASH ( YEAR(col3) );

You may not use eitherVALUES LESS THAN orVALUES IN clauses withPARTITION BY HASH.

PARTITION BY HASH uses the remainder ofexpr divided by the number of partitions (that is, the modulus). For examples and additional information, seeSection 26.2.4, “HASH Partitioning”.

TheLINEAR keyword entails a somewhat different algorithm. In this case, the number of the partition in which a row is stored is calculated as the result of one or more logicalAND operations. For discussion and examples of linear hashing, seeSection 26.2.4.1, “LINEAR HASH Partitioning”.

KEY(column_list)

This is similar toHASH, except that MySQL supplies the hashing function so as to guarantee an even data distribution. Thecolumn_list argument is simply a list of 1 or more table columns (maximum: 16). This example shows a simple table partitioned by key, with 4 partitions:

CREATE TABLE tk (col1 INT, col2 CHAR(5), col3 DATE)    PARTITION BY KEY(col3)    PARTITIONS 4;

For tables that are partitioned by key, you can employ linear partitioning by using theLINEAR keyword. This has the same effect as with tables that are partitioned byHASH. That is, the partition number is found using the& operator rather than the modulus (seeSection 26.2.4.1, “LINEAR HASH Partitioning”, andSection 26.2.5, “KEY Partitioning”, for details). This example uses linear partitioning by key to distribute data between 5 partitions:

CREATE TABLE tk (col1 INT, col2 CHAR(5), col3 DATE)    PARTITION BY LINEAR KEY(col3)    PARTITIONS 5;

TheALGORITHM={1 | 2} option is supported with[SUB]PARTITION BY [LINEAR] KEY.ALGORITHM=1 causes the server to use the same key-hashing functions as MySQL 5.1;ALGORITHM=2 means that the server employs the key-hashing functions implemented and used by default for newKEY partitioned tables in MySQL 5.5 and later. (Partitioned tables created with the key-hashing functions employed in MySQL 5.5 and later cannot be used by a MySQL 5.1 server.) Not specifying the option has the same effect as usingALGORITHM=2. This option is intended for use chiefly when upgrading or downgrading[LINEAR] KEY partitioned tables between MySQL 5.1 and later MySQL versions, or for creating tables partitioned byKEY orLINEAR KEY on a MySQL 5.5 or later server which can be used on a MySQL 5.1 server. For more information, seeSection 15.1.9.1, “ALTER TABLE Partition Operations”.

mysqldump writes this option encased in versioned comments.

ALGORITHM=1 is shown when necessary in the output ofSHOW CREATE TABLE using versioned comments in the same manner asmysqldump.ALGORITHM=2 is always omitted fromSHOW CREATE TABLE output, even if this option was specified when creating the original table.

You may not use eitherVALUES LESS THAN orVALUES IN clauses withPARTITION BY KEY.

RANGE(expr)

In this case,expr shows a range of values using a set ofVALUES LESS THAN operators. When using range partitioning, you must define at least one partition usingVALUES LESS THAN. You cannot useVALUES IN with range partitioning.

Suppose that you have a table that you wish to partition on a column containing year values, according to the following scheme.

A table implementing such a partitioning scheme can be realized by theCREATE TABLE statement shown here:

CREATE TABLE t1 (    year_col  INT,    some_data INT)PARTITION BY RANGE (year_col) (    PARTITION p0 VALUES LESS THAN (1991),    PARTITION p1 VALUES LESS THAN (1995),    PARTITION p2 VALUES LESS THAN (1999),    PARTITION p3 VALUES LESS THAN (2002),    PARTITION p4 VALUES LESS THAN (2006),    PARTITION p5 VALUES LESS THAN MAXVALUE);

PARTITION ... VALUES LESS THAN ... statements work in a consecutive fashion.VALUES LESS THAN MAXVALUE works to specify“leftover” values that are greater than the maximum value otherwise specified.

VALUES LESS THAN clauses work sequentially in a manner similar to that of thecase portions of aswitch ... case block (as found in many programming languages such as C, Java, and PHP). That is, the clauses must be arranged in such a way that the upper limit specified in each successiveVALUES LESS THAN is greater than that of the previous one, with the one referencingMAXVALUE coming last of all in the list.

RANGE COLUMNS(column_list)

This variant onRANGE facilitates partition pruning for queries using range conditions on multiple columns (that is, having conditions such asWHERE a = 1 AND b < 10 orWHERE a = 1 AND b = 10 AND c < 10). It enables you to specify value ranges in multiple columns by using a list of columns in theCOLUMNS clause and a set of column values in eachPARTITION ... VALUES LESS THAN (value_list) partition definition clause. (In the simplest case, this set consists of a single column.) The maximum number of columns that can be referenced in thecolumn_list andvalue_list is 16.

Thecolumn_list used in theCOLUMNS clause may contain only names of columns; each column in the list must be one of the following MySQL data types: the integer types; the string types; and time or date column types. Columns usingBLOB,TEXT,SET,ENUM,BIT, or spatial data types are not permitted; columns that use floating-point number types are also not permitted. You also may not use functions or arithmetic expressions in theCOLUMNS clause.

TheVALUES LESS THAN clause used in a partition definition must specify a literal value for each column that appears in theCOLUMNS() clause; that is, the list of values used for eachVALUES LESS THAN clause must contain the same number of values as there are columns listed in theCOLUMNS clause. An attempt to use more or fewer values in aVALUES LESS THAN clause than there are in theCOLUMNS clause causes the statement to fail with the errorInconsistency in usage of column lists for partitioning.... You cannot useNULL for any value appearing inVALUES LESS THAN. It is possible to useMAXVALUE more than once for a given column other than the first, as shown in this example:

CREATE TABLE rc (    a INT NOT NULL,    b INT NOT NULL)PARTITION BY RANGE COLUMNS(a,b) (    PARTITION p0 VALUES LESS THAN (10,5),    PARTITION p1 VALUES LESS THAN (20,10),    PARTITION p2 VALUES LESS THAN (50,MAXVALUE),    PARTITION p3 VALUES LESS THAN (65,MAXVALUE),    PARTITION p4 VALUES LESS THAN (MAXVALUE,MAXVALUE));

Each value used in aVALUES LESS THAN value list must match the type of the corresponding column exactly; no conversion is made. For example, you cannot use the string'1' for a value that matches a column that uses an integer type (you must use the numeral1 instead), nor can you use the numeral1 for a value that matches a column that uses a string type (in such a case, you must use a quoted string:'1').

For more information, seeSection 26.2.1, “RANGE Partitioning”, andSection 26.4, “Partition Pruning”.

LIST(expr)

This is useful when assigning partitions based on a table column with a restricted set of possible values, such as a state or country code. In such a case, all rows pertaining to a certain state or country can be assigned to a single partition, or a partition can be reserved for a certain set of states or countries. It is similar toRANGE, except that onlyVALUES IN may be used to specify permissible values for each partition.

VALUES IN is used with a list of values to be matched. For instance, you could create a partitioning scheme such as the following:

CREATE TABLE client_firms (    id   INT,    name VARCHAR(35))PARTITION BY LIST (id) (    PARTITION r0 VALUES IN (1, 5, 9, 13, 17, 21),    PARTITION r1 VALUES IN (2, 6, 10, 14, 18, 22),    PARTITION r2 VALUES IN (3, 7, 11, 15, 19, 23),    PARTITION r3 VALUES IN (4, 8, 12, 16, 20, 24));

When using list partitioning, you must define at least one partition usingVALUES IN. You cannot useVALUES LESS THAN withPARTITION BY LIST.

LIST COLUMNS(column_list)

This variant onLIST facilitates partition pruning for queries using comparison conditions on multiple columns (that is, having conditions such asWHERE a = 5 AND b = 5 orWHERE a = 1 AND b = 10 AND c = 5). It enables you to specify values in multiple columns by using a list of columns in theCOLUMNS clause and a set of column values in eachPARTITION ... VALUES IN (value_list) partition definition clause.

The rules governing regarding data types for the column list used inLIST COLUMNS(column_list) and the value list used inVALUES IN(value_list) are the same as those for the column list used inRANGE COLUMNS(column_list) and the value list used inVALUES LESS THAN(value_list), respectively, except that in theVALUES IN clause,MAXVALUE is not permitted, and you may useNULL.

There is one important difference between the list of values used forVALUES IN withPARTITION BY LIST COLUMNS as opposed to when it is used withPARTITION BY LIST. When used withPARTITION BY LIST COLUMNS, each element in theVALUES IN clause must be aset of column values; the number of values in each set must be the same as the number of columns used in theCOLUMNS clause, and the data types of these values must match those of the columns (and occur in the same order). In the simplest case, the set consists of a single column. The maximum number of columns that can be used in thecolumn_list and in the elements making up thevalue_list is 16.

The table defined by the followingCREATE TABLE statement provides an example of a table usingLIST COLUMNS partitioning:

CREATE TABLE lc (    a INT NULL,    b INT NULL)PARTITION BY LIST COLUMNS(a,b) (    PARTITION p0 VALUES IN( (0,0), (NULL,NULL) ),    PARTITION p1 VALUES IN( (0,1), (0,2), (0,3), (1,1), (1,2) ),    PARTITION p2 VALUES IN( (1,0), (2,0), (2,1), (3,0), (3,1) ),    PARTITION p3 VALUES IN( (1,3), (2,2), (2,3), (3,2), (3,3) ));

PARTITIONSnum

The number of partitions may optionally be specified with aPARTITIONSnum clause, wherenum is the number of partitions. If both this clauseand anyPARTITION clauses are used,num must be equal to the total number of any partitions that are declared usingPARTITION clauses.

SUBPARTITION BY

A partition may optionally be divided into a number of subpartitions. This can be indicated by using the optionalSUBPARTITION BY clause. Subpartitioning may be done byHASH orKEY. Either of these may beLINEAR. These work in the same way as previously described for the equivalent partitioning types. (It is not possible to subpartition byLIST orRANGE.)

The number of subpartitions can be indicated using theSUBPARTITIONS keyword followed by an integer value.

Rigorous checking of the value used inPARTITIONS orSUBPARTITIONS clauses is applied and this value must adhere to the following rules:

partition_definition

Each partition may be individually defined using apartition_definition clause. The individual parts making up this clause are as follows:

subpartition_definition

The partition definition may optionally contain one or moresubpartition_definition clauses. Each of these consists at a minimum of theSUBPARTITIONname, wherename is an identifier for the subpartition. Except for the replacement of thePARTITION keyword withSUBPARTITION, the syntax for a subpartition definition is identical to that for a partition definition.

Subpartitioning must be done byHASH orKEY, and can be done only onRANGE orLIST partitions. SeeSection 26.2.6, “Subpartitioning”.