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.

Share:
Share on Facebook0

Share on LinkedIn0Tweet about this on TwitterShare on Google+0Buffer this pageDigg thisShare on Reddit0

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 *

Most Popular Posts

Django Tutorial for beginners

Posted on 1 year ago

Bhumi

How To set up Signup Bonus in X-Cart

Posted on 5 years ago

Bhumi

How to handle Exception in PHP5

Posted on 2 years ago

Bhumi

MySQL coalesce() function

Updated 17 years ago

Bhumi

How to use JavaScript Promise API?

Updated 17 years ago

Bhumi

What are Lambdas and Closures in PHP?

Updated 17 years ago

Bhumi

An Introduction to Gulp JS

Updated 2 months ago

Bhumi

The Reader’s Poll – June 2015

Posted on 2 years ago

Bhumi

The Reader’s Poll – August 2014

Posted on 3 years ago

Bhumi

The Reader’s Poll – June 2014

Posted on 3 years ago

Bhumi

The Readers’ Poll – May 2014

Posted on 3 years ago

Bhumi

The Readers’ Poll – August 2013

Posted on 4 years ago

Bhumi