Disclaimer: this post takes into consideration that strict mode is enabled on the server
VARCHAR
and CHAR
are used to store strings. VARCHAR
stores varying length and CHAR
always use the same exact size no matter the size of the string. For example, CHAR(4) will always store 4 bytes, whereas VARCHAR(4) will store up to 5 bytes. See documentation.
When we create a table like this one:
We put inside the parentheses the length of the field in characters for the VARCHAR
field. However, the maximum size in bytes of the field will depend on the CHARSET
and COLLATION
of the table. You can also specify a different collation for a column.
For instance:
- latin1: 1 to 2 bytes per character.
- utf8: 1 to 4 bytes per character.
Why this is important to know
The new Online DDL changes for VARCHAR
fields are documented as follows:
The number of length bytes required by a
VARCHAR
column must remain the same. ForVARCHAR
values of 0 to 255, one length byte is required to encode the value. ForVARCHAR
values of 256 bytes or more, two length bytes are required. As a result, in-place ALTER TABLE only supports increasingVARCHAR
size from 0 to 255 bytes or increasingVARCHAR
size from a value equal to or greater than 256 bytes. In-place ALTER TABLE does not support increasingVARCHAR
size from less than 256 bytes to a value equal to or greater than 256 bytes. In this case, the number of required length bytes would change from 1 to 2, which is only supported by a table copy (ALGORITHM=COPY) (…)
The section highlighted is true, however, a bit misleading, changes between VARCHAR(1)
and VARCHAR(255)
will only be INPLACE
if you are using latin1 charset. If you are using utf8 for instance that range drops from VARCHAR(1)
to VARCHAR(63)
. The reason behind this is because in worst case scenario that field with utf8 will count each character as 4 bytes, making VARCHAR(63) < 256 bytes
and VARCHAR(>63) >= 256 bytes
.
More clarification on the Source Code for MySQL:
Conclusion
Online DDL changes are supported, but you must pay attention to your field size in bytes. Which it doesn’t mean it is the size inside the parentheses or the character count.
I found this while meddling with some change in size fields and I didn’t think the documentation was clear enough in the highlighted example.