Menu
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?
217
MySQL ENUM data type contains the following advantages: Compact data storage where the column may have a limited set of specified possible values. Here, the string values automatically used as a numeric index. It allows readable queries and output because the numbers can be translated again to the corresponding string.
An ENUM is a string object with a value chosen from a list of permitted values that are enumerated explicitly in the column specification at table creation time.
In MySQL, an ENUM is a string object whose value is chosen from a list of permitted values defined at the time of column creation. The ENUM data type provides the following advantages: Compact data storage. MySQL ENUM uses numeric indexes (1, 2, 3, …) to represents string values.
Using the Code. First of all, YES, we can assign an Enum to something else, a char !
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.
136
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
20
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
