Movatterモバイル変換

ALTER TABLEtbl_name    [alter_option [,alter_option] ...]    [partition_options]alter_option: {table_options  | ADD [COLUMN]col_namecolumn_definition        [FIRST | AFTERcol_name]  | ADD [COLUMN] (col_namecolumn_definition,...)  | ADD {INDEX | KEY} [index_name]        [index_type] (key_part,...) [index_option] ...  | ADD {FULLTEXT | SPATIAL} [INDEX | KEY] [index_name]        (key_part,...) [index_option] ...  | ADD [CONSTRAINT [symbol]] PRIMARY KEY        [index_type] (key_part,...)        [index_option] ...  | ADD [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY]        [index_name] [index_type] (key_part,...)        [index_option] ...  | ADD [CONSTRAINT [symbol]] FOREIGN KEY        [index_name] (col_name,...)reference_definition  | ADD [CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]  | DROP {CHECK | CONSTRAINT}symbol  | ALTER {CHECK | CONSTRAINT}symbol [NOT] ENFORCED  | ALGORITHM [=] {DEFAULT | INSTANT | INPLACE | COPY}  | ALTER [COLUMN]col_name {        SET DEFAULT {literal | (expr)}      | SET {VISIBLE | INVISIBLE}      | DROP DEFAULT    }  | ALTER INDEXindex_name {VISIBLE | INVISIBLE}  | CHANGE [COLUMN]old_col_namenew_col_namecolumn_definition        [FIRST | AFTERcol_name]  | [DEFAULT] CHARACTER SET [=]charset_name [COLLATE [=]collation_name]  | CONVERT TO CHARACTER SETcharset_name [COLLATEcollation_name]  | {DISABLE | ENABLE} KEYS  | {DISCARD | IMPORT} TABLESPACE  | DROP [COLUMN]col_name  | DROP {INDEX | KEY}index_name  | DROP PRIMARY KEY  | DROP FOREIGN KEYfk_symbol  | FORCE  | LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}  | MODIFY [COLUMN]col_namecolumn_definition        [FIRST | AFTERcol_name]  | ORDER BYcol_name [,col_name] ...  | RENAME COLUMNold_col_name TOnew_col_name  | RENAME {INDEX | KEY}old_index_name TOnew_index_name  | RENAME [TO | AS]new_tbl_name  | {WITHOUT | WITH} VALIDATION}partition_options:partition_option [partition_option] ...partition_option: {    ADD PARTITION (partition_definition)  | DROP PARTITIONpartition_names  | DISCARD PARTITION {partition_names | ALL} TABLESPACE  | IMPORT PARTITION {partition_names | ALL} TABLESPACE  | TRUNCATE PARTITION {partition_names | ALL}  | COALESCE PARTITIONnumber  | REORGANIZE PARTITIONpartition_names INTO (partition_definitions)  | EXCHANGE PARTITIONpartition_name WITH TABLEtbl_name [{WITH | WITHOUT} VALIDATION]  | ANALYZE PARTITION {partition_names | ALL}  | CHECK PARTITION {partition_names | ALL}  | OPTIMIZE PARTITION {partition_names | ALL}  | REBUILD PARTITION {partition_names | ALL}  | REPAIR PARTITION {partition_names | ALL}  | REMOVE PARTITIONING}key_part: {col_name [(length)] | (expr)} [ASC | DESC]index_type:    USING {BTREE | HASH}index_option: {    KEY_BLOCK_SIZE [=]value  |index_type  | WITH PARSERparser_name  | COMMENT 'string'  | {VISIBLE | INVISIBLE}}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  | TABLESPACEtablespace_name [STORAGE {DISK | MEMORY}]  | UNION [=] (tbl_name[,tbl_name]...)}partition_options:    (see CREATE TABLE options)

ALTER TABLE changes the structure of a table. For example, you can add or delete columns, create or destroy indexes, change the type of existing columns, or rename columns or the table itself. You can also change characteristics such as the storage engine used for the table or the table comment.

There are several additional aspects to theALTER TABLE statement, described under the following topics in this section:

Table Options

table_options signifies table options of the kind that can be used in theCREATE TABLE statement, such asENGINE,AUTO_INCREMENT,AVG_ROW_LENGTH,MAX_ROWS,ROW_FORMAT, orTABLESPACE.

For descriptions of all table options, seeSection 15.1.22, “CREATE TABLE Statement”. However,ALTER TABLE ignoresDATA DIRECTORY andINDEX DIRECTORY when given as table options.ALTER TABLE permits them only as partitioning options, and requires that you have theFILE privilege.

Use of table options withALTER TABLE provides a convenient way of altering single table characteristics. For example:

To verify that the table options were changed as intended, useSHOW CREATE TABLE, or query the Information SchemaTABLES table.

Performance and Space Requirements

ALTER TABLE operations are processed using one of the following algorithms:

For tables using theNDB storage engine, these algorithms work as follows:

SeeSection 25.6.12, “Online Operations with ALTER TABLE in NDB Cluster”, for more information.

TheALGORITHM clause is optional. If theALGORITHM clause is omitted, MySQL usesALGORITHM=INSTANT for storage engines andALTER TABLE clauses that support it. Otherwise,ALGORITHM=INPLACE is used. IfALGORITHM=INPLACE is not supported,ALGORITHM=COPY is used.

Specifying anALGORITHM clause requires the operation to use the specified algorithm for clauses and storage engines that support it, or fail with an error otherwise. SpecifyingALGORITHM=DEFAULT is the same as omitting theALGORITHM clause.

ALTER TABLE operations that use theCOPY algorithm wait for other operations that are modifying the table to complete. After alterations are applied to the table copy, data is copied over, the original table is deleted, and the table copy is renamed to the name of the original table. While theALTER TABLE operation executes, the original table is readable by other sessions (with the exception noted shortly). Updates and writes to the table started after theALTER TABLE operation begins are stalled until the new table is ready, then are automatically redirected to the new table. The temporary copy of the table is created in the database directory of the original table unless it is aRENAME TO operation that moves the table to a database that resides in a different directory.

The exception referred to earlier is thatALTER TABLE blocks reads (not just writes) at the point where it is ready to clear outdated table structures from the table and table definition caches. At this point, it must acquire an exclusive lock. To do so, it waits for current readers to finish, and blocks new reads and writes.

AnALTER TABLE operation that uses theCOPY algorithm prevents concurrent DML operations. Concurrent queries are still allowed. That is, a table-copying operation always includes at least the concurrency restrictions ofLOCK=SHARED (allow queries but not DML). You can further restrict concurrency for operations that support theLOCK clause by specifyingLOCK=EXCLUSIVE, which prevents DML and queries. For more information, seeConcurrency Control.

To force use of theCOPY algorithm for anALTER TABLE operation that would otherwise not use it, specifyALGORITHM=COPY or enable theold_alter_table system variable. If there is a conflict between theold_alter_table setting and anALGORITHM clause with a value other thanDEFAULT, theALGORITHM clause takes precedence.

ForInnoDB tables, anALTER TABLE operation that uses theCOPY algorithm on a table that resides in ashared tablespace can increase the amount of space used by the tablespace. Such operations require as much additional space as the data in the table plus indexes. For a table residing in a shared tablespace, the additional space used during the operation is not released back to the operating system as it is for a table that resides in afile-per-table tablespace.

For information about space requirements for online DDL operations, seeSection 17.12.3, “Online DDL Space Requirements”.

ALTER TABLE operations that support theINPLACE algorithm include:

ALTER TABLE operations that support theINSTANT algorithm include:

For more information about operations that supportALGORITHM=INSTANT, seeSection 17.12.1, “Online DDL Operations”.

ALTER TABLE upgrades MySQL 5.5 temporal columns to 5.6 format forADD COLUMN,CHANGE COLUMN,MODIFY COLUMN,ADD INDEX, andFORCE operations. This conversion cannot be done using theINPLACE algorithm because the table must be rebuilt, so specifyingALGORITHM=INPLACE in these cases results in an error. SpecifyALGORITHM=COPY if necessary.

If anALTER TABLE operation on a multicolumn index used to partition a table byKEY changes the order of the columns, it can only be performed usingALGORITHM=COPY.

TheWITHOUT VALIDATION andWITH VALIDATION clauses affect whetherALTER TABLE performs an in-place operation forvirtual generated column modifications. SeeSection 15.1.10.2, “ALTER TABLE and Generated Columns”.

NDB Cluster 9.3 supports online operations using the sameALGORITHM=INPLACE syntax used with the standard MySQL Server.NDB does not allow changing a tablespace online. SeeSection 25.6.12, “Online Operations with ALTER TABLE in NDB Cluster”, for more information.

When performing a copyingALTER TABLE,NDB checks to ensure that no concurrent writes have been made to the affected table. If it finds that any have been made,NDB rejects theALTER TABLE statement and raisesER_TABLE_DEF_CHANGED.

ALTER TABLE withDISCARD ... PARTITION ... TABLESPACE orIMPORT ... PARTITION ... TABLESPACE does not create any temporary tables or temporary partition files.

ALTER TABLE withADD PARTITION,DROP PARTITION,COALESCE PARTITION,REBUILD PARTITION, orREORGANIZE PARTITION does not create temporary tables (except when used withNDB tables); however, these operations can and do create temporary partition files.

ADD orDROP operations forRANGE orLIST partitions are immediate operations or nearly so.ADD orCOALESCE operations forHASH orKEY partitions copy data between all partitions, unlessLINEAR HASH orLINEAR KEY was used; this is effectively the same as creating a new table, although theADD orCOALESCE operation is performed partition by partition.REORGANIZE operations copy only changed partitions and do not touch unchanged ones.

ForMyISAM tables, you can speed up index re-creation (the slowest part of the alteration process) by setting themyisam_sort_buffer_size system variable to a high value.

Concurrency Control

ForALTER TABLE operations that support it, you can use theLOCK clause to control the level of concurrent reads and writes on a table while it is being altered. Specifying a non-default value for this clause enables you to require a certain amount of concurrent access or exclusivity during the alter operation, and halts the operation if the requested degree of locking is not available.

OnlyLOCK = DEFAULT is permitted for operations that useALGORITHM=INSTANT. The otherLOCK clause parameters are not applicable.

The parameters for theLOCK clause are:

Adding and Dropping Columns

UseADD to add new columns to a table, andDROP to remove existing columns.DROPcol_name is a MySQL extension to standard SQL.

To add a column at a specific position within a table row, useFIRST orAFTERcol_name. The default is to add the column last.

If a table contains only one column, the column cannot be dropped. If what you intend is to remove the table, use theDROP TABLE statement instead.

If columns are dropped from a table, the columns are also removed from any index of which they are a part. If all columns that make up an index are dropped, the index is dropped as well. If you useCHANGE orMODIFY to shorten a column for which an index exists on the column, and the resulting column length is less than the index length, MySQL shortens the index automatically.

ForALTER TABLE ... ADD, if the column has an expression default value that uses a nondeterministic function, the statement may produce a warning or error. For further information, seeSection 13.6, “Data Type Default Values”, andSection 19.1.3.7, “Restrictions on Replication with GTIDs”.

Renaming, Redefining, and Reordering Columns

TheCHANGE,MODIFY,RENAME COLUMN, andALTER clauses enable the names and definitions of existing columns to be altered. They have these comparative characteristics:

CHANGE is a MySQL extension to standard SQL.MODIFY andRENAME COLUMN are MySQL extensions for Oracle compatibility.

To alter a column to change both its name and definition, useCHANGE, specifying the old and new names and the new definition. For example, to rename anINT NOT NULL column froma tob and change its definition to use theBIGINT data type while retaining theNOT NULL attribute, do this:

ALTER TABLE t1 CHANGE a b BIGINT NOT NULL;

To change a column definition but not its name, useCHANGE orMODIFY. WithCHANGE, the syntax requires two column names, so you must specify the same name twice to leave the name unchanged. For example, to change the definition of columnb, do this:

ALTER TABLE t1 CHANGE b b INT NOT NULL;

MODIFY is more convenient to change the definition without changing the name because it requires the column name only once:

ALTER TABLE t1 MODIFY b INT NOT NULL;

To change a column name but not its definition, useCHANGE orRENAME COLUMN. WithCHANGE, the syntax requires a column definition, so to leave the definition unchanged, you must respecify the definition the column currently has. For example, to rename anINT NOT NULL column fromb toa, do this:

ALTER TABLE t1 CHANGE b a INT NOT NULL;

RENAME COLUMN is more convenient to change the name without changing the definition because it requires only the old and new names:

ALTER TABLE t1 RENAME COLUMN b TO a;

In general, you cannot rename a column to a name that already exists in the table. However, this is sometimes not the case, such as when you swap names or move them through a cycle. If a table has columns nameda,b, andc, these are valid operations:

-- swap a and bALTER TABLE t1 RENAME COLUMN a TO b,               RENAME COLUMN b TO a;-- "rotate" a, b, c through a cycleALTER TABLE t1 RENAME COLUMN a TO b,               RENAME COLUMN b TO c,               RENAME COLUMN c TO a;

For column definition changes usingCHANGE orMODIFY, the definition must include the data type and all attributes that should apply to the new column, other than index attributes such asPRIMARY KEY orUNIQUE. Attributes present in the original definition but not specified for the new definition are not carried forward. Suppose that a columncol1 is defined asINT UNSIGNED DEFAULT 1 COMMENT 'my column' and you modify the column as follows, intending to change onlyINT toBIGINT:

ALTER TABLE t1 MODIFY col1 BIGINT;

That statement changes the data type fromINT toBIGINT, but it also drops theUNSIGNED,DEFAULT, andCOMMENT attributes. To retain them, the statement must include them explicitly:

ALTER TABLE t1 MODIFY col1 BIGINT UNSIGNED DEFAULT 1 COMMENT 'my column';

For data type changes usingCHANGE orMODIFY, MySQL tries to convert existing column values to the new type as well as possible.

If you useCHANGE orMODIFY to shorten a column for which an index exists on the column, and the resulting column length is less than the index length, MySQL shortens the index automatically.

For columns renamed byCHANGE orRENAME COLUMN, MySQL automatically renames these references to the renamed column:

For columns renamed byCHANGE orRENAME COLUMN, MySQL does not automatically rename these references to the renamed column:

To reorder columns within a table, useFIRST andAFTER inCHANGE orMODIFY operations.

ALTER ... SET DEFAULT orALTER ... DROP DEFAULT specify a new default value for a column or remove the old default value, respectively. If the old default is removed and the column can beNULL, the new default isNULL. If the column cannot beNULL, MySQL assigns a default value as described inSection 13.6, “Data Type Default Values”.

ALTER ... SET VISIBLE andALTER ... SET INVISIBLE enable column visibility to be changed. SeeSection 15.1.22.10, “Invisible Columns”.

Primary Keys and Indexes

DROP PRIMARY KEY drops theprimary key. If there is no primary key, an error occurs. For information about the performance characteristics of primary keys, especially forInnoDB tables, seeSection 10.3.2, “Primary Key Optimization”.

If thesql_require_primary_key system variable is enabled, attempting to drop a primary key produces an error.

If you add aUNIQUE INDEX orPRIMARY KEY to a table, MySQL stores it before any nonunique index to permit detection of duplicate keys as early as possible.

DROP INDEX removes an index. This is a MySQL extension to standard SQL. SeeSection 15.1.29, “DROP INDEX Statement”. To determine index names, useSHOW INDEX FROMtbl_name.

Some storage engines permit you to specify an index type when creating an index. The syntax for theindex_type specifier isUSINGtype_name. For details aboutUSING, seeSection 15.1.16, “CREATE INDEX Statement”. The preferred position is after the column list. Expect support for use of the option before the column list to be removed in a future MySQL release.

index_option values specify additional options for an index.USING is one such option. For details about permissibleindex_option values, seeSection 15.1.16, “CREATE INDEX Statement”.

RENAME INDEXold_index_name TOnew_index_name renames an index. This is a MySQL extension to standard SQL. The content of the table remains unchanged.old_index_name must be the name of an existing index in the table that is not dropped by the sameALTER TABLE statement.new_index_name is the new index name, which cannot duplicate the name of an index in the resulting table after changes have been applied. Neither index name can bePRIMARY.

If you useALTER TABLE on aMyISAM table, all nonunique indexes are created in a separate batch (as forREPAIR TABLE). This should makeALTER TABLE much faster when you have many indexes.

ForMyISAM tables, key updating can be controlled explicitly. UseALTER TABLE ... DISABLE KEYS to tell MySQL to stop updating nonunique indexes. Then useALTER TABLE ... ENABLE KEYS to re-create missing indexes.MyISAM does this with a special algorithm that is much faster than inserting keys one by one, so disabling keys before performing bulk insert operations should give a considerable speedup. UsingALTER TABLE ... DISABLE KEYS requires theINDEX privilege in addition to the privileges mentioned earlier.

While the nonunique indexes are disabled, they are ignored for statements such asSELECT andEXPLAIN that otherwise would use them.

After anALTER TABLE statement, it may be necessary to runANALYZE TABLE to update index cardinality information. SeeSection 15.7.7.24, “SHOW INDEX Statement”.

TheALTER INDEX operation permits an index to be made visible or invisible. An invisible index is not used by the optimizer. Modification of index visibility applies to indexes other than primary keys (either explicit or implicit), and cannot be performed usingALGORITHM=INSTANT. This feature is storage engine neutral (supported for any engine). For more information, seeSection 10.3.12, “Invisible Indexes”.

Foreign Keys and Other Constraints

TheFOREIGN KEY andREFERENCES clauses are supported by theInnoDB andNDB storage engines, which implementADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (...) REFERENCES ... (...). SeeSection 15.1.22.5, “FOREIGN KEY Constraints”. For other storage engines, the clauses are parsed but ignored.

ForALTER TABLE, unlikeCREATE TABLE,ADD FOREIGN KEY ignoresindex_name if given and uses an automatically generated foreign key name. As a workaround, include theCONSTRAINT clause to specify the foreign key name:

ADD CONSTRAINTname FOREIGN KEY (....) ...

MySQL Server and NDB Cluster both support the use ofALTER TABLE to drop foreign keys:

ALTER TABLEtbl_name DROP FOREIGN KEYfk_symbol;

Adding and dropping a foreign key in the sameALTER TABLE statement is supported forALTER TABLE ... ALGORITHM=INPLACE but not forALTER TABLE ... ALGORITHM=COPY.

The server prohibits changes to foreign key columns that have the potential to cause loss of referential integrity. A workaround is to useALTER TABLE ... DROP FOREIGN KEY before changing the column definition andALTER TABLE ... ADD FOREIGN KEY afterward. Examples of prohibited changes include:

ALTER TABLEtbl_name RENAMEnew_tbl_name changes internally generated foreign key constraint names and user-defined foreign key constraint names that begin with the string“tbl_name_ibfk_” to reflect the new table name.InnoDB interprets foreign key constraint names that begin with the string“tbl_name_ibfk_” as internally generated names.

ALTER TABLE permitsCHECK constraints for existing tables to be added, dropped, or altered:

TheDROP CHECK andALTER CHECK clauses are MySQL extensions to standard SQL.

ALTER TABLE permits more general (and SQL standard) syntax for dropping and altering existing constraints of any type, where the constraint type is determined from the constraint 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, but constraints of different types can have the same name. When multiple constraints have the same name,DROP CONSTRAINT andADD CONSTRAINT are ambiguous and an error occurs. In such cases, constraint-specific syntax must be used to modify the constraint. For example, useDROP PRIMARY KEY or DROP FOREIGN KEY to drop a primary key or foreign key.

If a table alteration causes a violation of an enforcedCHECK constraint, an error occurs and the table is not modified. Examples of operations for which an error occurs:

ALTER TABLEtbl_name RENAMEnew_tbl_name changes internally generated and user-definedCHECK constraint names that begin with the string“tbl_name_chk_” to reflect the new table name. MySQL interpretsCHECK constraint names that begin with the string“tbl_name_chk_” as internally generated names.

Changing the Character Set

To change the table default character set and all character columns (CHAR,VARCHAR,TEXT) to a new character set, use a statement like this:

ALTER TABLEtbl_name CONVERT TO CHARACTER SETcharset_name;

The statement also changes the collation of all character columns. If you specify noCOLLATE clause to indicate which collation to use, the statement uses default collation for the character set. If this collation is inappropriate for the intended table use (for example, if it would change from a case-sensitive collation to a case-insensitive collation), specify a collation explicitly.

For a column that has a data type ofVARCHAR or one of theTEXT types,CONVERT TO CHARACTER SET changes the data type as necessary to ensure that the new column is long enough to store as many characters as the original column. For example, aTEXT column has two length bytes, which store the byte-length of values in the column, up to a maximum of 65,535. For alatin1TEXT column, each character requires a single byte, so the column can store up to 65,535 characters. If the column is converted toutf8mb4, each character might require up to 4 bytes, for a maximum possible length of 4 × 65,535 = 262,140 bytes. That length does not fit in aTEXT column's length bytes, so MySQL converts the data type toMEDIUMTEXT, which is the smallest string type for which the length bytes can record a value of 262,140. Similarly, aVARCHAR column might be converted toMEDIUMTEXT.

To avoid data type changes of the type just described, do not useCONVERT TO CHARACTER SET. Instead, useMODIFY to change individual columns. For example:

ALTER TABLE t MODIFY latin1_text_col TEXT CHARACTER SET utf8mb4;ALTER TABLE t MODIFY latin1_varchar_col VARCHAR(M) CHARACTER SET utf8mb4;

If you specifyCONVERT TO CHARACTER SET binary, theCHAR,VARCHAR, andTEXT columns are converted to their corresponding binary string types (BINARY,VARBINARY,BLOB). This means that the columns no longer have a character set and a subsequentCONVERT TO operation does not apply to them.

Ifcharset_name isDEFAULT in aCONVERT TO CHARACTER SET operation, the character set named by thecharacter_set_database system variable is used.

ALTER TABLE t1 CHANGE c1 c1 BLOB;ALTER TABLE t1 CHANGE c1 c1 TEXT CHARACTER SET utf8mb4;

To change only thedefault character set for a table, use this statement:

ALTER TABLEtbl_name DEFAULT CHARACTER SETcharset_name;

The wordDEFAULT is optional. The default character set is the character set that is used if you do not specify the character set for columns that you add to a table later (for example, withALTER TABLE ... ADD column).

When theforeign_key_checks system variable is enabled, which is the default setting, character set conversion is not permitted on tables that include a character string column used in a foreign key constraint. The workaround is to disableforeign_key_checks before performing the character set conversion. You must perform the conversion on both tables involved in the foreign key constraint before re-enablingforeign_key_checks. If you re-enableforeign_key_checks after converting only one of the tables, anON DELETE CASCADE orON UPDATE CASCADE operation could corrupt data in the referencing table due to implicit conversion that occurs during these operations (Bug #45290, Bug #74816).

Importing InnoDB Tables

AnInnoDB table created in its ownfile-per-table tablespace can be imported from a backup or from another MySQL server instance usingDISCARD TABLEPACE andIMPORT TABLESPACE clauses. SeeSection 17.6.1.3, “Importing InnoDB Tables”.

Row Order for MyISAM Tables

ORDER BY enables you to create the new table with the rows in a specific order. This option is useful primarily when you know that you query the rows in a certain order most of the time. By using this option after major changes to the table, you might be able to get higher performance. In some cases, it might make sorting easier for MySQL if the table is in order by the column that you want to order it by later.

ORDER BY syntax permits one or more column names to be specified for sorting, each of which optionally can be followed byASC orDESC to indicate ascending or descending sort order, respectively. The default is ascending order. Only column names are permitted as sort criteria; arbitrary expressions are not permitted. This clause should be given last after any other clauses.

ORDER BY does not make sense forInnoDB tables becauseInnoDB always orders table rows according to theclustered index.

When used on a partitioned table,ALTER TABLE ... ORDER BY orders rows within each partition only.

Partitioning Options

partition_options signifies options that can be used with partitioned tables for repartitioning, to add, drop, discard, import, merge, and split partitions, and to perform partitioning maintenance.

It is possible for anALTER TABLE statement to contain aPARTITION BY orREMOVE PARTITIONING clause in an addition to other alter specifications, but thePARTITION BY orREMOVE PARTITIONING clause must be specified last after any other specifications. TheADD PARTITION,DROP PARTITION,DISCARD PARTITION,IMPORT PARTITION,COALESCE PARTITION,REORGANIZE PARTITION,EXCHANGE PARTITION,ANALYZE PARTITION,CHECK PARTITION, andREPAIR PARTITION options cannot be combined with other alter specifications in a singleALTER TABLE, since the options just listed act on individual partitions.

For more information about partition options, seeSection 15.1.22, “CREATE TABLE Statement”, andSection 15.1.10.1, “ALTER TABLE Partition Operations”. For information about and examples ofALTER TABLE ... EXCHANGE PARTITION statements, seeSection 26.3.3, “Exchanging Partitions and Subpartitions with Tables”.