What is the advantage of using the ENUM type?

Asked

Viewed 11,055 times

21

When I should use the guy ENUM because even today where I saw this guy being used he could be replaced by VARCHAR or even for a simple CHAR, I can’t see a case where in it it really becomes necessary? A practical example would be useful.

mysql database typing enums attributes

2 answers

16


In general, little. First of all the ENUM is used as a form of normalization. So using a similar mechanism might be useful.

If you have a list of data items that can be chosen exclusively, it can be highly interesting to have in your table only one code, an ID that identifies one of them. The description of it can be changed at some point, you may need other associated information, anyway, if you know how to do the normalization you know you should not put the description of the item, not only because it takes more space, it is a matter of canonicality.

Using this form is a good thing and should only be avoided if there is an important reason, if you need the most performance and know the risks and what you are doing.

The question is to use the ENUM to replace a related table. In general there are only disadvantages. Prefer the lookup Tables described above.

  • Read the items from ENUM is a more complicated process than it should and is slow. It is not a common datum.
  • Change the ENUM itself requires tampering with the tables that use it, among other problems. It is quite true that a ENUM should only be used if you know it should never be changed.
  • Can’t add extra data beyond the description.
  • It cannot be internationalized.
  • It is an element belonging to the table where it is used, ie it is not a public member of the base and can not be reused, which practically kills the advantage of canonization of information.
  • It’s not so simple to use it properly.

I didn’t even mention the portability problem because this is something that doesn’t seem to be a problem in this question.

The advantage is that it takes less space and you get the advantages of normalization, of separating the details of the data, but as said before, it has better techniques.

Obviously I’m not going to show you an example of how to use it. I’m just going to say to create tables that list this. It has two ways of using: either you create a table for each "enumeration" you need; or if you know that you only need the description or other data fields that will be used in all enumerations, then make an enumeration table with an extra column identifying which is the enumeration that that entry refers to.

Alternative

If you make several tables, essentially it will have a ID or code as the primary key and a column with the description. You can have others as needed.

The consumer tables of these enumerations will have a column with the ID to relate to the respective enumeration. Eventually you can put a foreign key to facilitate the automatic relationship. Not everyone likes to do this. Obviously you will need to do JOINs to get the description.

In some cases you may only have a number in the consumer tables and not have the table with the description, leave it to the application to treat this. It is not the most appropriate from the point of view of the database, but it is still a viable option in many cases even if it prefers to leave part of the decisions for the application.

If you use a VARCHAR and describe right there, possibly with a CHECK to limit what can be used, is it wrong? No, the normalization is lost, it has some disadvantages. But it may be suitable for you and is usually more advantageous than the ENUM, at least it does not offer more disadvantages than it.

  • 3

    The difference is in the use of memory... the ENUM, represents a collection of positions, ENUM('a', 'b','c') = position 1, position 2, position 3, it stores only one value in the position, and not in the field, which would occupy much more space.

  • 2

    Then the Enum is a "decoy" for gambiarras?

  • 1

    @Wallacemaxters used to be.

0

Well, first we have the storage requirements .

ENUM uses 1 byte (if it is below 255 values) or 2 bytes (up to 65,535 maximum) Tinyint wave 1 byte (maximum of 255 values) Boolean is a synonym for Tinyint

So on the surface, they’re all the same. ENUM takes some metadata for the string value associated with it.

So as you add more values, any advantage starts to drift away from the ENUM. Especially if you add the values after the table is already in use, because it is necessary to change the table structure to accommodate.

What is the advantage of using a ENUM ? A sequence representation of what value means. The importance of this depends on its application.

  • Below is a link that shows 8 reasons why the use of ENUM is bad.

8 reasons why the use of ENUM is bad

Browser other questions tagged mysql database typing enums attributes

You are not signed in. Login or sign up in order to post.