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
toBIGINT
- Modifying the
DECIMAL
precision - Compressing the length of
VARCHAR(10)
toVARCHAR(5)
Synopsis
- AlterTableStmt
- ModifyColumnSpec
- ColumnType
- ColumnOption
- ColumnName
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 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 flagCREATE 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 flagCREATE 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 generatedDoes 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 tableDoes 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