CODEWITHSUNDEEP
mysqlenums
kanchan
kanchan

Reputation: 359

enum('active','inactive') vs enum(0,1)

I want to store users' status in db. status column is enum('active','inactive') or enum(0,1).

I am never gonna allow inactive users use my application. There is no need to make a join here or displayin active and inactive users ever. In short, no need to show users' status anywhere but only use them. I can store it as enum(0,1) but I just wanted to know is there any optimization concern if I chose to use enum('active','inactive') enum('active','inactive') is internally mapped in mysql but still I am curious to know if string can make any difference.

Upvotes: 0

Views: 2978

Answers (1)

Angelin Nadar
Angelin Nadar

Reputation: 9300

  1. enum is a string object internally mapped to integer. It will 1 or 2 bytes depending on the number of enumeration values. Here , you have only 'active' or 'inactive'. So, no space issue .

  2. Getting list of distinct ENUM members would be pain.

    Eg: you cant dynamically generate the drop down list of status by just select * from roles, if you would have maintained in a table

  3. Changing the member list of ENUM is expensive

    ALTER TABLE ENUM('active','inactive') to ENUM('active','disabled') MySQL needs to rebuild your table and look through every record to check for the now-invalid value 'inactive' and would be bad for millions of records

  4. When you use enum, you have to note you can no longer add any attribute or related info to the roles

Upvotes: 2

Related Questions