MySQL | CHAR and VARCHAR
VARCHAR types in MySQL are both used to store strings (i.e. text), but the way they do so is different.
They both take an integer as input, specifically from
255. It takes one byte to store one character.
CHAR(10) would mean that each of the column entries cannot be more than 10 characters long.
The memory space needed to store CHAR formatted data is fixed. Carrying on with our example, suppose we used
CHAR(10) as our data type:
Now, suppose we added
“bob” as our entry, which is only 3 characters long. What is interesting is that this piece of data is actually stored as
“bob “ (i.e. 7 trailing spaces) in our database.
This means that we still use 10 bytes even though we’re only technically storing 3 characters. However, when we do retrieve this data, MySQL will remove the trailing spaces behind the scenes, so you don't have to worry about that!
The biggest difference between VARCHAR and CHAR is that the memory space is not fixed. Suppose we set the data type to be
CHAR(10), we cannot store strings that have more than 10 characters.
Now, suppose we stored the string
CHAR(10), MySQL does not add any trailing space, which is great in the sense that we are saving a lot of memory space. However, the caveat here is that the
VARCHAR requires 1 byte or 2 bytes additional storage space per data. More specifically, they are stored in the front (i.e. prefixes).
The prefix is used to indicate the number of bytes in our data. Each byte can be used to represent $2^8=256$ characters. Therefore, for
VARCHAR(10), the prefix needed will only take up 1 byte, yet
VARCHAR(400) for instance, the prefix will take up 2 bytes.