Disclaimer: this post takes into consideration that strict mode is enabled on the server
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
COLLATION of the table. You can also specify a different collation for a column.
- 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
VARCHARcolumn must remain the same. For
VARCHARvalues of 0 to 255, one length byte is required to encode the value. For
VARCHARvalues of 256 bytes or more, two length bytes are required. As a result, in-place ALTER TABLE only supports increasing
VARCHARsize from 0 to 255 bytes or increasing
VARCHARsize from a value equal to or greater than 256 bytes. In-place ALTER TABLE does not support increasing
VARCHARsize 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(255) will only be
INPLACE if you are using latin1 charset. If you are using utf8 for instance that range drops from
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:
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.