MySQL data types: int versus enum

Question

I have a number of columns that only need to store a few values (e.g. 0, 1, 2, 3). What datatype should I pick for such a case? I feel like I should pick something like ENUM('0', '1', '2'). Would int be better (despite it being less restrictive)? Should I consider something else (like tinyint)?

EDIT:

Actually, what general advice should I consider when deciding on a data type?

General advice? Always pick the right one :)

Pekka
– Pekka

2010-10-30 08:51:53 +00:00
Commented Oct 30, 2010 at 8:51 — Pekka
– Pekka, Commented Oct 30, 2010 at 8:51
You mean the one on the right? :P

Mchl
– Mchl

2010-10-30 08:59:28 +00:00
Commented Oct 30, 2010 at 8:59 — Mchl
– Mchl, Commented Oct 30, 2010 at 8:59

Mchl · Accepted Answer · 2010-10-30 08:50:23Z

5

If you want to restrict it to these 3 values, then indeed ENUM might be the best.

If however, there is a possibility that in future, more values might be needed, then TINYINT UNSIGNED is probably a better solution.

answered Oct 30, 2010 at 8:50

Mchl

62.4k9 gold badges121 silver badges121 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Pekka Over a year ago

Yup. Even with millions of records, the amount of data saved by using an ENUM over an INT/TINYINT is minuscule. Usually, better use the latter

StackOverflowNewbie Over a year ago

why would ENUM be better than int or tinyint? Is it because of the restriction of data that can be stored (i.e. better data integrity)? Does it have anything to do with storage requirements? Or speed? Or something else?

Jon Black Over a year ago

enum values are stored as indexes and require 2 bytes (smallint unsigned) hence you can have a maximum of 65,535 enum elements.

Mchl Over a year ago

Yes, the main advantage of ENUM here, is restricting the set of allowed values (since we don't have column constraints in MySQL yet). Performance-wise, TINYINT will probably be faster.

ArK · Accepted Answer · 2010-10-30 08:53:40Z

1

Using enum with fixed set is advisable. If you want to extend then it is 'Alter' basically schema change which should be avoided.

Find better idea about choosing datatype

and get comparison of enum with data types

answered Oct 30, 2010 at 8:53

ArK

21.1k67 gold badges113 silver badges136 bronze badges

Collectives™ on Stack Overflow

MySQL data types: int versus enum

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related