Understanding MySQL Online DDL for VARCHAR Column Size Changes and In‑Place vs Copy Operations

MySQL introduced Online DDL in version 5.6 and significantly improved it in 5.7 and 8.0, allowing many DDL operations to be performed without blocking reads and writes. This article focuses on a special case: expanding VARCHAR columns and the conditions under which the change can be applied in‑place (seconds) or requires a full table copy.

In everyday work the most common data types are int / bigint and varchar . While int(N) does not affect storage size, varchar(N) does: when the stored byte length exceeds the defined limit, the column must be expanded. The author uses sysbench to create four tables with 5 million rows each on MySQL 5.7.29 and runs a 4‑thread workload to simulate production traffic.

First, the c1 column is expanded from varchar(10) to varchar(50) . The QPS remains stable, confirming an in‑place change. However, expanding the same column further to varchar(100) causes a noticeable pause and the workload stalls, indicating a table copy.

The MySQL documentation states that in‑place ALTER TABLE supports increasing VARCHAR size only when the number of length bytes does not change. VARCHAR columns up to 255 bytes use a 1‑byte length prefix; columns 256 bytes and larger use a 2‑byte prefix. Changing a column from a size that requires 1 byte to one that requires 2 bytes forces a COPY operation.

Because the length prefix is based on the number of **bytes**, not characters, the actual threshold depends on the character set. In UTF‑8 (3 bytes per character), the threshold is 85 characters (1‑byte prefix) and 86 characters (2‑byte prefix). Experiments show that expanding a UTF‑8 column from 85 to 86 characters triggers a full table copy, while further expansions do not.

Similar tests with UTF‑8MB4 (4 bytes per character) show the threshold at 63/64 characters, and with latin1 (1 byte per character) at 255/256 characters. The results are illustrated with screenshots of sysbench logs.

Key takeaways:

VARCHAR stores a 1‑ or 2‑byte length prefix; changes that alter the required prefix size cannot be done in‑place.

In‑place ALTER works when the byte length stays within the same prefix range; crossing the 255‑byte boundary forces a COPY.

The N in VARCHAR(N) represents characters, so the byte threshold varies with the column's character set.

To avoid unexpected blocking, teams should establish development guidelines that consider these limits, test on the specific MySQL version (note that versions before 5.7.20 may behave differently), and be cautious when shrinking VARCHAR columns or changing data types, as these always require a COPY.

root@localhost[sbtest]> show create table sbtest1\G
*************************** 1. row ***************************
       Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  `c1` varchar(10) NOT NULL DEFAULT 'cc',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=5000001 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)