When you specify UTF-8 for a column in MySQL, the engine defaults to UTF8MB3 — a 3-byte subset of Unicode that cannot represent emoji or other supplementary characters. Migrating to UTF8MB4 normally requires rebuilding the entire table, which blocks writes and can take minutes or longer on large datasets. PolarDB for MySQL lets you change a column's character set from UTF8MB3 to UTF8MB4 instantly, without rebuilding the table or blocking concurrent DML.
Prerequisites
Before you begin, ensure that your cluster meets one of the following version requirements:
PolarDB for MySQL 8.0.1, revision version 8.0.1.1.40 or later. To check your revision version, see the Query the engine version section of "Engine versions."
NoteOn 8.0.1 clusters, you must enable the
loose_innodb_support_instant_modify_charsetparameter before using this feature. See Change the character set for instructions.PolarDB for MySQL 8.0.2. The feature is enabled by default. No additional configuration is required.
Limitations
The instant character set change applies only when all of the following conditions are met.
Supported data types
The column must be one of these data types: CHAR, VARCHAR, ENUM, or TEXT.
Supported conversions
UTF8MB3 → UTF8MB4
Any character type → BINARY
No index on the column
The column must not have an index.
Storage length boundaries
Both of the following must hold:
The maximum storage length before and after the change must stay on the same side of the 256-byte boundary — both below 256 bytes, or both at 256 bytes or above.
The maximum storage length after the change must not be less than before the change.
Storage length is calculated as: column length × bytes per character. UTF8MB3 uses up to 3 bytes per character; UTF8MB4 uses up to 4 bytes per character; BINARY uses 1 byte per character.
Examples
| Column definition | Before change | After change | Instant? | Reason |
|---|---|---|---|---|
VARCHAR(85) UTF8MB3 → UTF8MB4 | 85 × 3 = 255 bytes | 85 × 4 = 340 bytes | No | Crosses the 256-byte boundary (255 → 340) |
CHAR(120) UTF8MB3 → BINARY | 120 × 3 = 360 bytes | 120 × 1 = 120 bytes | No | After-change length is less than before |
VARCHAR(60) UTF8MB3 → UTF8MB4 | 60 × 3 = 180 bytes | 60 × 4 = 240 bytes | Yes | Both below 256 bytes; after ≥ before |
Change the character set
Step 1: Enable the parameter (8.0.1 clusters only)
On PolarDB for MySQL 8.0.1, set the loose_innodb_support_instant_modify_charset parameter to ON before running any ALTER TABLE statement.
| Parameter | Level | Default | Description |
|---|---|---|---|
loose_innodb_support_instant_modify_charset | Global | OFF | Enables instant character set change. Set to ON to use this feature. |
For instructions on setting cluster parameters, see Configure cluster and node parameters.
On PolarDB for MySQL 8.0.2, skip this step.
Step 2: Run ALTER TABLE
Use ALGORITHM=INPLACE to explicitly trigger the instant change:
ALTER TABLE tablename MODIFY COLUMN test_column varchar(60) CHARACTER SET utf8mb4, ALGORITHM = INPLACE;To let PolarDB pick the fastest available algorithm automatically, omit the ALGORITHM clause or set it to DEFAULT:
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;When you use ALGORITHM=DEFAULT, PolarDB selects INPLACE if the instant change is possible, and falls back to COPY otherwise.
If INPLACE fails
If the column does not meet the limitations, the statement returns:
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.Check that loose_innodb_support_instant_modify_charset is set to ON (8.0.1 clusters) and that the column satisfies all conditions in Limitations.
Performance comparison
The following example compares execution time on two tables (t1 and t2) with identical structure, each containing 10 million rows. Both tables change the column character set from UTF8MB3 to UTF8MB4.
Without instant change — the engine rebuilds the entire table:
ALTER TABLE t1 MODIFY COLUMN c varchar(2) CHARACTER SET utf8mb4 NOT NULL DEFAULT '';Query OK, 10000000 rows affected (59.66 sec)
Records: 10000000 Duplicates: 0 Warnings: 0The operation takes about 1 minute and only query operations can be performed during the entire DDL process.
With instant change — only metadata is updated:
ALTER TABLE t2 MODIFY COLUMN c varchar(2) CHARACTER SET utf8mb4 NOT NULL DEFAULT '',ALGORITHM = INPLACE;Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0The operation completes in 0.03 seconds regardless of table size.
What's next
Contact us if you have questions about DDL operations
Contact us
If you have any questions about DDL operations, please contact technical support.