CHANGE COLUMN

The ALTER TABLE.. CHANGE COLUMN statement changes a column on an existing table. The change can include both renaming the column, and changing the data type to a compatible type.

Since v5.1.0, TiDB has supported changing the Reorg data type, including but not limited to:

  • Changing VARCHAR to BIGINT
  • Modifying the DECIMAL precision
  • Compressing the length of VARCHAR(10) to VARCHAR(5)

Synopsis

AlterTableStmt

CHANGE COLUMN - 图1

ChangeColumnSpec

CHANGE COLUMN - 图2

ColumnType

CHANGE COLUMN - 图3

ColumnOption

CHANGE COLUMN - 图4

ColumnName

CHANGE COLUMN - 图5

  1. AlterTableStmt
  2.          ::= 'ALTER' 'IGNORE'? 'TABLE' TableName ChangeColumnSpec ( ',' ChangeColumnSpec )*
  4. ChangeColumnSpec
  5.          ::= 'CHANGE' ColumnKeywordOpt 'IF EXISTS' ColumnName ColumnName ColumnType ColumnOption* ( 'FIRST' | 'AFTER' ColumnName )?
  7. ColumnType
  8.          ::= NumericType
  9.            | StringType
  10.            | DateAndTimeType
  11.            | 'SERIAL'
  13. ColumnOption
  14.          ::= 'NOT'? 'NULL'
  15.            | 'AUTO_INCREMENT'
  16.            | 'PRIMARY'? 'KEY' ( 'CLUSTERED' | 'NONCLUSTERED' )?
  17.            | 'UNIQUE' 'KEY'?
  18.            | 'DEFAULT' ( NowSymOptionFraction | SignedLiteral | NextValueForSequence )
  19.            | 'SERIAL' 'DEFAULT' 'VALUE'
  20.            | 'ON' 'UPDATE' NowSymOptionFraction
  21.            | 'COMMENT' stringLit
  22.            | ( 'CONSTRAINT' Identifier? )? 'CHECK' '(' Expression ')' ( 'NOT'? ( 'ENFORCED' | 'NULL' ) )?
  23.            | 'GENERATED' 'ALWAYS' 'AS' '(' Expression ')' ( 'VIRTUAL' | 'STORED' )?
  24.            | 'REFERENCES' TableName ( '(' IndexPartSpecificationList ')' )? Match? OnDeleteUpdateOpt
  25.            | 'COLLATE' CollationName
  26.            | 'COLUMN_FORMAT' ColumnFormat
  27.            | 'STORAGE' StorageMedia
  28.            | 'AUTO_RANDOM' ( '(' LengthNum ')' )?
  30. ColumnName ::=
  31.     Identifier ( '.' Identifier ( '.' Identifier )? )?

Examples

  1. CREATE TABLE t1 (id int not null primary key AUTO_INCREMENT, col1 INT);
  1. Query OK, 0 rows affected (0.11 sec)
  1. INSERT INTO t1 (col1) VALUES (1),(2),(3),(4),(5);
  1. Query OK, 5 rows affected (0.02 sec)
  2. Records: 5  Duplicates: 0  Warnings: 0
  1. ALTER TABLE t1 CHANGE col1 col2 INT;
  1. Query OK, 0 rows affected (0.09 sec)
  1. ALTER TABLE t1 CHANGE col2 col3 BIGINT, ALGORITHM=INSTANT;
  1. Query OK, 0 rows affected (0.08 sec)
  1. ALTER TABLE t1 CHANGE col3 col4 BIGINT, CHANGE id id2 INT NOT NULL;
  1. ERROR 1105 (HY000): can't run multi schema change
  1. CREATE TABLE t (a int primary key);
  2. ALTER TABLE t CHANGE COLUMN a a VARCHAR(10);
  1. ERROR 8200 (HY000): Unsupported modify column: column has primary key flag
  1. CREATE TABLE t (c1 INT, c2 INT, c3 INT) partition by range columns(c1) ( partition p0 values less than (10), partition p1 values less than (maxvalue));
  2. ALTER TABLE t CHANGE COLUMN c1 c1 DATETIME;
  1. ERROR 8200 (HY000): Unsupported modify column: table is partition table
  1. CREATE TABLE t (a INT, b INT as (a+1));
  2. ALTER TABLE t CHANGE COLUMN b b VARCHAR(10);
  1. ERROR 8200 (HY000): Unsupported modify column: column is generated
  1. CREATE TABLE t (a DECIMAL(13, 7));
  2. ALTER TABLE t CHANGE COLUMN a a DATETIME;
  1. ERROR 8200 (HY000): Unsupported modify column: change from original type decimal(13,7) to datetime is currently unsupported yet

MySQL compatibility

  • Changes of 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.

See also