If you’ve found yourself in the MySQL space even for a little while, you already know that MySQL and other flavors of it offer a couple of data types for developers to choose from. A wide majority of the most popular data types offered by MySQL are related to text – in this space, MySQL offers VARCHAR, CHAR, TINYTEXT, TEXT, and other options. In this blog, we are going to go through the similarities and differences between two of some of the most popular options: the aforementioned VARCHAR and CHAR.
What is CHAR and VARCHAR?
Before diving deeper into the differences between the two data types, we must first make you understand what they are, right? Well, everything in this space is pretty simple because the names of the data types themselves suggest what they are able to do – CHAR stands for “character values” and VARCHAR stands for “variable character values.”
Both CHAR and VARCHAR differ from other similar data types by the fact that they both are considered to be fixed-length data types: we create both CHAR and VARCHAR data types with a “fixed” length that can range from 0 to 255 characters in size if CHAR is being used or from 1 to 65,535 characters if VARCHAR is in use. The bigger our data length is, the more space our characters will occupy (this will probably won’t be a very large problem, however, if your data grows, you might want to keep an eye out on both the structure of your database and learn how to work with big data sets), though this is common sense. What’s not sometimes very widely discussed though is the core differences between the two data types – after all, even their names are similar, so what’s the deal? Bear with us while we guide you through their world...
CHAR vs. VARCHAR
At first glance, both CHAR and VARCHAR might seem very similar – after all, they are defined in exactly the same fashion, aren’t they? Demo CHAR(5) would create a column named demo with a CHAR data type with a length of 5 on it, and demo VARCHAR(5) would create a column named demo with a VARCHAR data type.
However, when we dive deeper into these, we would notice that MySQL hides their differences inside of its architecture:
| CHAR | VARCHAR |
|---|---|
| Fixed length | Variable length |
| Always takes the same amount of space regardless of what's stored | Amount of space taken varies: either 1 or 2 bytes + data |
| Can hold a maximum of 255 characters | Can hold a maximum of 65,535 characters |
| Uses static memory allocation | Uses dynamic memory allocation |
As you can see, some of the differences are apparent, some are not – for example, we would imply that VARCHAR is a variable-length data storage type because of the name, but did you know that CHAR can only hold 255 characters? One of the reasons for that might be because of the fact that CHAR is known to always take up exactly the same amount of storage space – how can MySQL ensure that a column with a CHAR data type will not take up 5MB of storage if it holds 50,000 characters? With 255, that’s easy to do – the length of a CHAR column is defined when we create a table and it doesn’t change no matter what happens. This has its own downsides though – we can’t store very large text values. For you to imagine what CHAR can handle, head over to the Lorem Ipsum generator and observe the paragraphs. CHAR can handle approximately one-third of one paragraph – in other words, four-five sentences.
We have emboldened the text that CHAR could handle without issues in blue – see the problem? Such a data type might be a fit if we are storing very small amounts of text, but not so if we are working with larger-scale systems or even if we need to handle a couple of login forms (remember, we need to hash our passwords to make them not humanly readable – do we really want to take the risk of not being able to store them?)
In a nutshell, CHAR is a fit if we are strapped on storage space and do not plan on storing much data inside of our column.
With VARCHAR however, that’s a different story altogether – as you can understand, it’s a variable-length data type meaning that its rows can get up to 65,535 bytes of data in size. The amount of characters that this data type can handle is not limited to 255, though that’s what pretty much every developer uses if they’re dealing with data – do note that VARCHAR data types can also easily hold 6,000, 10,000 characters, etc., but it all depends on the collation and character set that is being used – for details on that topic, refer to the docs, but the bottom line is this – we can create tables with VARCHAR data types on columns so long as the row size doesn’t exceed 65,535 – simple as that. For example, if we have 7 columns and 6 of them are of 10,000 characters in size and the seventh has 6,000 we are in trouble, because the row size combined would exceed 65,535 – we would have 66,000 instead. You get the point.
Also, keep in mind that the amount of space such a data type takes up on the disk varies from 1 to 2 bytes – keep that in mind when designing systems with bigger sets of data, but other than that, do not worry too much – modern hard and solid state drives don’t make this a problem at all. Use them and you should be fine.
The last thing is that CHAR differs from VARCHAR in the amount it allocates memory for its data – CHAR allocates memory statically meaning that once the memory is allocated, its size cannot change, and VARCHAR does things the other way around – it allocates memory at runtime.
The bottom line is this though – VARCHAR is mostly used to work with medium-sized to big projects and CHAR is generally only used when we are strapped in size or our project is very small. Sacrificing a couple thousand bytes here or there isn’t an issue that should worry a database administrator – disk space is cheap and easy to acquire. However, with that being said, do keep in mind that if we are working with bigger sets of data, we should take care of the size of our data types accordingly. Other than that though, there shouldn’t be any major issues!
Summary
Both CHAR and VARCHAR are data types that are used very frequently – however, to use them properly, we must understand that CHAR is more of a fit when we deal with fixed-length data that does not exceed 255 characters, and VARCHAR is a fit when we deal with projects of a bigger scale. The amount of space these data types occupy does vary as well, however, it shouldn’t cause many issues when we aren’t working with bigger data sets. If you find yourself working with them, though, head over to our big data tutorial, and we will walk you through everything you need to know.
If you liked what you’ve read so far, we have good news – since Arctype is a SQL client, it can very easily help you take care of the data types you have no matter what they are – simply glance over at the left side of the tool and expand the table you are dealing with.
See how easy everything becomes when Arctype is in use? Give it a try today, run a couple of queries (hint: the “/” sign would return a bunch of statements that you can use to build your queries if you’re not sure where to begin), and we will see you in the next one!
Top comments (1)
Thank you for sharing !! 👍😎
I think performance is also an issue ... char columns can be indexed and queried faster than the dynamic varchar columns ... so even if you have a lot of disk space, considere using char for things like product codes or user emails that may be heavily queried and normally won't exceed 255 characters so it works faster