The first group of data types will cover are string or text data types.
We've already seen var char.
We're now going to look at car or char.
Let's just be.
Let's do char and var char.
Okay, here we go.
Let's talk about char.
What is it and how is it different from var char.
Both of them allow us to store text and if you ever have questions I highly recommend you go to the
docs.
They have a great page just explaining char and var char because they are very similar.
That's actually the first line of the documentation here.
They are similar, but they differ in the way they are stored and retrieved.
They differ in their maximum length and they differ in when you might want to use one versus the other,
even though they both store text.
So we know that var char allows us to specify a maximum length.
I can do create table.
How about just friends?
And then I can have each person have a friend that is a var char and then I could say their maximum
length.
Let's just keep it short just so that we can see what happens.
When I exceed.
It will be ten characters, which I know a lot of people have names longer than ten characters.
That's okay.
So when I create that table, if I do an insert into friends, whatever size name, assuming it's less
than ten characters that I insert will be stored as is.
So insert into friends name.
If I do a couple of examples here like Tom, how about a longer name like Juan Pablo?
How many characters is that?
Four or five, six, seven, eight, nine, ten.
All right.
Right up against the limit.
And then let's do one more.
That was a typo.
Let's try that again.
Okay.
Our last one here will just be.
I don't know.
How about James?
Okay, so now we have three rows.
If I do select star from what I call it, friends.
We see our different names unchanged.
Of course, they are all under ten characters, or in this case, exactly ten characters.
We include the space.
It doesn't matter if it's a a letter of the alphabet, the English alphabet, or a space or a dollar
sign.
Whatever character counts as a character.
Anyway, my first point I want to make is that these vary in length names generally vary in length.
Some people have really small names.
Some people have longer names.
Text in general, in the real world varies in length for a lot of different entities, a lot of the
different types of text or strings that we store.
And that's what's nice about VAR.
It's optimized to be able to store text of different sizes in the best possible way.
But if we know that we're going to be storing text that is the same size or almost the same size, for
example, if I'm storing state abbreviations for the US, Colorado, California, Alaska, that sort
of thing.
Alabama.
Those are all two letter abbreviations, and they always are two letters.
They are fixed in size and that is where the car or char data type can come in handy.
Chars have a fixed length.
When I create a column with the char type, I specify the size of that column and every single string
that I insert will be stored as that size.
In other words, if I did create table states and I'll just keep it simple, like we'll call this a
breve.
I'll just call it.
Yeah, I guess a BBR is going to be a char of two characters and exactly two characters.
And I insert into states abbreviation values and then we'll have C.
A.
And one more here.
How about what's another New York OC?
When I select those back, we're not going to see anything different.
Select Star from States.
We have our two states stored K and Y.
But what is different is how much storage this takes up behind the scenes.
This is optimized to store two characters and take up the least amount of memory possible to always
store two characters.
Because I said it's fixed in length.
It's always two characters.
That doesn't mean, though, that I can't store something smaller.
It just means that, let's say I wanted to store a one letter abbreviation for some reason, like X.
What my SQL will actually do is add whitespace to make it fit to characters.
It will pat it, and then when I retrieve it back from the database, it will remove that padding and
it will look like nothing has changed.
But behind the scenes it's actually expanding any of my strings to fit that fixed size.
So that's a significant difference.
If I do something like CHA three, we're essentially saying this column should always be three characters
long.
But if it's smaller than three characters, my SQL will expand.
It will balloon it using whitespace characters to fit three characters.
And here's an excerpt from the docs explaining just that the length of a char column is fixed to the
length that you declare when you create the table.
That length can go anywhere from 0 to 2 five five, but it's always fixed.
And then when you store values in a char column, they are right padded with spaces to fit that specified
length.
And then when they're retrieved, those spaces are removed.
Unless we'll worry about this SQL mode thing, I'm actually going to cover that in the next section.
But there's a setting you can change that will alter that behavior.
So the big downside of using a fixed length is let's say that I want to allow up to ten characters for
somebody's name, but if somebody's name is Tom, then my SQL is going to store that in a fixed column
with a bunch of spaces to fit ten characters.
Whereas with VAR, it doesn't care about that size.
It doesn't care if it's three or five or ten characters.
I mean, it cares about the maximum size, but it doesn't care about any variability.
Hence the var car.
It's totally fine if it varies and it's optimized to store stuff that varies in length, whereas Char
of ten is only really optimized to store things that are ten characters long.
It can store things that are smaller, but it is wasteful.
If you really dive down into how much storage it uses up.
And here's a table this is mostly taken from the docs or some changes that it shows what I'm talking
about.
So if we have char of four, meaning a fixed size of four characters and we try and store an empty string
in there, it's going to add four spaces and that will take up four bytes.
If I try and store a two character string a B, it's going to pad on two spaces to make it take up four
bytes.
If I have four characters a BCD, well, it keeps it as a BCD.
It takes up four bytes, it's fixed in size.
It's going to take up the same amount of memory.
And then we have the equivalent using var car where we have a max size of four.
If you try and insert an empty string or this actually might have a space in it, I think it does have
a space, so it's a single space.
It's going to leave it as is.
It doesn't care and it will store it taking one byte.
If we have two characters that we try and insert a B into a VAR column, it keeps it the same.
It takes up three bytes, and if we have four characters that we try and insert, we'd see that it's
completely unchanged.
It's just four characters, but it takes up five bytes.
Now the actual byte sizes don't matter and that can really vary depending on the types of characters
you're actually storing.
Some characters take up more memory, some really complicated characters, multi byte characters, but
we're not worrying about that.
If we do an apples to apples comparison, we see that if we use var car to store something for characters
long, it might take up five bytes with a char, it might take up four bytes.
So that shows us that it is more efficient in terms of storage or space to use char when your data is
similar in size.
Because if we were always storing for character strings, well it might take four bytes if we use char,
but if we tried to only store four character strings with var char, it takes up five bytes.
So we have way more flexibility here.
If our data varies, it might not take up as much space, but if our data is really always the same
size or plus or minus, maybe one character, if it's always ten characters or nine characters, then
char.
Might be a better choice.
So here are some examples of where we might want to use char.
Things like state abbreviations, yes or no flags y kn zip codes where it's always going to be five
digits at least in the US.
It post codes.
In the UK they have letters, I believe, and but I think they're always the same size.
So something like that where there is a fixed size for the piece of texture working with, then it's
better to use Chas.
If not, I stick with var.
Chas you can worry about memory, you can worry about storage and bytes and all of that.
When you get to the point where it really matters, you'll know it really matters if your queries start
getting really slow.
If you have a boss or people complaining to you about that, your schema and the data types need to
be reconsidered.
That could happen.
But if you're learning my SQL right now, that's possibly a long ways away.
So I would boil it down to this.
Use CHAS for fixed length things if you know they're always going to be the same size.
Otherwise stick with var Chas.
One last thing that I'll show is what happens with either data type when we exceed that limit.
So if we describe states, we have chas of two, right?
It's a fixed string of two characters.
So what happens if I do this insert into states and then we have a abbreviation, I think is what we
called it, a BBR values.
And I do something.
Let's do a short one first.
Just the letter X, That's fine.
Remember, it's going to pat it with a space in memory.
But when I select it back out, select star from states, we can't tell that there's any extra space.
It's I mean, I guess how would you tell anyway?
But if I did something like this, we could do this right?
Select char, length of abbreviation.
Remember this string function?
We see that it does in fact call it or it views it as one character long, but in memory it stores it
as two characters.
It adds spaces.
Okay, But back to what happens if we insert something that is too long?
Like I try and put Ohio in here, I get a straight up error.
It says data too long for that column.
You can't do that.
And when I try and select from states, Ohio didn't make it in there.
It wasn't truncated.
It didn't store like zero h, it stored nothing.
Now, when we talk about SQL modes, the sort of settings that we can change to alter the behavior of
certain things, we'll see that maybe that's not always the case, but the standard SQL solution to
this problem is to throw an error and not insert your data.
The other option, of course, would be to truncate the data and actually just store the first two characters.
But that is not what happens with the current mode.
We have set up the default strict mode and the same goes for var.
So what was our table we made at the beginning?
I've already forgotten of this video.
Friends.
Right.
So if I try and insert into friends.
We had a name and then somebody who has a really long name like this same thing data.
Too long for that column name.
It's more than ten characters, which I believe is the limit that we set up.
Yes, ten characters.
This has been a long video.
I hope you're still here.
And if not, I hope you got what you needed.
And you understand the difference between Char and var.
All right.
I know it's kind of dull.