MySQL ENUM type vs join tables

Question

My requirement

The table should contain a status column.

This column represents one of 5 states.

initial design

I decided that I could just make it an entire column and represent the states using a numerical value.

• 0 = start
• 1 = start
• 2 = crashed
• 3 = paused
• 4 = stopped

Since I do not want my application to support matching of integers with their string description, I plan to put them in a separate state description table (relying on the FK relation).

Then I discovered that MySQL has an ENUM type that exactly matches my requirements. Besides the direct dependency on MySQL, are there any pitfalls using the ENUM type?

Answer 1

• To change a set of values ​​in ENUM, an ALTER TABLE is required which can cause table restructuring - an incredibly expensive operation (table restructuring does not happen if you just add one new value to the end of the ENUM definition, but if you delete one or change the order, it performs table restructuring) . Whereas changing a set of values ​​in a lookup table is as simple as INSERT or DELETE.

• There is no way to associate other attributes with values ​​in ENUM, for example, which ones are deleted and which ones can be added to the drop-down list in your user interface. However, the lookup table may include additional columns for such attributes.

• It is very difficult to query ENUM to get a list of different values, basically requiring you to query the data type definition from INFORMATION_SCHEMA , and parse the list from the returned BLOB. You can try SELECT DISTINCT status from your table, but it only gets the status values ​​that are currently in use, but it may not be all the values ​​in ENUM. However, if you store values ​​in a lookup table, you can easily execute queries, sort, etc.

As you can tell, I'm not a big fan of ENUM. :-)

The same applies to CHECK constraints, which simply compare a column with a fixed set of values. Although MySQL does not support CHECK restrictions in any case.

Update: MySQL 8.0.16 now implements CHECK restrictions .

Answer 2

Here is an article about listing speed comparison . Maybe this gives some hints. IMHO, it should be limited to using in a fixed list of lines ("Yes / No", "Child / Adult"), which with a probability of 99% will not change in the future.

Answer 3

Enumerations in mysql are bad for the reasons explained.
I can add the following fact: Enum does not provide any server side validation. If you insert a row with a value that does not go beyond the definition of an enumeration, you will get a good or NULL value in the database, depending on the NULL ability of the declaration of the enumeration field.

My question about tinyints:
- transfers are limited to 65535 characters
- if you don't need more than 256 values, tinyint will take up less space for each row, and its behavior is much more "predictable".

Answer 4

If you have a lot of data in your database (more data, then you have RAM), and the ENUM values ​​will NEVER change, I would go with ENUM, not a connection. It should be faster.
Think about it, if you combine, you will need the index of your foreign key and the index of your primary key in another table. As Riho said, look at the tests.

Answer 5

The table will be easier to internationalize. But there will also be a class outside the database completely. This type of validation can be difficult to debug when it is not part of the business logic and, as a rule, is not the responsibility of the database staff.

Like optimization, it is probably very premature; but the OP basically offers it as a convenient feature anyway.

See also http://komlenic.com/244/8-reasons-why-mysqls-enum-data-type-is-evil/