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

MODIFY COLUMN - 图1

ModifyColumnSpec

MODIFY COLUMN - 图2

ColumnType

MODIFY COLUMN - 图3

ColumnOption

MODIFY COLUMN - 图4

ColumnName

MODIFY COLUMN - 图5

  1. AlterTableStmt
  2.          ::= 'ALTER' 'IGNORE'? 'TABLE' TableName ModifyColumnSpec ( ',' ModifyColumnSpec )*
  4. ModifyColumnSpec
  5.          ::= 'MODIFY' ColumnKeywordOpt 'IF EXISTS' 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

Meta-Only Change

  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 MODIFY col1 BIGINT;
  1. Query OK, 0 rows affected (0.09 sec)
  1. SHOW CREATE TABLE t1\G
  1. *************************** 1. row ***************************
  2.        Table: t1
  3. Create Table: CREATE TABLE `t1` (
  4.   `id` int(11) NOT NULL AUTO_INCREMENT,
  5.   `col1` bigint(20) DEFAULT NULL,
  6.   PRIMARY KEY (`id`)
  7. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=30001
  8. 1 row in set (0.00 sec)

Reorg-Data Change

  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 (12345),(67890);
  1. Query OK, 2 rows affected (0.00 sec)
  2. Records: 2  Duplicates: 0  Warnings: 0
  1. ALTER TABLE t1 MODIFY col1 VARCHAR(5);
  1. Query OK, 0 rows affected (2.52 sec)
  1. SHOW CREATE TABLE t1\G
  1. *************************** 1. row ***************************
  2.        Table: t1
  3. CREATE TABLE `t1` (
  4.   `id` int(11) NOT NULL AUTO_INCREMENT,
  5.   `col1` varchar(5) DEFAULT NULL,
  6.   PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
  7. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=30001
  8. 1 row in set (0.00 sec)

MODIFY COLUMN - 图6

Note

  • TiDB returns an error when the changed data type conflicts with an existing data row. In the above example, TiDB returns the following error:

    1. alter table t1 modify column col1 varchar(4);
    2. ERROR 1406 (22001): Data Too Long, field len 4, data len 5

  • Due to the compatibility with the Async Commit feature, the DDL statement waits for a period of time (about 2.5s) before starting to process into Reorg Data.

    1. Query OK, 0 rows affected (2.52 sec)

MySQL compatibility

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

    1. CREATE TABLE t (a int primary key);
    2. ALTER TABLE t MODIFY COLUMN a VARCHAR(10);
    3. ERROR 8200 (HY000): Unsupported modify column: column has primary key flag
    1. CREATE TABLE t (a int primary key);
    2. ALTER TABLE t MODIFY COLUMN a INT(10) UNSIGNED;
    3. ERROR 8200 (HY000): Unsupported modify column: column has primary key flag
    1. CREATE TABLE t (a int primary key);
    2. ALTER TABLE t MODIFY COLUMN a bigint;
    3. Query OK, 0 rows affected (0.01 sec)

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

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

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

    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 MODIFY COLUMN c1 DATETIME;
    3. 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.

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

See also