What is the difference between SET and ENUM in Mysql?

Question

What are the differences between SET and ENUM in Mysql? And in what situations both are best applied?

Answer 1

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 can have some amazing results unless you have this string/integer duality in mind.

ENUM

The ENUM type is a type of enumeration. 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 shall be equal to 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 list of strings between comma-separated quotes. Internally, Mysql stores strings as integers, using values 1 to n for a column with n members in the enumeration. The declaration insert into paises(nome,continente) values('Kenya','Africa'); assign the enumeration 'Africa' value 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 ENUM columns. For example, if you assign "US A' in the continent column, Mysql will store the value 0 instead of some value from 1 to 7, because 'US A' is not a valid member of the enumeration. If you select the column later, Mysql displays values 0 as '' (the empty string).

SET

The SET data type, such as the ENUM, is declared using a comma-separated list of strings in quotes that define their valid members. However, unlike ENUM, a SET column can be assigned a value consisting of any combination of those members. The following statement contains a list of symptoms displayed by people suffering from allergy:

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

A patient may have any or all (or none) of these symptoms, and therefore the symptom values may contain from zero to four individual members of this SET, separated by commas. The following statements save in the column respectively an empty string (no SET members), a single SET members, 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 SET type columns as a bitmap using one bit per member, so elements in the symptom definition have internal values of 1,2,4 and 8 (i.e., they have bit values 1 through 3 in one byte) Internally, Mysql stores the values shown in previous INSERT declarations as 0 (no connected bit), 4 (connected bit 2) and 9 (connected bit 0 and 3, i.e., 1 plus 8).

A SET definition may 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 up to 8, 16, 24, 32 or 64 members. An ENUM-type column definition can list up to 65,535 members.

If you try to store an invalid member in a SET type column, it is ignored because it does not match any of the bits in the column definition. Poe example, record a symptom value with tosse, espirro, respiração ofegante results in an internal value equal to 1 (sneezing). The elements tosse e respiração ofegante 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 the string and numerical representations of values of the ENUM and SET types can result in surprises if you are not careful. For example, although you would normally refer to a column of enumeration type using the string forms of its 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 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 value of the sibling 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 '2' value 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 an enumeration value equal to '3'. In the second case, you get records where the internal value is 3, i.e., records with enumeration value equal to '2'.

Source: Mysql Study Guide for Certification

Answer 2

TL;DR

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

---

Documentation

According to Mysql documentation ENUM and SET Constraints:

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 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 invalid and are Rejected.

• To 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.

In free translation:

The clauses ENUM and SET 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, "Type SET".

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

• A value ENUM must be one of those listed in the column definition, or its internal numerical equivalent. The value cannot be the value of the error (i.e., 0 or the empty string). For a column defined as ENUM ('a', b', c'), values like ', ’d' or 'Ax' are invalid and are rejected.

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

---

In which situations both are best applied?

The basic difference is that when you set the column as SET, it is possible to store more value from the list. Already in the ENUM only one value can be entered.

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 insertion will be with the column fruta empty since each value can only be inserted 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');