MySQL data types: int versus enum

views:

answers:

+2 Q:

MySQL data types: int versus enum

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?

+2 A:

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.

Mchl 2010-10-30 08:50:23

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

Pekka 2010-10-30 08:52:45

@Mchl, 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?

StackOverflowNewbie 2010-10-30 08:53:13

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

f00 2010-10-30 08:55:20

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.

Mchl 2010-10-30 08:58:40

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

Paniyar 2010-10-30 08:53:40

ansaurus

tags:

views:

answers:

MySQL data types: int versus enum

related questions