enum or char(1) in MySQL

Question

Sometimes I am not sure whether using enum or char(1) in MysQL. For instance, I store statuses of posts. Normally, I only need Active or Passive values in status field. I have two options:

// CHAR
status char(1);

// ENUM (but too limited)
status enum('A', 'P');

What about if I want to add one more status type (ie. Hidden) in the future? If I have small data, it won't be an issue. But if i have too large data, so editing ENUM type will be problem, i think.

So what's your advice if we also think about MySQL performance? Which way I would go?

Answer 1

Neither. You'd typically use tinyint with a lookup table

• char(1) will be slightly slower because comparing uses collation

• confusion: As you extend to more than A and P

• using a letter limits you as you add more types. See last point.

• every system I've seen has more then one client eg reporting. A and P have to resolved to Active and Passive for in each client code

• extendibility: add one more type ("S" for "Suspended") you can one row to a lookup table or change a lot of code and constraints. And your client code too

• maintenance: logic is in 3 places: database constraint, database code and client code. With a lookup and foreign key, it can be in one place

• Enum is not portable

On the plus side of using a single letter or Enum

Note: there is a related DBA.SE MySQL question about Enums. The recommendation is to use a lookup table there too.

Answer 2

You can use

status enum('Active', 'Passive');

It will not save a string in the row, it will only save a number that is reference to enum member in the table structure, so the size is the same but its more readable than char(1) or your enum.
Editing enum is not a problem no matter how big your data is