Modify column types in seconds - ApsaraDB RDS: Central de Documentação do Alibaba Cloud

AliSQL extends community MySQL 8.0 to support instant column type modification. Instead of copying all table data, this operation modifies only metadata — completing in seconds regardless of table size.

Community MySQL 8.0 uses INSTANT DDL for adding and dropping columns, which avoids data copies. For column type modifications, however, it falls back to COPY DDL, which rewrites the entire table. AliSQL extends the INSTANT DDL framework to cover column type changes, applying the same metadata-only approach.

Prerequisites

Before you begin, ensure that you have:

An ApsaraDB RDS for MySQL instance running MySQL 8.0 with minor engine version 20251031 or later

If your instance does not meet this requirement, upgrade first:

Limitations

Constraint	Details
Storage engine	InnoDB only
Index columns	Modifying index columns is not supported
Partition key columns	Modifying columns included in a partition key is not supported
Supported integer changes	Widening only: `TINYINT` → `SMALLINT` → `MEDIUMINT` → `INT` → `BIGINT`. For example, `INT` to `BIGINT` is supported; `BIGINT` to `INT` is not
Supported string changes	Increasing the length of `CHAR` and `VARCHAR` columns only. For `CHAR(M)` to `CHAR(N)`, N must be greater than M

Parameters

Three parameters control this feature. All three must be set to ON for instant column type modification to work.

Parameter	Description	Scope	Default
`loose_rds_upgrade_datatype_instant_enable`	Enables instant column type modification	Global	`OFF`
`loose_rds_upgrade_datatype_online_enable`	Enables online column type modification	Global	`OFF`
`loose_innodb_instant_ddl_enabled`	Master switch for INSTANT DDL	Global	`ON`

All three parameters are Boolean, accept ON or OFF, and do not require an instance restart.

Enable the parameters

Go to the Instances page. In the top navigation bar, select the region where your RDS instance resides. Find the instance and click its ID.
In the left navigation pane, click Parameter Settings.
On the Modifiable Parameters tab, search for each parameter and set its value to ON.
Click OK, then click Submit Parameters. In the window that appears, select a time range for the change to take effect.

Modify column types instantly

The examples below use the following table:

CREATE TABLE `t1` (
  `id` int NOT NULL,
  `c1` int DEFAULT NULL,
  `c2` char(10) DEFAULT NULL,
  `c3` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

All examples use ALGORITHM=INSTANT to run the modification as a metadata-only operation.

Modify a column type

Modify c1 from int to bigint:

ALTER TABLE `t1` MODIFY `c1` bigint DEFAULT NULL, ALGORITHM=INSTANT;

Extend a string length

Extend the length of c2 (CHAR) and c3 (VARCHAR):

ALTER TABLE `t1` MODIFY `c2` char(20) DEFAULT NULL, ALGORITHM=INSTANT;
ALTER TABLE `t1` MODIFY `c3` varchar(100) DEFAULT NULL, ALGORITHM=INSTANT;

Let the system choose the algorithm

Omit ALGORITHM= and the system automatically selects the most appropriate algorithm to modify the column type:

ALTER TABLE `t1` MODIFY `c1` bigint DEFAULT NULL;

Note

Specifying ALGORITHM=INPLACE runs the modification online (using the in-place method), not instantly.

Verify the modification

The current version does not support separately viewing instantly modified columns. You can query table column information using the following SQL. If the HAS_DEFAULT column value is 1 in the query result, the column was added by the instant add column feature or was instantly modified.

SELECT * FROM INFORMATION_SCHEMA.INNODB_COLUMNS WHERE TABLE_ID = 
(SELECT TABLE_ID FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME = "<database_name>/<table_name>");

Example output:

+----------+------+-----+-------+---------+-----+-------------+------------------------------------------------------------------------------------+
| TABLE_ID | NAME | POS | MTYPE | PRTYPE  | LEN | HAS_DEFAULT | DEFAULT_VALUE                                                                      |
+----------+------+-----+-------+---------+-----+-------------+------------------------------------------------------------------------------------+
|     1976 | id   |   0 |     6 |    1283 |   4 |           0 | NULL                                                                               |
|     1976 | c1   |   1 |     6 |    1032 |   8 |           1 | 0x38303030303030303030303030303030                                                 |
|     1976 | c2   |   2 |    13 | 2162942 |  60 |           1 | 0x32303230323032303230323032303230323032303230323032303230323032303230323032303230 |
|     1976 | c3   |   3 |    12 | 2166799 | 300 |           1 | 0x                                                                                 |
+----------+------+-----+-------+---------+-----+-------------+------------------------------------------------------------------------------------+