1 00:00:00,090 --> 00:00:04,770 The first group of data types will cover are string or text data types. 2 00:00:04,770 --> 00:00:06,470 We've already seen var char. 3 00:00:06,480 --> 00:00:09,180 We're now going to look at car or char. 4 00:00:09,390 --> 00:00:10,230 Let's just be. 5 00:00:10,230 --> 00:00:11,910 Let's do char and var char. 6 00:00:11,940 --> 00:00:13,650 Okay, here we go. 7 00:00:13,650 --> 00:00:15,060 Let's talk about char. 8 00:00:15,060 --> 00:00:18,660 What is it and how is it different from var char. 9 00:00:18,690 --> 00:00:23,490 Both of them allow us to store text and if you ever have questions I highly recommend you go to the 10 00:00:23,490 --> 00:00:23,970 docs. 11 00:00:23,970 --> 00:00:28,500 They have a great page just explaining char and var char because they are very similar. 12 00:00:28,500 --> 00:00:31,290 That's actually the first line of the documentation here. 13 00:00:31,290 --> 00:00:35,220 They are similar, but they differ in the way they are stored and retrieved. 14 00:00:35,220 --> 00:00:40,800 They differ in their maximum length and they differ in when you might want to use one versus the other, 15 00:00:40,800 --> 00:00:42,810 even though they both store text. 16 00:00:43,110 --> 00:00:47,540 So we know that var char allows us to specify a maximum length. 17 00:00:47,550 --> 00:00:50,640 I can do create table. 18 00:00:51,360 --> 00:00:53,610 How about just friends? 19 00:00:53,760 --> 00:00:58,680 And then I can have each person have a friend that is a var char and then I could say their maximum 20 00:00:58,680 --> 00:00:58,950 length. 21 00:00:58,950 --> 00:01:01,680 Let's just keep it short just so that we can see what happens. 22 00:01:01,680 --> 00:01:02,370 When I exceed. 23 00:01:02,370 --> 00:01:07,590 It will be ten characters, which I know a lot of people have names longer than ten characters. 24 00:01:07,590 --> 00:01:08,370 That's okay. 25 00:01:08,880 --> 00:01:15,780 So when I create that table, if I do an insert into friends, whatever size name, assuming it's less 26 00:01:15,780 --> 00:01:20,070 than ten characters that I insert will be stored as is. 27 00:01:20,160 --> 00:01:23,010 So insert into friends name. 28 00:01:23,130 --> 00:01:29,850 If I do a couple of examples here like Tom, how about a longer name like Juan Pablo? 29 00:01:30,270 --> 00:01:31,560 How many characters is that? 30 00:01:31,560 --> 00:01:33,420 Four or five, six, seven, eight, nine, ten. 31 00:01:33,420 --> 00:01:34,020 All right. 32 00:01:34,020 --> 00:01:35,220 Right up against the limit. 33 00:01:35,220 --> 00:01:37,170 And then let's do one more. 34 00:01:37,350 --> 00:01:38,220 That was a typo. 35 00:01:38,250 --> 00:01:39,270 Let's try that again. 36 00:01:39,630 --> 00:01:40,150 Okay. 37 00:01:40,980 --> 00:01:42,840 Our last one here will just be. 38 00:01:42,840 --> 00:01:43,200 I don't know. 39 00:01:43,200 --> 00:01:44,220 How about James? 40 00:01:45,030 --> 00:01:46,380 Okay, so now we have three rows. 41 00:01:46,380 --> 00:01:50,850 If I do select star from what I call it, friends. 42 00:01:51,970 --> 00:01:54,310 We see our different names unchanged. 43 00:01:54,340 --> 00:01:59,270 Of course, they are all under ten characters, or in this case, exactly ten characters. 44 00:01:59,290 --> 00:02:00,470 We include the space. 45 00:02:00,490 --> 00:02:05,380 It doesn't matter if it's a a letter of the alphabet, the English alphabet, or a space or a dollar 46 00:02:05,380 --> 00:02:05,790 sign. 47 00:02:05,800 --> 00:02:08,660 Whatever character counts as a character. 48 00:02:08,680 --> 00:02:14,480 Anyway, my first point I want to make is that these vary in length names generally vary in length. 49 00:02:14,500 --> 00:02:15,850 Some people have really small names. 50 00:02:15,850 --> 00:02:17,590 Some people have longer names. 51 00:02:18,160 --> 00:02:23,200 Text in general, in the real world varies in length for a lot of different entities, a lot of the 52 00:02:23,200 --> 00:02:26,200 different types of text or strings that we store. 53 00:02:26,200 --> 00:02:28,200 And that's what's nice about VAR. 54 00:02:28,210 --> 00:02:33,940 It's optimized to be able to store text of different sizes in the best possible way. 55 00:02:34,300 --> 00:02:41,890 But if we know that we're going to be storing text that is the same size or almost the same size, for 56 00:02:41,890 --> 00:02:51,040 example, if I'm storing state abbreviations for the US, Colorado, California, Alaska, that sort 57 00:02:51,040 --> 00:02:51,730 of thing. 58 00:02:52,270 --> 00:02:53,200 Alabama. 59 00:02:53,230 --> 00:02:58,210 Those are all two letter abbreviations, and they always are two letters. 60 00:02:58,360 --> 00:03:04,360 They are fixed in size and that is where the car or char data type can come in handy. 61 00:03:04,390 --> 00:03:06,370 Chars have a fixed length. 62 00:03:06,370 --> 00:03:14,800 When I create a column with the char type, I specify the size of that column and every single string 63 00:03:14,800 --> 00:03:17,760 that I insert will be stored as that size. 64 00:03:17,770 --> 00:03:27,010 In other words, if I did create table states and I'll just keep it simple, like we'll call this a 65 00:03:27,010 --> 00:03:27,910 breve. 66 00:03:28,620 --> 00:03:29,330 I'll just call it. 67 00:03:29,340 --> 00:03:36,240 Yeah, I guess a BBR is going to be a char of two characters and exactly two characters. 68 00:03:38,570 --> 00:03:46,920 And I insert into states abbreviation values and then we'll have C. 69 00:03:46,940 --> 00:03:47,780 A. 70 00:03:48,950 --> 00:03:51,090 And one more here. 71 00:03:51,110 --> 00:03:55,010 How about what's another New York OC? 72 00:03:55,850 --> 00:03:58,490 When I select those back, we're not going to see anything different. 73 00:03:58,490 --> 00:03:59,960 Select Star from States. 74 00:04:00,680 --> 00:04:03,860 We have our two states stored K and Y. 75 00:04:04,040 --> 00:04:08,600 But what is different is how much storage this takes up behind the scenes. 76 00:04:08,630 --> 00:04:14,930 This is optimized to store two characters and take up the least amount of memory possible to always 77 00:04:14,930 --> 00:04:16,670 store two characters. 78 00:04:16,670 --> 00:04:18,970 Because I said it's fixed in length. 79 00:04:18,980 --> 00:04:20,510 It's always two characters. 80 00:04:20,750 --> 00:04:23,990 That doesn't mean, though, that I can't store something smaller. 81 00:04:24,170 --> 00:04:30,110 It just means that, let's say I wanted to store a one letter abbreviation for some reason, like X. 82 00:04:30,290 --> 00:04:36,530 What my SQL will actually do is add whitespace to make it fit to characters. 83 00:04:36,530 --> 00:04:42,860 It will pat it, and then when I retrieve it back from the database, it will remove that padding and 84 00:04:42,860 --> 00:04:44,500 it will look like nothing has changed. 85 00:04:44,510 --> 00:04:50,270 But behind the scenes it's actually expanding any of my strings to fit that fixed size. 86 00:04:50,390 --> 00:04:52,220 So that's a significant difference. 87 00:04:52,430 --> 00:04:58,850 If I do something like CHA three, we're essentially saying this column should always be three characters 88 00:04:58,850 --> 00:04:59,530 long. 89 00:04:59,540 --> 00:05:04,310 But if it's smaller than three characters, my SQL will expand. 90 00:05:04,310 --> 00:05:08,890 It will balloon it using whitespace characters to fit three characters. 91 00:05:08,900 --> 00:05:13,910 And here's an excerpt from the docs explaining just that the length of a char column is fixed to the 92 00:05:13,910 --> 00:05:16,130 length that you declare when you create the table. 93 00:05:16,160 --> 00:05:20,060 That length can go anywhere from 0 to 2 five five, but it's always fixed. 94 00:05:20,060 --> 00:05:26,210 And then when you store values in a char column, they are right padded with spaces to fit that specified 95 00:05:26,210 --> 00:05:26,780 length. 96 00:05:27,050 --> 00:05:29,690 And then when they're retrieved, those spaces are removed. 97 00:05:29,690 --> 00:05:34,160 Unless we'll worry about this SQL mode thing, I'm actually going to cover that in the next section. 98 00:05:34,160 --> 00:05:37,670 But there's a setting you can change that will alter that behavior. 99 00:05:37,670 --> 00:05:44,900 So the big downside of using a fixed length is let's say that I want to allow up to ten characters for 100 00:05:44,900 --> 00:05:51,800 somebody's name, but if somebody's name is Tom, then my SQL is going to store that in a fixed column 101 00:05:51,800 --> 00:05:54,920 with a bunch of spaces to fit ten characters. 102 00:05:54,920 --> 00:05:59,210 Whereas with VAR, it doesn't care about that size. 103 00:05:59,210 --> 00:06:01,670 It doesn't care if it's three or five or ten characters. 104 00:06:01,670 --> 00:06:05,660 I mean, it cares about the maximum size, but it doesn't care about any variability. 105 00:06:05,660 --> 00:06:07,310 Hence the var car. 106 00:06:07,310 --> 00:06:13,580 It's totally fine if it varies and it's optimized to store stuff that varies in length, whereas Char 107 00:06:13,580 --> 00:06:18,170 of ten is only really optimized to store things that are ten characters long. 108 00:06:18,200 --> 00:06:21,980 It can store things that are smaller, but it is wasteful. 109 00:06:21,980 --> 00:06:25,160 If you really dive down into how much storage it uses up. 110 00:06:25,160 --> 00:06:30,290 And here's a table this is mostly taken from the docs or some changes that it shows what I'm talking 111 00:06:30,290 --> 00:06:30,530 about. 112 00:06:30,530 --> 00:06:39,410 So if we have char of four, meaning a fixed size of four characters and we try and store an empty string 113 00:06:39,410 --> 00:06:43,610 in there, it's going to add four spaces and that will take up four bytes. 114 00:06:43,970 --> 00:06:51,680 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 115 00:06:51,680 --> 00:06:52,340 bytes. 116 00:06:52,550 --> 00:06:57,770 If I have four characters a BCD, well, it keeps it as a BCD. 117 00:06:57,800 --> 00:07:00,170 It takes up four bytes, it's fixed in size. 118 00:07:00,170 --> 00:07:02,300 It's going to take up the same amount of memory. 119 00:07:02,300 --> 00:07:06,920 And then we have the equivalent using var car where we have a max size of four. 120 00:07:07,250 --> 00:07:12,620 If you try and insert an empty string or this actually might have a space in it, I think it does have 121 00:07:12,620 --> 00:07:14,300 a space, so it's a single space. 122 00:07:14,300 --> 00:07:16,340 It's going to leave it as is. 123 00:07:16,340 --> 00:07:19,520 It doesn't care and it will store it taking one byte. 124 00:07:19,790 --> 00:07:25,850 If we have two characters that we try and insert a B into a VAR column, it keeps it the same. 125 00:07:25,850 --> 00:07:31,460 It takes up three bytes, and if we have four characters that we try and insert, we'd see that it's 126 00:07:31,460 --> 00:07:32,660 completely unchanged. 127 00:07:32,660 --> 00:07:35,690 It's just four characters, but it takes up five bytes. 128 00:07:35,690 --> 00:07:40,910 Now the actual byte sizes don't matter and that can really vary depending on the types of characters 129 00:07:40,910 --> 00:07:42,170 you're actually storing. 130 00:07:42,170 --> 00:07:48,170 Some characters take up more memory, some really complicated characters, multi byte characters, but 131 00:07:48,170 --> 00:07:49,550 we're not worrying about that. 132 00:07:49,550 --> 00:07:56,360 If we do an apples to apples comparison, we see that if we use var car to store something for characters 133 00:07:56,360 --> 00:08:02,390 long, it might take up five bytes with a char, it might take up four bytes. 134 00:08:02,390 --> 00:08:09,080 So that shows us that it is more efficient in terms of storage or space to use char when your data is 135 00:08:09,080 --> 00:08:10,760 similar in size. 136 00:08:10,760 --> 00:08:16,850 Because if we were always storing for character strings, well it might take four bytes if we use char, 137 00:08:17,030 --> 00:08:22,820 but if we tried to only store four character strings with var char, it takes up five bytes. 138 00:08:22,820 --> 00:08:25,100 So we have way more flexibility here. 139 00:08:25,100 --> 00:08:31,130 If our data varies, it might not take up as much space, but if our data is really always the same 140 00:08:31,130 --> 00:08:37,010 size or plus or minus, maybe one character, if it's always ten characters or nine characters, then 141 00:08:37,010 --> 00:08:37,580 char. 142 00:08:37,659 --> 00:08:38,919 Might be a better choice. 143 00:08:39,250 --> 00:08:42,409 So here are some examples of where we might want to use char. 144 00:08:42,429 --> 00:08:49,990 Things like state abbreviations, yes or no flags y kn zip codes where it's always going to be five 145 00:08:49,990 --> 00:08:51,640 digits at least in the US. 146 00:08:52,180 --> 00:08:53,200 It post codes. 147 00:08:53,200 --> 00:08:57,070 In the UK they have letters, I believe, and but I think they're always the same size. 148 00:08:57,070 --> 00:09:02,890 So something like that where there is a fixed size for the piece of texture working with, then it's 149 00:09:02,890 --> 00:09:03,940 better to use Chas. 150 00:09:03,970 --> 00:09:05,940 If not, I stick with var. 151 00:09:05,950 --> 00:09:10,870 Chas you can worry about memory, you can worry about storage and bytes and all of that. 152 00:09:10,870 --> 00:09:15,520 When you get to the point where it really matters, you'll know it really matters if your queries start 153 00:09:15,520 --> 00:09:16,690 getting really slow. 154 00:09:16,990 --> 00:09:23,590 If you have a boss or people complaining to you about that, your schema and the data types need to 155 00:09:23,590 --> 00:09:24,950 be reconsidered. 156 00:09:24,970 --> 00:09:26,140 That could happen. 157 00:09:26,140 --> 00:09:30,330 But if you're learning my SQL right now, that's possibly a long ways away. 158 00:09:30,340 --> 00:09:32,050 So I would boil it down to this. 159 00:09:32,050 --> 00:09:35,830 Use CHAS for fixed length things if you know they're always going to be the same size. 160 00:09:35,830 --> 00:09:37,600 Otherwise stick with var Chas. 161 00:09:37,660 --> 00:09:44,650 One last thing that I'll show is what happens with either data type when we exceed that limit. 162 00:09:44,650 --> 00:09:50,230 So if we describe states, we have chas of two, right? 163 00:09:50,230 --> 00:09:52,390 It's a fixed string of two characters. 164 00:09:52,390 --> 00:10:00,670 So what happens if I do this insert into states and then we have a abbreviation, I think is what we 165 00:10:00,670 --> 00:10:03,430 called it, a BBR values. 166 00:10:03,730 --> 00:10:04,690 And I do something. 167 00:10:04,690 --> 00:10:05,860 Let's do a short one first. 168 00:10:05,860 --> 00:10:08,140 Just the letter X, That's fine. 169 00:10:08,290 --> 00:10:11,020 Remember, it's going to pat it with a space in memory. 170 00:10:11,020 --> 00:10:17,680 But when I select it back out, select star from states, we can't tell that there's any extra space. 171 00:10:18,310 --> 00:10:20,470 It's I mean, I guess how would you tell anyway? 172 00:10:20,470 --> 00:10:23,810 But if I did something like this, we could do this right? 173 00:10:23,830 --> 00:10:28,540 Select char, length of abbreviation. 174 00:10:28,540 --> 00:10:30,310 Remember this string function? 175 00:10:30,430 --> 00:10:37,210 We see that it does in fact call it or it views it as one character long, but in memory it stores it 176 00:10:37,210 --> 00:10:37,930 as two characters. 177 00:10:37,930 --> 00:10:39,010 It adds spaces. 178 00:10:39,160 --> 00:10:43,150 Okay, But back to what happens if we insert something that is too long? 179 00:10:43,150 --> 00:10:48,100 Like I try and put Ohio in here, I get a straight up error. 180 00:10:48,100 --> 00:10:50,230 It says data too long for that column. 181 00:10:50,230 --> 00:10:51,490 You can't do that. 182 00:10:51,490 --> 00:10:55,120 And when I try and select from states, Ohio didn't make it in there. 183 00:10:55,120 --> 00:10:56,410 It wasn't truncated. 184 00:10:56,410 --> 00:10:59,440 It didn't store like zero h, it stored nothing. 185 00:10:59,770 --> 00:11:06,070 Now, when we talk about SQL modes, the sort of settings that we can change to alter the behavior of 186 00:11:06,070 --> 00:11:13,330 certain things, we'll see that maybe that's not always the case, but the standard SQL solution to 187 00:11:13,330 --> 00:11:17,410 this problem is to throw an error and not insert your data. 188 00:11:17,710 --> 00:11:23,440 The other option, of course, would be to truncate the data and actually just store the first two characters. 189 00:11:23,440 --> 00:11:26,560 But that is not what happens with the current mode. 190 00:11:26,560 --> 00:11:30,420 We have set up the default strict mode and the same goes for var. 191 00:11:30,760 --> 00:11:33,880 So what was our table we made at the beginning? 192 00:11:33,880 --> 00:11:35,740 I've already forgotten of this video. 193 00:11:35,740 --> 00:11:36,460 Friends. 194 00:11:36,460 --> 00:11:36,820 Right. 195 00:11:36,820 --> 00:11:38,920 So if I try and insert into friends. 196 00:11:41,040 --> 00:11:46,770 We had a name and then somebody who has a really long name like this same thing data. 197 00:11:46,770 --> 00:11:48,780 Too long for that column name. 198 00:11:48,870 --> 00:11:53,070 It's more than ten characters, which I believe is the limit that we set up. 199 00:11:53,430 --> 00:11:54,990 Yes, ten characters. 200 00:11:55,860 --> 00:11:57,060 This has been a long video. 201 00:11:57,480 --> 00:11:58,890 I hope you're still here. 202 00:11:58,890 --> 00:12:01,290 And if not, I hope you got what you needed. 203 00:12:01,290 --> 00:12:03,760 And you understand the difference between Char and var. 204 00:12:03,810 --> 00:12:04,530 All right. 205 00:12:04,530 --> 00:12:05,760 I know it's kind of dull.