ENUM data type is used in the MySQL database table to select any one value from the predefined list. The value of a particular field can be restricted by defining the predefined list as the field which is declared as ENUM will not accept any value outside the list.
The SET data type is used to select one or more or all values from the predefined list. This data type can also be used to restrict the field for inserting only the predefined list of values like ENUM.
An ENUM value must be one of those listed in the column definition, or the internal numeric equivalent thereof. The value cannot be the error value (that is, 0 or the empty string). For a column defined as ENUM('a','b','c'), values such as '', 'd', or 'ax' are illegal and are rejected.
A SET value must be the empty string or a value consisting only of the values listed in the column definition separated by commas. For a column defined as SET('a','b','c'), values such as 'd' or 'a,b,c,d' are illegal and are rejected.
Example: First create new table using below command:
CREATE TABLE clients (id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
membership ENUM('Silver', 'Gold', 'Diamond'),
interest SET('Movie', 'Music', 'Concert'));
Now use below command to insert data:
INSERT INTO Shopping.clients (name, membership,interest) VALUES ('Ram','Silver', 'Movie,Music');
this will return no error and it will store Movie,Music in interests column.
Now use below command to insert data:
INSERT INTO Shopping.clients (name, membership,interest) VALUES ('Ram','Silver,Gold', 'Movie,Music');
This will return error:
ERROR 1265 (01000): Data truncated for column 'membership' at row 1