Question:
What are the differences between SET and ENUM in MySQL? And in which situations are both best applied?
Answer:
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 internally represents them as integer values. This leads to very efficient storage, but it can have some surprising results unless you keep this string/integer duality in mind.
ENUM
The ENUM type is an enumeration type. A column definition of this type includes a list of allowable 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')
The values in the ENUM type definition are given in the form of a comma-separated list of quoted strings. Internally, MySQL stores strings as integers, using the values 1 to n for a column with n members in the enumeration. The declaration insert into paises(nome,continente) values('Kenya','Africa'); 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 'USA' to the continents column, MySQL will store the value 0 instead of some value from 1 to 7, because 'USA' is not a valid member of the enumeration. later, MySQL prints 0 values as '' (the empty string).
SET
The SET data type, like ENUM, is declared using a comma-separated list of quoted strings that define its valid members. However, unlike ENUM, a given column of type SET can 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')
A patient can have any or all (or none) of these symptoms, and the symptom values therefore can contain zero to four individual members of this SET, separated by commas. The following statements write to the column, respectively, an empty string (there are no SET members), a single SET member, and multiple SET members:
INSERT INTO alergia (sintoma) Values('');
INSERT INTO alergia (sintoma) Values('cabeça constipada');
INSERT INTO alergia (sintoma) Values('espirro', 'olhos vermelhos');
MySQL represents columns of type SET 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 a byte ) Internally, MySQL stores the values shown in the previous INSERT statements as 0 (no bits on), 4 (bit 2 on), and 9 (bits 0 and 3 on, that is, 1 plus 8).
A SET definition can contain 64 members. The internal storage required for the 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. A column definition of type ENUM can list). up to 65,535 members.
If you try to store an invalid member in a column of type SET, it is ignored because it doesn't match any of the bits in the column definition. For example, recording a symptom value with tosse, espirro, respiração ofegante results in an internal value of 1 (sneeze). The elements tosse e respiração ofegante are ignored as 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 values of types ENUM and SET can result in surprises if you're not careful. For example, although you would normally refer to an enumeration type column using the string forms of its values, you can also use the built-in numeric values. The effect of this can be very subtle if 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 strings '0', '1', '2', '3', '>3' , and the internal numeric values are 1,2,3,4, and 5, respectively. Now suppose you execute the following statement:
INSERT INTO t (idade, irmãos) VALUES (14, '3');
The sibling column value 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 a value in the enumeration equal to '2'.
Source: MySQL Study Guide for Certification