MySQL enum vs. set

For MySQL Data type of "enum" and "set" what are the differences and advantages and disadvantages of using one versus the other?

Example data type:

• enum('A', 'B', 'C')
• set('A', 'B', 'C')

The only difference that I am aware of is that ENUM only allows one value to be selected versus SET allows multiple values to be selected.

Solution 1:

analogy:
ENUM = radio fields (only accepted values are those listed, may only choose one)
SET = checkbox fields (only accepted values are those listed, may choose multiple)

Solution 2:

As the MySQL documentation states:

Definition of a ENUM or SET column does act as a constraint on values entered into the column. An error occurs for values that do not satisfy these conditions:

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.

Solution 3:

Enum and Set totally depends on requirements, like if you have a list of radio button where only one can be chosen at a time, use Enum. And if you have a list of checkbox where at a time more then one item can be chosen, use set.

Solution 4:

CREATE TABLE setTest(
  attrib SET('bold','italic','underline')
);

INSERT INTO setTest (attrib) VALUES ('bold');
INSERT INTO setTest (attrib) VALUES ('bold,italic');
INSERT INTO setTest (attrib) VALUES ('bold,italic,underline');

You can copy the code above and paste it in mysql, and you will find that SET actually is a collection. You can store each combine of attributes you declare.

CREATE TABLE enumTest(
 color ENUM('red','green','blue')
);

INSERT INTO enumTest (color) VALUES ('red');
INSERT INTO enumTest (color) VALUES ('gray');
INSERT INTO enumTest (color) VALUES ('red,green');

You can also copy the code above. And you will find that each ENUM actually can only be store once each time. And you will find that the results of last 2 lines will both be empty.