ALTER TABLE
This statement modifies an existing table to conform to a new table structure. The statement ALTER TABLE can be used to:
Synopsis
- AlterTableStmt
- TableName
- AlterTableSpec
- PlacementPolicyOption
AlterTableStmt ::=
'ALTER' IgnoreOptional 'TABLE' TableName (
AlterTableSpecListOpt AlterTablePartitionOpt |
'ANALYZE' 'PARTITION' PartitionNameList ( 'INDEX' IndexNameList )? AnalyzeOptionListOpt |
'COMPACT' ( 'PARTITION' PartitionNameList )? 'TIFLASH' 'REPLICA'
)
TableName ::=
Identifier ('.' Identifier)?
AlterTableSpec ::=
TableOptionList
| 'SET' 'TIFLASH' 'REPLICA' LengthNum LocationLabelList
| 'CONVERT' 'TO' CharsetKw ( CharsetName | 'DEFAULT' ) OptCollate
| 'ADD' ( ColumnKeywordOpt IfNotExists ( ColumnDef ColumnPosition | '(' TableElementList ')' ) | Constraint | 'PARTITION' IfNotExists NoWriteToBinLogAliasOpt ( PartitionDefinitionListOpt | 'PARTITIONS' NUM ) )
| ( ( 'CHECK' | 'TRUNCATE' ) 'PARTITION' | ( 'OPTIMIZE' | 'REPAIR' | 'REBUILD' ) 'PARTITION' NoWriteToBinLogAliasOpt ) AllOrPartitionNameList
| 'COALESCE' 'PARTITION' NoWriteToBinLogAliasOpt NUM
| 'DROP' ( ColumnKeywordOpt IfExists ColumnName RestrictOrCascadeOpt | 'PRIMARY' 'KEY' | 'PARTITION' IfExists PartitionNameList | ( KeyOrIndex IfExists | 'CHECK' ) Identifier | 'FOREIGN' 'KEY' IfExists Symbol )
| 'EXCHANGE' 'PARTITION' Identifier 'WITH' 'TABLE' TableName WithValidationOpt
| ( 'IMPORT' | 'DISCARD' ) ( 'PARTITION' AllOrPartitionNameList )? 'TABLESPACE'
| 'REORGANIZE' 'PARTITION' NoWriteToBinLogAliasOpt ReorganizePartitionRuleOpt
| 'ORDER' 'BY' AlterOrderItem ( ',' AlterOrderItem )*
| ( 'DISABLE' | 'ENABLE' ) 'KEYS'
| ( 'MODIFY' ColumnKeywordOpt IfExists | 'CHANGE' ColumnKeywordOpt IfExists ColumnName ) ColumnDef ColumnPosition
| 'ALTER' ( ColumnKeywordOpt ColumnName ( 'SET' 'DEFAULT' ( SignedLiteral | '(' Expression ')' ) | 'DROP' 'DEFAULT' ) | 'CHECK' Identifier EnforcedOrNot | 'INDEX' Identifier IndexInvisible )
| 'RENAME' ( ( 'COLUMN' | KeyOrIndex ) Identifier 'TO' Identifier | ( 'TO' | '='? | 'AS' ) TableName )
| LockClause
| AlgorithmClause
| 'FORCE'
| ( 'WITH' | 'WITHOUT' ) 'VALIDATION'
| 'SECONDARY_LOAD'
| 'SECONDARY_UNLOAD'
| ( 'AUTO_INCREMENT' | 'AUTO_ID_CACHE' | 'AUTO_RANDOM_BASE' | 'SHARD_ROW_ID_BITS' ) EqOpt LengthNum
| ( 'CACHE' | 'NOCACHE' )
| (
'TTL' EqOpt TimeColumnName '+' 'INTERVAL' Expression TimeUnit (TTLEnable EqOpt ( 'ON' | 'OFF' ))?
| 'REMOVE' 'TTL'
| TTLEnable EqOpt ( 'ON' | 'OFF' )
| TTLJobInterval EqOpt stringLit
)
| PlacementPolicyOption
PlacementPolicyOption ::=
"PLACEMENT" "POLICY" EqOpt PolicyName
| "PLACEMENT" "POLICY" (EqOpt | "SET") "DEFAULT"
Examples
Create a table with some initial data:
CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, c1 INT NOT NULL);
INSERT INTO t1 (c1) VALUES (1),(2),(3),(4),(5);
Query OK, 0 rows affected (0.11 sec)
Query OK, 5 rows affected (0.03 sec)
Records: 5 Duplicates: 0 Warnings: 0
The following query requires a full table scan because the column c1 is not indexed:
EXPLAIN SELECT * FROM t1 WHERE c1 = 3;
+-------------------------+----------+-----------+---------------+--------------------------------+
| id | estRows | task | access object | operator info |
+-------------------------+----------+-----------+---------------+--------------------------------+
| TableReader_7 | 10.00 | root | | data:Selection_6 |
| └─Selection_6 | 10.00 | cop[tikv] | | eq(test.t1.c1, 3) |
| └─TableFullScan_5 | 10000.00 | cop[tikv] | table:t1 | keep order:false, stats:pseudo |
+-------------------------+----------+-----------+---------------+--------------------------------+
3 rows in set (0.00 sec)
The statement ALTER TABLE .. ADD INDEX can be used to add an index on the table t1. EXPLAIN confirms that the original query now uses an index range scan, which is more efficient:
ALTER TABLE t1 ADD INDEX (c1);
EXPLAIN SELECT * FROM t1 WHERE c1 = 3;
Query OK, 0 rows affected (0.30 sec)
+------------------------+---------+-----------+------------------------+---------------------------------------------+
| id | estRows | task | access object | operator info |
+------------------------+---------+-----------+------------------------+---------------------------------------------+
| IndexReader_6 | 10.00 | root | | index:IndexRangeScan_5 |
| └─IndexRangeScan_5 | 10.00 | cop[tikv] | table:t1, index:c1(c1) | range:[3,3], keep order:false, stats:pseudo |
+------------------------+---------+-----------+------------------------+---------------------------------------------+
2 rows in set (0.00 sec)
TiDB supports the ability to assert that DDL changes will use a particular ALTER algorithm. This is only an assertion, and does not change the actual algorithm which will be used to modify the table. It can be useful if you only want to permit instant DDL changes during the peak hours of your cluster:
ALTER TABLE t1 DROP INDEX c1, ALGORITHM=INSTANT;
Query OK, 0 rows affected (0.24 sec)
Using the ALGORITHM=INSTANT assertion on an operation that requires the INPLACE algorithm results in a statement error:
ALTER TABLE t1 ADD INDEX (c1), ALGORITHM=INSTANT;
ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported. Reason: Cannot alter table by INSTANT. Try ALGORITHM=INPLACE.
However, using the ALGORITHM=COPY assertion for an INPLACE operation generates a warning instead of an error. This is because TiDB interprets the assertion as this algorithm or better. This behavior difference is useful for MySQL compatibility because the algorithm TiDB uses might differ from MySQL:
ALTER TABLE t1 ADD INDEX (c1), ALGORITHM=COPY;
SHOW WARNINGS;
Query OK, 0 rows affected, 1 warning (0.25 sec)
+-------+------+---------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------------------------------------------------------------------+
| Error | 1846 | ALGORITHM=COPY is not supported. Reason: Cannot alter table by COPY. Try ALGORITHM=INPLACE. |
+-------+------+---------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
MySQL compatibility
The following major restrictions apply to ALTER TABLE in TiDB:
When altering multiple schema objects in a single
ALTER TABLEstatement:- Modifying the same object in multiple changes is not supported.
- TiDB validates statements according to the table schema before execution. For example, an error returns when
ALTER TABLE t ADD COLUMN c1 INT, ADD COLUMN c2 INT AFTER c1;is executed because columnc1does not exist in the table. - For an
ALTER TABLEstatement, the order of execution in TiDB is one change after another from left to right, which is incompatible with MySQL in some cases.
Changes of the Reorg-Data types on primary key columns are not supported.
Changes of column types on partitioned tables are not supported.
Changes of column types on generated columns are not supported.
Changes of some data types (for example, some TIME, Bit, Set, Enum, and JSON types) are not supported due to the compatibility issues of the
CASTfunction's behavior between TiDB and MySQL.Spatial data types are not supported.
ALTER TABLE t CACHE | NOCACHEis a TiDB extension to MySQL syntax. For details, see Cached Tables.
For further restrictions, see MySQL Compatibility.