Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
MySQL provides two unique types that work from lists of strings. So let's learn about them using the scratch data base and start by creating a table. And of course I will drop table if exists, and we'll call it test. Create table test. I'll have an id column and ENUM column. So ENUM works from a list of strings, and the column can hold in each row one of these strings in the list.
And so I'll give it a list of strings here. Pablo, Henri, and Jackson. And no comma after that last column in the definition. And so we can insert into test, values. And so I can put Pablo, Henri, or Jackson, but no other string's valid in this column. And then we'll select star from test, and press go. So here we have three rows, and column haze Pablo, Henri, and Jackson.
Now, what's interesting here, it's actually being stored. Because we could have a lot of columns. We could do this again, we could just copy and paste these three again. And I now have six rows. Right? I can have these same values many times throughout them. And I don't need to store these strings over and over again. In fact, all that this column is storing is numbers. If, instead of Pablo here, I put in a one. And instead of Henri, I put in a two. And instead of Jackson, I put in a three.
The result is the same. Because really all that's being stored in the ENUM column, is an index into this table of strings. So I can have a bunch of these, and I could have four, five, six, seven. And, each of these rows in that column is just holding a number that represents an index into that list of strings. So, it's a space-saving tool. And it's also a bit of a constraint tool, because you're only going to have the values that you've defined in the table.
So, that's ENUM. SET, works very similarly, and I'm just going to change this to SET. And we'll use exactly the same. We just have, Pablo, Henri and Jackson. And I'm going to say Go. And it looks like our result is exactly the same. But there's a very distinct difference. I'm going to, copy this. And, put a few more of them here. And I can have Pablo and Jackson in the same row.
In that one column, I can have Henri and Jackson, or I can have Pablo, Henri, and Jackson. And you can see in these rows down here we have more than one value from the list. So the distinction here is rather than how ENUM works, where each of these rows for this column holds a number that represents an index into this string. Instead, with SET, each bit in this value represents one of these strings.
So, if I come down here, I'm going to take this one. And I'm going to copy and paste it three more times. And I'm going to put in the value one, and I'm going to put in the value two, and now I'm going to put in the value three, and actually let's keep going four, five, six and seven. Because you'll notice we have. Three strings to find. So three bits are actually being defined here, and there are seven possible values with three bits.
And so I'm going to say go here and what we have is a one is Pablo. Two is on Henri. Three is both of those first two bits set, and so that's Pablo and Henri. Four is just that third bit set. And that's Jackson. Five, the first and the third bit. Six, the second and the third bit. And seven, all three bits are set. So each bit represents one of these strings. That's the distinction between SET and ENUM.
So if you know you're going to have a limited number of possible values in a column, it can be much more economical to store an index. As in an ENUM than many copies of the same string. Likewise if you need to store more than one from a set of possible values. A set can be economical as well. And a set may also be useful for storing flags and State indicators.
Get unlimited access to all courses for just $25/month.Become a member
61 Video lessons · 104776 Viewers
56 Video lessons · 116661 Viewers
71 Video lessons · 85848 Viewers
131 Video lessons · 41073 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.