9/11/2023 0 Comments Mysql enum![]() ORDER BY with a CAST is the best way to sort by value. The output will be the same as in Figure 3. Result set after casting the ENUM column to CHARįrom the looks of it, the value will be used for sorting. Check out the code below and the output that follows in Figure 3. ENUM values are stored according to their index numbers, not the value. You can also use the string value, like the one below: SELECT * FROM peopleĪND gender = 'Female' 6. So, this will work too: SELECT * from people It used the index to specify the country. In point #4, you saw an example with a WHERE clause to filter with an ENUM column. Filter MySQL Enumeration by Index or String Value ![]() Having a separate table is more flexible and requires nothing from the developer when data is live. And to make a friendlier output like in Figure 2, you will also need a JOIN. More data with an indefinite number of rows and more attributes requires a table. So, should you use MySQL ENUM to avoid JOINs altogether? Definitely not! This is good for a small but fixed list. ,CASE WHEN p.gender = 'M' THEN 'Male' ELSE 'Female' END AS gender Meanwhile, the same output can be achieved when using a separate table and a join. Note: The data you see was generated using dbForge Studio for MySQL’s data generating tool. The friendly output of gender and country columns without a JOIN The error message below occurred because South Korea is not on the enumerated list.įigure 2. VALUES ('Choi', 'Seungcheol', '','Male','South Korea') ![]() So, if you try to do this, the “Data truncated for column ‘country'” error will be thrown: INSERT INTO people (lastname, firstname, middlename, gender, country) Under strict mode, the country column in our example earlier will only accept 12 possible values. MySQL ENUM Limits the Possible Values in a Column To avoid errors, always use a strict server mode.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |