Enum or char (1) in MySQL

Question

Sometimes I'm not sure if enum or char (1) is used in MysQL. For example, I store message statuses. Usually I only need Active or Passive in the status field. I have two options:

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

And if I want to add another type of status (i.e. Hidden ) in the future? If I have small data, this is not a problem. But if I have too much data, so editing type ENUM will be a problem, I think.

So what is your advice if we also think about MySQL performance? Which direction will I go?

Answer 1

None. Usually you use tinyint with a lookup table.

• char (1) will be a bit slower as comparison uses matching

• confusion: as you expand to more than A and P

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

• Each system that I saw has more than one client, for example, reports. A and P must be enabled for Active and Passive for each client code.

• extensibility: add another type ("S" for "Suspended"), you can use one row in the lookup table or change a lot of code and restrictions. And your client code too

• service: the logic is in 3 places: database restriction, database code and client code. Using a foreign key and a foreign key, it can be in one place.

• Enum is not tolerated

On the plus side of using a single letter or enum

Note: There is a related DBA.SE MySQL question about Enums . It is also recommended that you use a lookup table.

Answer 2

you can use

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

It will not save the string in the string, it will save only the number that refers to the enumeration element in the table structure, so the size is the same, but more readable than char(1) or your enum .
Editing an enumeration is not a problem no matter how large your data is.

Answer 3

I would use a binary SET field for this, but without marking options specific to the database. All "marking" will be done inside your code, but it provides some very flexible options.

For example, you can create a SET containing eight “options”, for example:

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

In your application, you can define "a" to mean "Active" or "Passive", "b" can mean "Hidden", and the rest can be left undefined until you need them.

Then you can use all sorts of useful binary operations in this field, for example, you can extract everything that is "hidden" by running;

 WHERE `column_name` & 'b' 

And all those that are "active" and "hidden" are launched;

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

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

For more information, read the MySQL documentation;

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

Hope this helps!

Dave

Answer 4

It is difficult to say, not knowing the semantics of your statuses, but for me, "hidden" does not seem to be an alternative to "active" or "passive", i.e. you may 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 scheme with Boolean flags: one for active / passive difference and one for hidden / visible differences: Queries become more readable when your condition is "WHERE NOT hidden" or "WHERE active" instead of "WHERE status =" A ".