PolarDB:Instantly change a column character set

Prerequisites

Your cluster must be one of the following versions:

• PolarDB for MySQL 8.0.1 with a revision version of 8.0.1.1.40 or later. For more information, see Query the version number.

  Note

  On PolarDB for MySQL 8.0.1 clusters, you must set the loose_innodb_support_instant_modify_charset parameter to use this feature.

• PolarDB for MySQL 8.0.2.

  Note

  PolarDB for MySQL 8.0.2 clusters support the instant column character set change feature by default. You do not need to configure any parameters.

Limits

• You can only change the character set for columns of the CHAR, VARCHAR, ENUM, and TEXT types.

• You can only change a column character set from utf8mb3 to utf8mb4, or change any character set to the BINARY type.

• No indexes can exist on the column whose character set you want to change.

• Before and after the character set change, the column length must meet the following requirements:

  • The maximum storage length of the column must be either less than 256 bytes both before and after the change, or greater than 255 bytes both before and after the change. The storage length is the actual storage space consumed by the character set.

    For example, consider a VARCHAR(85) column that uses the `utf8mb3` character set. Because `utf8mb3` uses a maximum of 3 bytes to store a character, the maximum storage length of this column is 85 × 3 = 255 bytes. If you change the character set of this column to `utf8mb4`, its maximum storage length becomes 85 × 4 = 340 bytes. Because the new maximum storage length is greater than 255 bytes, the change crosses the boundary. Therefore, the character set cannot be changed instantly.

  • The maximum storage length of the column with the target character set cannot be less than its maximum storage length with the original character set.

    For example, consider a CHAR(120) column that uses the `utf8mb3` character set. The maximum storage length of the column is 120 × 3 = 360 bytes. After you change the character set to `BINARY`, the maximum storage length of the column becomes 120 × 1 = 120 bytes. Therefore, this column does not meet the requirement for an instant character set change.

Procedure

1. Configure the parameter.

   For PolarDB for MySQL 8.0.1, set the loose_innodb_support_instant_modify_charset parameter to ON to enable the instant column character set change feature. For more information about how to set parameter values, see Set cluster and node parameters.

   Parameter Name	Parameter level	Description
   loose_innodb_support_instant_modify_charset	Global	Controls the instant column character set change feature. Valid values: OFF (Default): Disables the instant column character set change feature. ON: Enables the instant column character set change feature.

2. Change the column character set.

   • Specify ALGORITHM=INPLACE to force the use of the instant change feature. The following example shows how:

     ALTER TABLE tablename MODIFY COLUMN test_column varchar(60) CHARACTER SET utf8mb4, ALGORITHM = INPLACE;

     If the preceding statement returns the following error, the operation cannot be performed with the INPLACE algorithm. Ensure that the loose_innodb_support_instant_modify_charset parameter is set to ON and that all the conditions described in the Limits section are met.

     ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

   • If you do not specify ALGORITHM or if you specify ALGORITHM=DEFAULT, PolarDB automatically selects the fastest algorithm to perform the operation. The following examples show how:

     ALTER TABLE tablename MODIFY COLUMN test_column varchar(60) CHARACTER SET utf8mb4, ALGORITHM = DEFAULT;
     ALTER TABLE tablename MODIFY COLUMN test_column varchar(60) CHARACTER SET utf8mb4;

Results

This section uses tables t1 and t2 as an example. Both tables contain 10 million records and have identical table schemas. This section compares the performance of changing a column's character set from utf8mb3 to utf8mb4 with the instant change feature disabled and enabled.

• When the instant change feature is disabled, changing the column character set requires rebuilding the table. The running time is about 1 minute. During the entire Data Definition Language (DDL) operation, only query operations are allowed.

  ALTER TABLE t1 MODIFY COLUMN c varchar(2)  CHARACTER SET utf8mb4 NOT NULL DEFAULT '';

  The following result is returned:

  Query OK, 10000000 rows affected (59.66 sec)
  Records: 10000000  Duplicates: 0  Warnings: 0

• When the instant change feature is enabled, the operation completes in seconds, regardless of the table's data volume.

  ALTER TABLE t2 MODIFY COLUMN c varchar(2)  CHARACTER SET utf8mb4 NOT NULL DEFAULT '',ALGORITHM = INPLACE;

  The following result is returned:

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

Contact us

If you have any questions about DDL operations, Contact us.