TL; DR

SET allows you to save a collection. ENUM allows only unit values.

Documentation

According to the MySQL documentation ENUM and SET Constraints ENUM > :

  

ENUM and SET columns provide an efficient way to define columns that can contain only a given SET of values. See Section 11.4.4, "The ENUM Type", and Section 11.4.5, "The SET Type".

     With strict mode enabled (see Section 5.1.8, "Server SQL Modes"), the definition of a ENUM or SET column acts 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 can not 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 invalid 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 invalid and are rejected.

    

Free translation:

  

The ENUM and SET clauses provide an efficient way to define columns that can contain only a certain set of values. See Section 11.4.4, "The ENUM Type," and Section 11.4.5, "SET Type."

     With strict mode enabled ( strict mode ) (see Section 5.1.8, "SQL Server Modes"), setting a ENUM or SET clause acts as a constraint on the values entered in the column . An error occurs for values that do not satisfy these conditions:

     

• A ENUM value must be one of those listed in the column definition, or its internal numeric equivalent. The value can not be the value of the error (that is, 0 or the empty string). For a column defined as ENUM ('a', 'b', 'c'), values like '', 'd', or 'x' are invalid and are rejected. "

    

• A SET value must be the empty string or a value that consists only of the values listed in the comma-separated column definition. For a column defined as SET ('a', 'b', 'c'), values such as 'd' or 'a, b, c, d' are invalid and are rejected. >   

  

In which situations are both applied in the best way?

The basic difference is that when you set the column to SET , you can store more than value from the list. In ENUM , you can only enter a value.

In the case below the records will be included normally, since isolated values or the combination of attributes can be used:

CREATE TABLE teste_set (
  fruta SET('banana', 'laranja', 'abacaxi')
);

INSERT INTO teste_set (fruta) VALUES ('banana');
INSERT INTO teste_set (fruta) VALUES ('banana,laranja');

In the code below the second insert will have the column fruta empty since each value can only be entered once:

CREATE TABLE teste_enum (
  fruta ENUM('banana', 'laranja', 'abacaxi')
);

INSERT INTO teste_enum (fruta) VALUES ('banana');
INSERT INTO teste_enum (fruta) VALUES ('banana,laranja');

SET and ENUM are used when the values to be stored are chosen from a fixed set of values. You define columns of both types in terms of string values, but MySQL represents them internally as integer values. This leads to very efficient storage, but may have some surprising results, unless you have in mind this string / integer duality.

ENUM

The ENUM type is an enumeration type. A column definition of this type includes a list of allowed values, each of which is called a "member" of the list. Each value stored in the column must equal one of the values in the list.

ENUM ('Asia', 'Europe', 'USA', Africa ',' Oceania ', Antarctica', 'South America')

Values in the ENUM type definition are given in the form of a comma-separated list of strings. Internally, MySQL stores strings as integers, using values 1 through n for a column with n members in the enumeration. The insert into paises(nome,continente) values('Kenya','Africa'); statement assigns the 'Africa' value of the enumeration to the continent column. MySQL actually assigns the value 4, because 'Africa' is the fourth continent name listed in the enumeration definition.

MySQL reserves the value 0 as an implicit member of all columns of type ENUM. For example, if you assign "US" in the continent column, MySQL will store a value of 0, instead of some value from 1 to 7, because 'US' is not a valid enumeration member. later, MySQL displays values of 0 as '' (the empty string).

SET

The SET data type, such as ENUM, is declared using a comma-separated list of strings that define its valid members. However, unlike ENUM, a determinate SET-type column may be assigned a value consisting of any combination of these members. The following statement contains a list of symptoms exhibited by allergy sufferers:

SET ('espirro','nariz entupido', ' cabeça constipada', olhos vermelhos')

INSERT INTO alergia (sintoma) Values('');
INSERT INTO alergia (sintoma) Values('cabeça constipada');
INSERT INTO alergia (sintoma) Values('espirro', 'olhos vermelhos');

MySQL represents SET columns as a bitmap using one bit per member, so elements in the symptom definition have internal values of 1,2,4 and 8 (that is, they have the values of bits 1 through 3 in one byte) Internally, MySQL stores the values shown in the previous INSERT statements as 0 (no bit on), 4 (bit 2 on), and 9 (bits 0 and 3 on, ie 1 plus 8). >

A SET setting can contain 64 members. The internal storage required for SET values depends on the number of elements (1,2,3,4 or 8 bytes for sets of up to 8, 16, 24, 32, or 64 members. up to 65,535 members.

If you try to store an invalid member in a column of type SET, it is ignored because it does not match any of the bits in the column definition. For example, writing a symptom value with tosse, espirro, respiração ofegante results in an internal value equal to 1 (sneeze). tosse e respiração ofegante elements are ignored because they are not listed in the column definition as valid members.

As mentioned in the first paragraph of this answer , converting between string and numeric representations of ENUM and SET types can lead to surprises if you are not careful. For example, although you would normally refer to a column of type enumeration using the strings forms of their values, you can also use the internal numeric values. The effect of this can be very subtle if the string values look like numbers. Suppose you define a table t as follows

CREAT TABLE t (idade INT, irmãos ('0', '1', '2', '3', '>3'));

In this case, the enumeration values are the '0', '1', '2', '3', '>3' strings, and the internal numeric values are 1,2,3,4 and 5, respectively. Now suppose you run the following statement:

INSERT INTO t (idade, irmãos) VALUES (14, '3');

The value of the siblings column is specified here as the string '3', and this is the value assigned to the column in the new record. However, you can also specify the sibling value as a number, as follows:

INSERT INTO t (idade, irmãos) VALUES (14, 3);

However, in this case, 3 is interpreted as the internal value, which corresponds to the value '2' of the enumeration! The same principle applies to recoveries. Consider the following two statements:

SELECT * FROM t WHERE  irmãos = '3';

SELECT * FROM t WHERE  irmãos = 3;

In the first case, you get records that have a value in the enumeration equal to '3'. In the second case, you get records where the internal value is 3, that is, records with enumeration value equal to '2'.

Source: MySQL Certification Study Guide