Back to Top

Avoid to use ENUM and SET type in MySQL

Avoid to use ENUM and SET type in MySQL

ENUM and SET are the most controversial types provided by MySQL.ENUM and SET types are the special string types in which values are chosen from the fixed list of values set in the database table.

MySQL supports various data types which we can use as per requirement or data.MySQL provides two data types ENUM and SET types. Both ENUM and SET types allow us to specify a list of possible values for a column with a default value.When defining an ENUM, we are creating a list of items from which the value must be selected or it can be NULL.

Difference between SET and ENUM

The difference between SET and ENUM is that SET column can contain multiple values and whereas an ENUM can hold only one of the possible values.The SET type is similar to ENUM whereas the SET type is stored as a full value rather than an index of a value as with ENUM.

How to set Enum in MySQL

An Enumeration is known as ENUM. It is a column that can take the value from the list of values ​that are enumerated explicitly in the column specification when creating table field.

SYNTAX:

ENUM type should be used if the column values are known in advance. For example, Gender.Each ENUM value has an index.The index value of the empty string is 0.If you pass incorrect values ​​in the column of ENUM, the empty string is inserted, which is the wrong value.

How to use Set in MySQL

A Set is a string type which can have zero or more values, each of which must be selected from a list of values ​​specified when creating a table field. comma separated values used for Set.The display is same like the type ENUM.

MySql stores SET values ​​numerically and SET type support a maximum of 64 different values.

Let’s have a look with very simple table “Person” with the “status” field being an ENUM:

When a field is defined as ENUM or SET, we are using Data edit panels to store fixed data which provided by PHPMyAdmin and values will be displayed in a drop-down​ list.see in below screen

set enum in mysql

Now Let’s see the benefits of such types:

Instead of storing the whole value, MySQL stores only an integer values which use one or two bytes depending on the number of values in the list

MySQL itself refuses value which is not included in the list.

Even after seeing benefits, I am suggesting not to use ENUM and SET types because of the following reasons:

  1. We need more values then we need to change the list of possible values from the database which is more difficult for non programmers.
  2. The storage limits for ENUM types is 65535 possible values in the list and also a SET can allow upto 64 distinct values which are the chosen values in the set.
  3. Sometimes its increase the complexity of program code and complicated to maintain database.

If you know more about ENUM and SET in MySQL, do let me know via comments. If you find this article useful, do consider sharing it on Google Plus and Facebook.

Comments (4)

  1. Thanks for sharing information. quite useful.

  2. […] we see how that demo works let’s check how we create this […]

  3. Is the SET or ENUM type can have an index?
    How about the TINYINT type in mysql? Does it faster than SET/ENUM?
    thanks

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Most Popular Posts