MODIFY COLUMN

The ALTER TABLE.. MODIFY COLUMN statement modifies a column on an existing table. The modification can include changing the data type and attributes. To rename at the same time, use the CHANGE COLUMN statement instead.

Since v5.1.0, TiDB has supported changes of data types for Reorg data, including but not limited to:

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

Synopsis

AlterTableStmt
         ::= 'ALTER' 'IGNORE'? 'TABLE' TableName ModifyColumnSpec ( ',' ModifyColumnSpec )*

ModifyColumnSpec
         ::= 'MODIFY' ColumnKeywordOpt 'IF EXISTS' 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

Meta-Only Change

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 MODIFY col1 BIGINT;

Query OK, 0 rows affected (0.09 sec)

SHOW CREATE TABLE t1\G;

*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `col1` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=30001
1 row in set (0.00 sec)

Reorg-Data Change

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 (12345),(67890);

Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

ALTER TABLE t1 MODIFY col1 VARCHAR(5);

Query OK, 0 rows affected (2.52 sec)

SHOW CREATE TABLE t1\G;

*************************** 1. row ***************************
       Table: t1
CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `col1` varchar(5) DEFAULT NULL,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=30001
1 row in set (0.00 sec)

MySQL compatibility

• Does not support modifying multiple columns using a single ALTER TABLE statement. For example:

  ALTER TABLE t1 MODIFY col1 BIGINT, MODIFY id BIGINT NOT NULL;
  ERROR 1105 (HY000): Unsupported multi schema change
  

• Does not support modifying the Reorg-Data types on the primary key columns but supports modifying the Meta-Only types. For example:

  CREATE TABLE t (a int primary key);
  ALTER TABLE t MODIFY COLUMN a VARCHAR(10);
  ERROR 8200 (HY000): Unsupported modify column: column has primary key flag
  

  CREATE TABLE t (a int primary key);
  ALTER TABLE t MODIFY COLUMN a INT(10) UNSIGNED;
  ERROR 8200 (HY000): Unsupported modify column: column has primary key flag
  

  CREATE TABLE t (a int primary key);
  ALTER TABLE t MODIFY COLUMN a bigint;
  Query OK, 0 rows affected (0.01 sec)
  

• Does not support modifying the column types on generated columns. For example:

  CREATE TABLE t (a INT, b INT as (a+1));
  ALTER TABLE t MODIFY COLUMN b VARCHAR(10);
  ERROR 8200 (HY000): Unsupported modify column: column is generated
  

• Does not support modifying the column types on the partitioned tables. For example:

  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 MODIFY COLUMN c1 DATETIME;
  ERROR 8200 (HY000): Unsupported modify column: table is partition table
  

• Does not support modifying some data types (for example, some TIME types, Bit, Set, Enum, JSON) are not supported due to some compatibility issues of the cast function's behavior between TiDB and MySQL.

  CREATE TABLE t (a DECIMAL(13, 7));
  ALTER TABLE t MODIFY COLUMN a DATETIME;
  ERROR 8200 (HY000): Unsupported modify column: change from original type decimal(13,7) to datetime is currently unsupported yet
  

See also

• CREATE TABLE
• SHOW CREATE TABLE
• ADD COLUMN
• DROP COLUMN
• CHANGE COLUMN