MYSQL ENUM

Why ENUM?

ENUM provides a way to provide an attribute for a row. Attributes with a small number of non-numeric options work best. Examples:

reply ENUM('yes', 'no')
gender ENUM('male', 'female', 'other', 'decline-to-state')

The values are strings:

INSERT ... VALUES ('yes', 'female')
SELECT ... --> yes female

VARCHAR as an alternative

Let's say we have

type ENUM('fish','mammal','bird')

An alternative is

type VARCHAR(20) COMENT "fish, bird, etc"

This is quite open-ended in that new types are trivially added.

Comparison, and whether better or worse than ENUM:

.(same) INSERT: simply provide the string

.(worse?) On INSERT a typo will go unnoticed

.(same) SELECT: the actual string is returned

.(worse) A lot more space is consumed

Adding a new option

ALTER TABLE tbl MODIFY COLUMN type ENUM('fish','mammal','bird','insect');

Notes

.As with all cases of MODIFY COLUMN, you must include NOT NULL, and any other qualifiers that originally
existed, else they will be lost.

.If you add to the end of the list and the list is under 256 items, the ALTER is done by merely changing the
schema. That is there will not be a lengthy table copy. (Old versions of MySQL did not have this optimization.)

Conclusion

In this page (written and validated by A. Gawali) you learned about MYSQL ENUM . What's Next? If you are interested in completing MYSQL tutorial, your next topic will be learning about: MYSQL Install container with Docker Compose.

Incorrect info or code snippet? We take very seriously the accuracy of the information provided on our website. We also make sure to test all snippets and examples provided for each section. If you find any incorrect information, please send us an email about the issue: mockstacks@gmail.com.

Share On:

Mockstacks was launched to help beginners learn programming languages; the site is optimized with no Ads as, Ads might slow down the performance. We also don't track any personal information; we also don't collect any kind of data unless the user provided us a corrected information. Almost all examples have been tested. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. By using Mockstacks.com, you agree to have read and accepted our terms of use, cookies and privacy policy.