MySQL enumeration and set

Question

For the MySQL data type “enum” and “set”, what are the differences and advantages and disadvantages of using one and the other?

Example data type:

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

The only difference that I know of is that ENUM only allows you to select a single value compared to SET to select multiple values.

Answer 1

The MySQL documentation says:

The definition of an ENUM or SET column acts as a restriction on the values ​​entered in the column. An error has occurred for values ​​that do not satisfy the following conditions:

The ENUM value must be one of the values ​​specified in the column definition, or an internal digital equivalent. A value cannot be an error value (that is, 0 or an empty string). For a column defined as ENUM ('a', 'b', 'c'), values ​​such as '', 'd' or 'ax' are illegal and are rejected.

The SET value must be an empty string or a value consisting only of the values ​​specified 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 rejected.

Answer 2

analogy:
ENUM = radio fields (only the accepted values ​​are listed, only one can be selected)
SET = (only accepted values ​​are listed, can select several)

Answer 3

Enum and Set are completely dependent on the requirements, for example, if you have a list of radio buttons where only one can be selected at a time, use Enum. And if you have a list of checkboxes where you can select one item at a time, use set.

Answer 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 into mysql, and you will find that SET is actually a collection. You can save every combined attribute that 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 can actually be stored only once. And you will find that the results of the last two lines will be empty.