What's the difference between ENUM & SET ?

An ENUM object will allow the list of values declared explicitly in the column specification at
the time of table creation. If you try to insert the value which is not part of enum declared
list then null value will be inserted.

SET datatype also similar to ENUM but the difference is that it will allow combination of values from the
list of assigned string during the table creation.

Look at the example given below. Let's create a test table and explore it..

create table test
(
id integer not null auto_increment primary key,
prod varchar(30) comment 'Product Name',
prod_cat ENUM('Food', 'Cosmetics', 'Sweet', 'Others'),
prod_sub SET('new stock', 'stock', 'old stock', 'stock reorder')
);

-- Inserting some values...
insert into test values(null, 'Horlicks', 'Food', 'new stock');

-- here prod_cat value given as 'newnew', this is not part of the ENUM list hence blank value will be inserted.
insert into test values(null, 'Horlicks', 'newnew', 'new stock');

-- SET : we can have multiple values from the assigned list if the data type declared as SET.
insert into test values(null, 'Sugar', 'Food', 'new stock,old stock');

mysql> select * from test;
+----+----------+----------+---------------------+
| id | prod | prod_cat | prod_sub |
+----+----------+----------+---------------------+
| 1 | Horlicks | Food | new stock |
| 2 | Horlicks | | new stock |
| 3 | Sugar | Food | new stock,old stock |
+----+----------+----------+---------------------+


-- for SET data type, use LIKE / FIND_IN_SET() operators while searching
mysql> select * from test where FIND_IN_SET('old stock', prod_sub) >0;
+----+-------+----------+---------------------+
| id | prod | prod_cat | prod_sub |
+----+-------+----------+---------------------+
| 3 | Sugar | Food | new stock,old stock |
+----+-------+----------+---------------------+

(or)
mysql> select * from test where prod_sub = 'new stock,old stock';
+----+-------+----------+---------------------+
| id | prod | prod_cat | prod_sub |
+----+-------+----------+---------------------+
| 3 | Sugar | Food | new stock,old stock |
+----+-------+----------+---------------------+

These datatypes can be used effectively for the predetermined values, so that we can able to preserve data
integrity. However it depends upon the user requirement.