Adding a new item to an Enum requires a ALTER TABLE, but as long as you out the new vales at the end it will be instant, even for extremely large tables.
@@kellyjoe256Assume that we have small, medium and large. If we want to add x-small to the enum, x-small will be the first item in list and enum will be reordered. Adding at the end of list does not cause that.
ordering by the integer was my original guess and that the "surprise" was the string ordering actually while typing this (and letting the video continue playing), i got to the min/max going by the string value
In MySQL/MariaDB, ORDER BY sorts values based on their integer index for faster performance, while MAX and MIN treat them as strings, using alphabetical comparison. This behavior, although seeminly confusing, is by design. For MAX and MIN string-based operations are used as they are often uswd for reporting, where lexicographic ordering is more logical. On the other hand, ORDER BY uses the internal integer to enhance sorting performance, as sorting by integers is generally faster than sorting by strings. This is also in the Docs 😊 Btw, love the videos 😊
In PostgreSQL you can add new value to enum Here is example: ALTER TYPE enum_type ADD VALUE 'new_value'; -- appends to list ALTER TYPE enum_type ADD VALUE 'new_value' BEFORE 'old_value'; ALTER TYPE enum_type ADD VALUE 'new_value' AFTER 'old_value';
Your videos are great, I'm glad to know I'm not the only one who printed off the MySQL docs, there must be dozens of us! I check the job posting weekly, I hope I can work at a company this cool haha.
Thanks for the video with good examples! Can you get into more details on the drawbacks of using enums? Comparing to for example having just another table with ids from 1 to 5 with the same values which makes it easier to do migrations etc.
Using a reference table is a viable way of handling them. Alternatively just using a small integer field and doing the mapping in the application is also viable. Neither of those options give you the bit of data validation that enums do. Just tradeoffs, all the way down! I prefer using integers and doing the mapping in the application
@@PlanetScale Thanks! With the approach using int values we also can get the values check like we have with enums at lease when using Prostgres by using CHECK size INT NOT NULL, CHECK (size BETWEEN 1 AND 5) But not sure in the case if we add a new type how we then handle the migrations updating the range in check
Thanks for your videos. They are amazing. How about make a video "how EXPLAIN commmand works and what their result means?" Its look like a pretty useful tool
Thanks for sharing, love watching these videos. Always good to learn something new or refresh something forgotten lol. I've heard a lot of good things about Planet Scale. Will be using your platform in the future.
You can still use `ORDER BY FIELD(enumfield,'enum2','enum4','enum1')` etc to order enums by their text value, it has some oddities like if you miss out some they appear at the top of the list unless you say do DESC instead of ASC, but ive used that when the enum index was out of order
Nice video. I would include a section mentioning enums are not aligned with the relational model. The same can be achieved with a separate table where each row is an entry of the enum. Foreign keys ensure that only legal values are allowed. One can add properties like order as extra columns
Database-layer enums are great... in moderation 🙂 I regularly use them to declare values that are also enumerated in the application. Since database schema and application evolve side-by-side it is ok to alter the schema in the (rare) cases where a new type needs to be added. This is most likely also the best indicator whether you should use them: if regular change is necessary a foreign key relation to a separate table is usually the better choice. But for everlasting things that only have a hand full valid options an enum can be the right fit I guess since I mainly use them to categorize things I not yet stumbled on the difference between sorting behavior and max function - but good to know!
database first on enums is fine for small companies or personal projects but it is generally frowned upon in most modern applications and tends to annoy your engineers.
Hi Aaron, nice video, as always. Can you make a video about foreign keys constraint, why planetscale not support it, and how to migrate existing database with foreign keys constraint already set to planetscale?
Weird quirk! I wonder if `MAX(myenum+0)` behaves numerically from the explicit cast. I've done some wild stuff with enums, taking advantage of their number nature. All well-tested, of course. But I was able to do a visibility check very efficiently with `visibility > privacy` where the two values got treated as numbers. It was a massive speedup and made it possible for the values to have readable names. I can't remember if I needed the +0 for that though. It's been awhile 😂
This was a super interesting video, thanks for sharing! I have an idea for another potential video - Can you walk us through how to properly optimise queries? Let's say you have added indexes, and found that the cost estimations are still waaaay off, What's the next thing you do? How do you find out what the analyze query is doing in more detail?
Nothing like waking up on a nice sunny, sunday morning, opening my printed mysql docs and reading them docs with a nice hot cup of tea. imagine reading fiction novels lmao
If I ever wanted to do data validation in my database, I would rather add a check constraint for its maintability than an enum, it is a true nightmare to maintain. Does this make sense ? Do you think of other way to do data validation in DB ?
I probably wouldn't add a check constraint for much, personally. I don't like pushing validation down to the DB layer. Enums are kind of a weird case because it's more data validation than business logic validation. As always, depends on the use case!
The first part didn't surprise me but the MAX part is really weird, I think the same applies to Sets as well. I sometimes find it easier to order by FIND_IN_SET(x, 'v1,v2,v3,v4,v5') or any other custom order when the order is subject to change depending on some business logics Thanks Aaron!
@@PlanetScaleThat will be nice, although I don't use the set column type in favor of json column but I still find it interesting. Thanks once again Aaron!
I suspect that altering the schema for the enums and inserting a value in the middle of the list will break all the existing numerical values after the new enum. They’ll all be off by 1 I suspect. Perhaps it’s smart enough to redo the integer to enum mapping.
@@PlanetScale While the client of the DB doesn’t rely on the integers the DB will. So if you have this initially: Foo - 1 Bar - 2 And you insert a record with Bar, the stored numerical value will be 2. Now you alter the scheme so the enums are: Foo, Buzz, Bar. I.e. Foo - 1 Buzz - 2 Bar - 3 As the previous record was stored as a numerical 2, wouldn’t it accidentally now be Buzz? Or I wonder if the alter conmand is smart enough to re-number the stored values. It’s an interesting thing to try.
Strange to be certain (and the fact that they seem to be 1-indexed), but I've never, in 25 years, needed to use enums for anything other than a constraint on a value being inserted.
This. The INT then becomes a primary key which you have full control over. You can also add more columns for other properties if you need to down the line.
Who would use a max on enums? I would prefer a foreign table on the field, so you can add or remove as many options as you like. Talk about foreign tables...it would be nice, if there will be a video on that topic too with all the pros and automatisms provided by MySQL for that. Thank you and c ya.
Using them in the database could prevent a faulty app from inserting invalid values into the database, and I think that is a good thing. You should not trust the application software, or its users, so it seems sensible that the database system checks as much as it can.
Yep, definitely use a FK pointing to a beautiful table listing all your allowed values, you get the enum safety plus changing a label is not a nightmare anymore (and your DBA will not cry in Normal Form anymore 😇)
Advice: don’t use enum in your code: use type tables instead. Yes, it’s not compile-time safe, but often even enums in code aren’t because they’re parsed from strings anyway. If you use a language with powerful macros like rust, you can even make enums from the type table itself so you don’t trade compile time safety for single source of truth or extensibility.
the serious flaw to me is that actually, enums have one hidden value: empty. If you try to insert a enum value that does not exist in that list it will be jsut blank. According to the docs, this can be avoided only with per-DB configuration. But overall, this is super weird to me
TL;DR: don't use Enums in database layer :p, that behaviour is too strange and unintuative in my opinion. I guess it'd be a (minor) perfomance hit to use a enum table to reference instead? Or does the DBMS make a table for the Enums in the non-visible part?
I think it's just another tool in the chest. For situations that make sense, it's great. It you have a set number of options in a column that you don't order by, and that table has millions of rows, you save a lot of space by storing just the integer rather than the corresponding text value.
For something like storing, say, authorization mechanism (i.e. 'oauth', 'password', 'token'), enums makes sense since chances are small that you're going to change these often and a schema migration is fine for those cases where it does. But for things that are more likely to change, such as user roles/permissions, tables would make more sense. Of course it highly depends on your specific use-case.
Unfortunately... it depends 😭 They're nice because you get that bit of validation at the db layer, the tiny storage + indexes, and the readability of a string. But if those things don't matter or you're going to be changing the values a lot, I'd move the enums to the app layer.
Why would I want to do mathematical operations on my enum. I don’t think this issues are valid enough to not use enums. What is the use case for ordering enums. I just don’t see it
Adding a new item to an Enum requires a ALTER TABLE, but as long as you out the new vales at the end it will be instant, even for extremely large tables.
Good point!
"but as long as you out the new vales at the end it will be instant"
Can you please expand on this?
@@kellyjoe256Assume that we have small, medium and large. If we want to add x-small to the enum, x-small will be the first item in list and enum will be reordered. Adding at the end of list does not cause that.
Man, I LOVE the content of this channel!!!
You're kind to say so. Thank you!
ordering by the integer was my original guess and that the "surprise" was the string ordering
actually while typing this (and letting the video continue playing), i got to the min/max going by the string value
Weird right
In MySQL/MariaDB, ORDER BY sorts values based on their integer index for faster performance, while MAX and MIN treat them as strings, using alphabetical comparison. This behavior, although seeminly confusing, is by design. For MAX and MIN string-based operations are used as they are often uswd for reporting, where lexicographic ordering is more logical. On the other hand, ORDER BY uses the internal integer to enhance sorting performance, as sorting by integers is generally faster than sorting by strings.
This is also in the Docs 😊
Btw, love the videos 😊
But ordering is also used for reporting, and using integers would also be faster for max/min. MySQL devs even considered fixing it in 2002.
In PostgreSQL you can add new value to enum
Here is example:
ALTER TYPE enum_type ADD VALUE 'new_value'; -- appends to list
ALTER TYPE enum_type ADD VALUE 'new_value' BEFORE 'old_value';
ALTER TYPE enum_type ADD VALUE 'new_value' AFTER 'old_value';
Your videos are great, I'm glad to know I'm not the only one who printed off the MySQL docs, there must be dozens of us! I check the job posting weekly, I hope I can work at a company this cool haha.
Dozens of us! Dozens!
Great Videos 🌟
Videos I want Next about:
Normalisation
Transactions
Cursors
Automatic Backups
Thanks for the video with good examples!
Can you get into more details on the drawbacks of using enums? Comparing to for example having just another table with ids from 1 to 5 with the same values which makes it easier to do migrations etc.
Using a reference table is a viable way of handling them. Alternatively just using a small integer field and doing the mapping in the application is also viable. Neither of those options give you the bit of data validation that enums do. Just tradeoffs, all the way down! I prefer using integers and doing the mapping in the application
@@PlanetScale Thanks!
With the approach using int values we also can get the values check like we have with enums at lease when using Prostgres by using CHECK
size INT NOT NULL,
CHECK (size BETWEEN 1 AND 5)
But not sure in the case if we add a new type how we then handle the migrations updating the range in check
Thanks for your videos. They are amazing. How about make a video "how EXPLAIN commmand works and what their result means?"
Its look like a pretty useful tool
That's a good one!
this channel is a sql gold mine!
Thanks for sharing, love watching these videos. Always good to learn something new or refresh something forgotten lol. I've heard a lot of good things about Planet Scale. Will be using your platform in the future.
You can still use `ORDER BY FIELD(enumfield,'enum2','enum4','enum1')` etc to order enums by their text value, it has some oddities like if you miss out some they appear at the top of the list unless you say do DESC instead of ASC, but ive used that when the enum index was out of order
This is very helpful. Love these videos. Thanks.
Always learning something here
Nice video. I would include a section mentioning enums are not aligned with the relational model. The same can be achieved with a separate table where each row is an entry of the enum. Foreign keys ensure that only legal values are allowed. One can add properties like order as extra columns
Database-layer enums are great... in moderation 🙂
I regularly use them to declare values that are also enumerated in the application. Since database schema and application evolve side-by-side it is ok to alter the schema in the (rare) cases where a new type needs to be added.
This is most likely also the best indicator whether you should use them: if regular change is necessary a foreign key relation to a separate table is usually the better choice.
But for everlasting things that only have a hand full valid options an enum can be the right fit
I guess since I mainly use them to categorize things I not yet stumbled on the difference between sorting behavior and max function - but good to know!
All things in moderation!
> But for everlasting things that only have a hand full valid options an enum can be the right fit
Yup, fully agree
database first on enums is fine for small companies or personal projects but it is generally frowned upon in most modern applications and tends to annoy your engineers.
What software are you using for interacting with the database?
TablePlus!
please make a video on CTE
I shall! In the meantime I've made some here: planetscale.com/learn/courses/mysql-for-developers/queries/common-table-expressions-ctes
what is this tool you use for querying the db?
TablePlus!
Hi Aaron, nice video, as always. Can you make a video about foreign keys constraint, why planetscale not support it, and how to migrate existing database with foreign keys constraint already set to planetscale?
Yes! Good idea.
Weird quirk! I wonder if `MAX(myenum+0)` behaves numerically from the explicit cast.
I've done some wild stuff with enums, taking advantage of their number nature. All well-tested, of course. But I was able to do a visibility check very efficiently with `visibility > privacy` where the two values got treated as numbers. It was a massive speedup and made it possible for the values to have readable names. I can't remember if I needed the +0 for that though. It's been awhile 😂
Woah that is wild. I've never thought of doing that... I love it but also hate it
This was a super interesting video, thanks for sharing! I have an idea for another potential video - Can you walk us through how to properly optimise queries? Let's say you have added indexes, and found that the cost estimations are still waaaay off, What's the next thing you do? How do you find out what the analyze query is doing in more detail?
This is a great idea. Adding to my queue!
I transferred all my DB enums to the API after I had too many issues each migration (using Prisma)
Yup, the requirement to ALTER the table is a pain
Nothing like waking up on a nice sunny, sunday morning, opening my printed mysql docs and reading them docs with a nice hot cup of tea.
imagine reading fiction novels lmao
> imagine reading fiction novels lmao
Couldn't be me
If I ever wanted to do data validation in my database, I would rather add a check constraint for its maintability than an enum, it is a true nightmare to maintain. Does this make sense ? Do you think of other way to do data validation in DB ?
I probably wouldn't add a check constraint for much, personally. I don't like pushing validation down to the DB layer. Enums are kind of a weird case because it's more data validation than business logic validation. As always, depends on the use case!
That is a kind of problem that we spend a lot of time to understand when it happens.
Thanks for help the me of the future 🤖
Haha you're welcome!
The first part didn't surprise me but the MAX part is really weird,
I think the same applies to Sets as well.
I sometimes find it easier to order by FIND_IN_SET(x, 'v1,v2,v3,v4,v5') or any other custom order when the order is subject to change depending on some business logics
Thanks Aaron!
Find in set is a good tip! I might need to do a video on that
@@PlanetScaleThat will be nice,
although I don't use the set column type in favor of json column but I still find it interesting.
Thanks once again Aaron!
I suspect that altering the schema for the enums and inserting a value in the middle of the list will break all the existing numerical values after the new enum. They’ll all be off by 1 I suspect.
Perhaps it’s smart enough to redo the integer to enum mapping.
It will assign new integers if needed, but you shouldn't be relying on those integers for anything. You'd be querying against string values!
@@PlanetScale While the client of the DB doesn’t rely on the integers the DB will.
So if you have this initially:
Foo - 1
Bar - 2
And you insert a record with Bar, the stored numerical value will be 2.
Now you alter the scheme so the enums are: Foo, Buzz, Bar. I.e.
Foo - 1
Buzz - 2
Bar - 3
As the previous record was stored as a numerical 2, wouldn’t it accidentally now be Buzz?
Or I wonder if the alter conmand is smart enough to re-number the stored values. It’s an interesting thing to try.
Could you do "MAX(size+0)" to get back "x-large"? Would that work?
I actually don't know! would be worth trying I suppose
been watching for a while and cant find what software you're using for querying 😭
TablePlus!
Strange to be certain (and the fact that they seem to be 1-indexed), but I've never, in 25 years, needed to use enums for anything other than a constraint on a value being inserted.
I'd rather use a joining table with a seperate table for such enums. This is so much better when you want to dynamically add or remove variants.
This. The INT then becomes a primary key which you have full control over. You can also add more columns for other properties if you need to down the line.
Who would use a max on enums? I would prefer a foreign table on the field, so you can add or remove as many options as you like.
Talk about foreign tables...it would be nice, if there will be a video on that topic too with all the pros and automatisms provided by MySQL for that. Thank you and c ya.
That's certainly an option! Good idea, I'll add some joins to my queue. See ya!
So, do you read MySQL documentation for fun?
For fun and for work (same thing)
Hi Boss
Holly says "Hi junit1606, please like and subscribe." Boss's orders!
Hi! 😀
Advice: use enums in your app, don’t use them in the db
Using them in the database could prevent a faulty app from inserting invalid values into the database, and I think that is a good thing. You should not trust the application software, or its users, so it seems sensible that the database system checks as much as it can.
Yep, definitely use a FK pointing to a beautiful table listing all your allowed values, you get the enum safety plus changing a label is not a nightmare anymore (and your DBA will not cry in Normal Form anymore 😇)
@@gerardvanwilgen9917I dread to imagine just how disfunctional a company has to be for the DB Team to "not trust" the data they get from the app
@@Khalyomedethis is the correct answer
Advice: don’t use enum in your code: use type tables instead. Yes, it’s not compile-time safe, but often even enums in code aren’t because they’re parsed from strings anyway. If you use a language with powerful macros like rust, you can even make enums from the type table itself so you don’t trade compile time safety for single source of truth or extensibility.
the serious flaw to me is that actually, enums have one hidden value: empty.
If you try to insert a enum value that does not exist in that list it will be jsut blank.
According to the docs, this can be avoided only with per-DB configuration.
But overall, this is super weird to me
Yeah I think the integer 0 is reserved for invalid data. Weird to me too
PlanetScale is awesome except for the storage cost. $1.50/gb is nuts.
TL;DR: don't use Enums in database layer :p, that behaviour is too strange and unintuative in my opinion. I guess it'd be a (minor) perfomance hit to use a enum table to reference instead? Or does the DBMS make a table for the Enums in the non-visible part?
I think it's just another tool in the chest. For situations that make sense, it's great. It you have a set number of options in a column that you don't order by, and that table has millions of rows, you save a lot of space by storing just the integer rather than the corresponding text value.
For something like storing, say, authorization mechanism (i.e. 'oauth', 'password', 'token'), enums makes sense since chances are small that you're going to change these often and a schema migration is fine for those cases where it does.
But for things that are more likely to change, such as user roles/permissions, tables would make more sense. Of course it highly depends on your specific use-case.
Unfortunately... it depends 😭 They're nice because you get that bit of validation at the db layer, the tiny storage + indexes, and the readability of a string. But if those things don't matter or you're going to be changing the values a lot, I'd move the enums to the app layer.
Why would I want to do mathematical operations on my enum. I don’t think this issues are valid enough to not use enums. What is the use case for ordering enums. I just don’t see it
Beats me! No idea honestly.
Can I get a copy of your precious MySQL note! 😅
You'll have to pry them out of my hands!
But really, I downloaded chapters 8 & 11 from the website as PDF and got them printed at a local print shop
i thought you were you own boss, hahaha
In a way, I am!
In a much more real way, I'm definitely not.
enumeratrion
Video like that eliminate time for explaining