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:
1 2 3 | ENUM(value1, ...valueN) 1 or 2 bytes Up to 65535 distinct values. |
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.
1 2 3 | SET(value1,... valueN) Up to 8 bytes Up to 64 distinct values. |
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:
1 2 3 4 5 6 7 8 9 | CREATE table person ( `id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY , `FirstName` VARCHAR( 255 ) NULL DEFAULT NULL , `LastName` VARCHAR( 255 ) NULL DEFAULT NULL, `status` ENUM('active','inactive') NOT NULL, |
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
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:
- We need more values then we need to change the list of possible values from the database which is more difficult for non programmers.
- 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.
- 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)