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
toBIGINT
- Modifying the
DECIMAL
precision - Compressing the length of
VARCHAR(10)
toVARCHAR(5)
Synopsis
- AlterTableStmt
- ChangeColumnSpec
- ColumnType
- ColumnOption
- ColumnName
AlterTableStmt
::= 'ALTER' 'IGNORE'? 'TABLE' TableName ChangeColumnSpec ( ',' ChangeColumnSpec )*
ChangeColumnSpec
::= 'CHANGE' ColumnKeywordOpt 'IF EXISTS' ColumnName ColumnName ColumnType ColumnOption* ( 'FIRST' | 'AFTER' ColumnName )?
ColumnType
::= NumericType
| StringType
| DateAndTimeType
| 'SERIAL'
ColumnOption
::= 'NOT'? 'NULL'
| 'AUTO_INCREMENT'
| 'PRIMARY'? 'KEY' ( 'CLUSTERED' | 'NONCLUSTERED' )?
| 'UNIQUE' 'KEY'?
| 'DEFAULT' ( NowSymOptionFraction | SignedLiteral | NextValueForSequence )
| 'SERIAL' 'DEFAULT' 'VALUE'
| 'ON' 'UPDATE' NowSymOptionFraction
| 'COMMENT' stringLit
| ( 'CONSTRAINT' Identifier? )? 'CHECK' '(' Expression ')' ( 'NOT'? ( 'ENFORCED' | 'NULL' ) )?
| 'GENERATED' 'ALWAYS' 'AS' '(' Expression ')' ( 'VIRTUAL' | 'STORED' )?
| 'REFERENCES' TableName ( '(' IndexPartSpecificationList ')' )? Match? OnDeleteUpdateOpt
| 'COLLATE' CollationName
| 'COLUMN_FORMAT' ColumnFormat
| 'STORAGE' StorageMedia
| 'AUTO_RANDOM' ( '(' LengthNum ')' )?
ColumnName ::=
Identifier ( '.' Identifier ( '.' Identifier )? )?
Examples
CREATE TABLE t1 (id int not null primary key AUTO_INCREMENT, col1 INT);
Query OK, 0 rows affected (0.11 sec)
INSERT INTO t1 (col1) VALUES (1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.02 sec)
Records: 5 Duplicates: 0 Warnings: 0
ALTER TABLE t1 CHANGE col1 col2 INT;
Query OK, 0 rows affected (0.09 sec)
ALTER TABLE t1 CHANGE col2 col3 BIGINT, ALGORITHM=INSTANT;
Query OK, 0 rows affected (0.08 sec)
ALTER TABLE t1 CHANGE col3 col4 BIGINT, CHANGE id id2 INT NOT NULL;
ERROR 1105 (HY000): can't run multi schema change
CREATE TABLE t (a int primary key);
ALTER TABLE t CHANGE COLUMN a a VARCHAR(10);
ERROR 8200 (HY000): Unsupported modify column: column has primary key flag
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));
ALTER TABLE t CHANGE COLUMN c1 c1 DATETIME;
ERROR 8200 (HY000): Unsupported modify column: table is partition table
CREATE TABLE t (a INT, b INT as (a+1));
ALTER TABLE t CHANGE COLUMN b b VARCHAR(10);
ERROR 8200 (HY000): Unsupported modify column: column is generated
CREATE TABLE t (a DECIMAL(13, 7));
ALTER TABLE t CHANGE COLUMN a a DATETIME;
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 from some data types (for example, TIME, BIT, SET, ENUM, and JSON types) to some other types are not supported due to the compatibility issues of the
CAST
function's behavior between TiDB and MySQL.