CODEWITHSUNDEEP
mysqlperformance
kuzey beytar
kuzey beytar

Reputation: 3227

enum or char(1) in MySQL

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?

Upvotes: 6

Views: 6486

Answers (4)

Dave Rix
Dave Rix

Reputation: 1679

I would use a binary SET field for this, but without labelling the options specifically within the database. All the "labelling" would be done within your code, but it does provide some very flexible options.

For example, you could create a SET containing eight "options" such as;

`column_name` SET('a', 'b', 'c', 'd', 'e', 'f', 'g', 'h') NOT NULL DEFAULT ''

Within your application, you can then define the 'a' as denoting "Active" or "Passive", the 'b' can denote "Hidden", and the rest can be left undefined until you need them.

You can then use all sorts of useful binary operations on the field for instance you could extract all those which are "Hidden" by running;

WHERE `column_name` & 'b'

And all those which are "Active" AND "Hidden" by running;

WHERE `column_name` & 'a' AND `column_name` & 'b'

You can even use the LIKE and FIND_IN_SET operators to do even more useful queries.

Read the MySQL documentation for further information;

http://dev.mysql.com/doc/refman/5.1/en/set.html

Hope it helps!

Dave

Upvotes: 1

gbn
gbn

Reputation: 432230

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.

Upvotes: 6

Daniel
Daniel

Reputation: 31579

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

Upvotes: 2

lanzz
lanzz

Reputation: 43168

Hard to tell without knowing the semantics of your statuses, but to me "hidden" doesn't seem like an alternative to "active" or "passive", i.e. you might want to have both "active hidden" and "passive hidden"; this would degenerate with each new non-exclusive "status", it would be better to implement your schema with boolean flags: one for the active/passive distinction, and one for the hidden/visible distinction. Queries become more readable when your condition is "WHERE NOT hidden" or "WHERE active", instead of "WHERE status = 'A'".

Upvotes: 0

Related Questions