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

ALTER TABLE - 图1

TableName

ALTER TABLE - 图2

AlterTableSpec

ALTER TABLE - 图3

PlacementPolicyOption

ALTER TABLE - 图4

  1. AlterTableStmt ::=
  2.     'ALTER' IgnoreOptional 'TABLE' TableName (
  3.         AlterTableSpecListOpt AlterTablePartitionOpt |
  4.         'ANALYZE' 'PARTITION' PartitionNameList ( 'INDEX' IndexNameList )? AnalyzeOptionListOpt |
  5.         'COMPACT' ( 'PARTITION' PartitionNameList )? 'TIFLASH' 'REPLICA'
  6.     )
  8. TableName ::=
  9.     Identifier ('.' Identifier)?
  11. AlterTableSpec ::=
  12.     TableOptionList
  13. |   'SET' 'TIFLASH' 'REPLICA' LengthNum LocationLabelList
  14. |   'CONVERT' 'TO' CharsetKw ( CharsetName | 'DEFAULT' ) OptCollate
  15. |   'ADD' ( ColumnKeywordOpt IfNotExists ( ColumnDef ColumnPosition | '(' TableElementList ')' ) | Constraint | 'PARTITION' IfNotExists NoWriteToBinLogAliasOpt ( PartitionDefinitionListOpt | 'PARTITIONS' NUM ) )
  16. |   ( ( 'CHECK' | 'TRUNCATE' ) 'PARTITION' | ( 'OPTIMIZE' | 'REPAIR' | 'REBUILD' ) 'PARTITION' NoWriteToBinLogAliasOpt ) AllOrPartitionNameList
  17. |   'COALESCE' 'PARTITION' NoWriteToBinLogAliasOpt NUM
  18. |   'DROP' ( ColumnKeywordOpt IfExists ColumnName RestrictOrCascadeOpt | 'PRIMARY' 'KEY' |  'PARTITION' IfExists PartitionNameList | ( KeyOrIndex IfExists | 'CHECK' ) Identifier | 'FOREIGN' 'KEY' IfExists Symbol )
  19. |   'EXCHANGE' 'PARTITION' Identifier 'WITH' 'TABLE' TableName WithValidationOpt
  20. |   ( 'IMPORT' | 'DISCARD' ) ( 'PARTITION' AllOrPartitionNameList )? 'TABLESPACE'
  21. |   'REORGANIZE' 'PARTITION' NoWriteToBinLogAliasOpt ReorganizePartitionRuleOpt
  22. |   'ORDER' 'BY' AlterOrderItem ( ',' AlterOrderItem )*
  23. |   ( 'DISABLE' | 'ENABLE' ) 'KEYS'
  24. |   ( 'MODIFY' ColumnKeywordOpt IfExists | 'CHANGE' ColumnKeywordOpt IfExists ColumnName ) ColumnDef ColumnPosition
  25. |   'ALTER' ( ColumnKeywordOpt ColumnName ( 'SET' 'DEFAULT' ( SignedLiteral | '(' Expression ')' ) | 'DROP' 'DEFAULT' ) | 'CHECK' Identifier EnforcedOrNot | 'INDEX' Identifier ("VISIBLE" | "INVISIBLE") )
  26. |   'RENAME' ( ( 'COLUMN' | KeyOrIndex ) Identifier 'TO' Identifier | ( 'TO' | '='? | 'AS' ) TableName )
  27. |   LockClause
  28. |   AlgorithmClause
  29. |   'FORCE'
  30. |   ( 'WITH' | 'WITHOUT' ) 'VALIDATION'
  31. |   'SECONDARY_LOAD'
  32. |   'SECONDARY_UNLOAD'
  33. |   ( 'AUTO_INCREMENT' | 'AUTO_ID_CACHE' | 'AUTO_RANDOM_BASE' | 'SHARD_ROW_ID_BITS' ) EqOpt LengthNum
  34. |   ( 'CACHE' | 'NOCACHE' )
  35. |   (
  36.         'TTL' EqOpt TimeColumnName '+' 'INTERVAL' Expression TimeUnit (TTLEnable EqOpt ( 'ON' | 'OFF' ))?
  37.         | 'REMOVE' 'TTL'
  38.         | TTLEnable EqOpt ( 'ON' | 'OFF' )
  39.         | TTLJobInterval EqOpt stringLit
  40.     )
  41. |   PlacementPolicyOption
  43. PlacementPolicyOption ::=
  44.     "PLACEMENT" "POLICY" EqOpt PolicyName
  45. |   "PLACEMENT" "POLICY" (EqOpt | "SET") "DEFAULT"

Examples

Create a table with some initial data:

  1. CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, c1 INT NOT NULL);
  2. INSERT INTO t1 (c1) VALUES (1),(2),(3),(4),(5);
  1. Query OK, 0 rows affected (0.11 sec)
  3. Query OK, 5 rows affected (0.03 sec)
  4. Records: 5  Duplicates: 0  Warnings: 0

The following query requires a full table scan because the column c1 is not indexed:

  1. EXPLAIN SELECT * FROM t1 WHERE c1 = 3;
  1. +-------------------------+----------+-----------+---------------+--------------------------------+
  2. | id                      | estRows  | task      | access object | operator info                  |
  3. +-------------------------+----------+-----------+---------------+--------------------------------+
  4. | TableReader_7           | 10.00    | root      |               | data:Selection_6               |
  5. | └─Selection_6           | 10.00    | cop[tikv] |               | eq(test.t1.c1, 3)              |
  6. |   └─TableFullScan_5     | 10000.00 | cop[tikv] | table:t1      | keep order:false, stats:pseudo |
  7. +-------------------------+----------+-----------+---------------+--------------------------------+
  8. 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:

  1. ALTER TABLE t1 ADD INDEX (c1);
  2. EXPLAIN SELECT * FROM t1 WHERE c1 = 3;
  1. Query OK, 0 rows affected (0.30 sec)
  3. +------------------------+---------+-----------+------------------------+---------------------------------------------+
  4. | id                     | estRows | task      | access object          | operator info                               |
  5. +------------------------+---------+-----------+------------------------+---------------------------------------------+
  6. | IndexReader_6          | 10.00   | root      |                        | index:IndexRangeScan_5                      |
  7. | └─IndexRangeScan_5     | 10.00   | cop[tikv] | table:t1, index:c1(c1) | range:[3,3], keep order:false, stats:pseudo |
  8. +------------------------+---------+-----------+------------------------+---------------------------------------------+
  9. 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:

  1. ALTER TABLE t1 DROP INDEX c1, ALGORITHM=INSTANT;
  1. 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:

  1. ALTER TABLE t1 ADD INDEX (c1), ALGORITHM=INSTANT;
  1. 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:

  1. ALTER TABLE t1 ADD INDEX (c1), ALGORITHM=COPY;
  2. SHOW WARNINGS;
  1. Query OK, 0 rows affected, 1 warning (0.25 sec)
  3. +-------+------+---------------------------------------------------------------------------------------------+
  4. | Level | Code | Message                                                                                     |
  5. +-------+------+---------------------------------------------------------------------------------------------+
  6. | Error | 1846 | ALGORITHM=COPY is not supported. Reason: Cannot alter table by COPY. Try ALGORITHM=INPLACE. |
  7. +-------+------+---------------------------------------------------------------------------------------------+
  8. 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 TABLE statement:

    • 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 column c1 does not exist in the table.
    • For an ALTER TABLE statement, 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 CAST function’s behavior between TiDB and MySQL.

  • Spatial data types are not supported.

  • ALTER TABLE t CACHE | NOCACHE is a TiDB extension to MySQL syntax. For details, see Cached Tables.

For further restrictions, see MySQL Compatibility.

See also