Instantly change a column character set - ApsaraDB RDS - Alibaba Cloud Documentation Center

AliSQL lets you instantly change a column character set. This feature modifies only the metadata instead of rebuilding the table, which greatly reduces the time required for the change and improves operational and maintenance (O&M) efficiency.

Feature description

Background: In community MySQL, the default implementation of the UTF-8 character set is utf8mb3. The utf8mb3 character set uses a maximum of three bytes to store a single character. This causes information loss when you need to store 4-byte information, such as emoji. Starting from version 8.0, the community edition changed the default character set to utf8mb4. However, changing the character set for columns in existing tables usually requires you to rebuild the table, which is a time-consuming process.

Introduction: AliSQL supports the Instant Modify Column Charset feature. Instead of rebuilding the table by creating a temporary table and copying data, this feature only modifies the metadata to instantly change the character set.

Algorithm: For the Instant Modify Column Charset feature, MySQL 5.7 supports only the INPLACE algorithm. MySQL 8.0 supports both the INPLACE and INSTANT algorithms. In practice, both algorithms modify only the metadata.

Applicability

To use the feature to instantly change a column character set, your instance must run one of the following database versions. If your minor engine version does not meet the requirements, you can upgrade the minor engine version.

MySQL 8.0: Minor engine version 20230630 or later.
MySQL 5.7: Minor engine version 20251031 or later.

Note

For MySQL 8.0, minor engine versions of 20251031 or later support both the INPLACE and INSTANT algorithms. Earlier minor engine versions support only the INPLACE algorithm.

The feature to instantly change a column character set has the following limitations:

Column type limit: Character set conversion is supported only for columns of the CHAR and VARCHAR data types.
Character set limit: Only conversions from utf8/utf8mb3 to utf8mb4 are supported.
Engine limit: Only the InnoDB engine is supported.
Index limit: You cannot modify index columns.
Partition key limit: You cannot modify columns that are part of a partition key.
Column length limit: The maximum storage length of the column cannot cross the 256-byte boundary during the character set change. This means that before and after the change, the length must either remain less than 256 bytes or remain greater than or equal to 256 bytes. For example, consider the conversion from utf8/utf8mb3 to utf8mb4:
- For the utf8/utf8mb3 character set, each character uses 3 bytes of storage. The 256-byte critical value corresponds to 85 characters.
- For the utf8mb4 character set, each character uses 4 bytes of storage. The 256-byte critical value corresponds to 63 characters.
Therefore, the feature to instantly change a column character set is supported only when the defined column length is within the range of (0, 63] characters or (85, 65,535] characters.

Parameter management

Parameter description

You can modify the following parameters for MySQL 8.0 instances only to specify the INSTANT algorithm:

Parameter name	Description
`loose_rds_upgrade_datatype_instant_enable`	Description: The switch for the feature to instantly change a column data type. Scope: Global parameter. Data type: `Boolean`. Default value: `OFF`. Valid values: `ON` or `OFF`. Restart required: No.
`loose_innodb_instant_ddl_enabled`	Description: The master switch for Instant DDL. Scope: Global parameter. Data type: `Boolean`. Default value: `ON`. Valid values: `ON` or `OFF`. Restart required: No.

Modify parameters

Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.
In the left navigation pane, click Parameter Settings.
On the Editable Parameters tab, search for the parameter to modify and set its value.
Click OK. Then, click Submit Parameters. In the dialog box that appears, select a time range for the change to take effect.

Usage

Assume that you want to modify a data table named t1. The table schema is as follows:

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

Specify an algorithm to change the column character set

You can specify the INPLACE or INSTANT algorithm when you change the column character set.

Specify the INPLACE algorithm

-- Specify the INPLACE algorithm to change the character set of columns c1 and c2 to utf8mb4
ALTER TABLE `t1` MODIFY `c1` char(10) CHARACTER SET utf8mb4 DEFAULT NULL, ALGORITHM=INPLACE;
ALTER TABLE `t1` MODIFY `c2` varchar(10) CHARACTER SET utf8mb4 DEFAULT NULL, ALGORITHM=INPLACE;

-- View the modification result
SHOW CREATE TABLE `t1`\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `c1` char(10) CHARACTER SET utf8mb4 DEFAULT NULL,
  `c2` varchar(10) CHARACTER SET utf8mb4 DEFAULT NULL,
  `c3` char(100) DEFAULT NULL,
  `c4` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

Specify the INSTANT algorithm

-- Specify the INSTANT algorithm to change the character set of columns c3 and c4 to utf8mb4
ALTER TABLE `t1` MODIFY `c3` char(100) CHARACTER SET utf8mb4 DEFAULT NULL, ALGORITHM=INSTANT;
ALTER TABLE `t1` MODIFY `c4` varchar(100) CHARACTER SET utf8mb4 DEFAULT NULL, ALGORITHM=INSTANT;

-- View the modification result
SHOW CREATE TABLE `t1`\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `c1` char(10) CHARACTER DEFAULT NULL,
  `c2` varchar(10) CHARACTER DEFAULT NULL,
  `c3` char(100) CHARACTER SET utf8mb4 DEFAULT NULL,
  `c4` varchar(100) CHARACTER SET utf8mb4 DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

Change the column character set without specifying an algorithm

If you do not specify an algorithm when you change a column character set, MySQL automatically selects the most appropriate algorithm.

-- Change the character set of column c1 to utf8mb4 without specifying an algorithm
ALTER TABLE `t1` MODIFY `c1` char(10) CHARACTER SET utf8mb4 DEFAULT NULL;