MySQL enum vs. set

Question

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.

Answer 1

I addition to the points already mentioned in the answers so far given I am adding an additional point on the difference -

MySQL stores ENUM string values internally as decimal integers of values 1 through n for a column with n members in the enumeration.

MySQL represents SET string values as a bitmap using one bit per value, thus the values are stored internally as 1, 2, 4, 8, ..... up to 65,535 for a maximum of 64 members.

This point is demonstrated as below -

ENUM example -

I create table table1 with column col1 having data type of ENUM('a','b','c','d','e','f','g','h','i','j') with the following table structure -

| table1 | CREATE TABLE `table1` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `col1` enum('a','b','c','d','e','f','g','h','i','j') DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |

col1 gets internally stored as follows -

+----+---------+---------------+
| id | element | decimal_value |
+----+---------+---------------+
|  1 | a       |             1 |
|  2 | b       |             2 |
|  3 | c       |             3 |
|  4 | d       |             4 |
|  5 | e       |             5 |
|  6 | f       |             6 |
|  7 | g       |             7 |
|  8 | h       |             8 |
|  9 | i       |             9 |
| 10 | j       |            10 |
+----+---------+---------------+

Now, suppose we want to insert the value - 'e' into col1

'e' has the index 5

Thus to enter 'e' into col1 we use the following query -

INSERT INTO table1  VALUES (1, 5);

Then we check the values present in table1 -

SELECT * FROM table1;
+----+------+
| id | col1 |
+----+------+
|  1 | e    |
+----+------+

and we see that in first row we have the value of col1 as 'e'

Again, suppose we want to insert the value - 'i' into col1

'i' has the index 9

Thus to enter 'i' into col1 we use the following query -

INSERT INTO table1  VALUES (2, 9);

Then we check the values present in table1 -

SELECT * FROM table1;
+----+------+
| id | col1 |
+----+------+
|  1 | e    |
|  2 | i    |
+----+------+

and we see that in second row we have the value of col1 as 'i'

Again, suppose we want to insert the value - 'a' into col1

'a' has the index 1

Thus to enter 'a' into col1 we use the following query -

INSERT INTO table1  VALUES (3, 1);

Then we check the values present in table1 -

SELECT * FROM table1;
+----+------+
| id | col1 |
+----+------+
|  1 | e    |
|  2 | i    |
|  3 | a    |
+----+------+

and we see that in third row we have the value of col1 as 'a'

---

SET example -

I create table table1 with column col1 having data type of SET('a','b','c','d','e','f','g','h','i','j') with the following table structure -

| table1 | CREATE TABLE `table1` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `col1` set('a','b','c','d','e','f','g','h','i','j') DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |

col1 gets internally stored as follows -

+----+---------+----------------+---------------+
| id | element | binary_value   | decimal_value |
+----+---------+----------------+---------------+
|  1 | a       | 0000 0000 0001 |             1 |
|  2 | b       | 0000 0000 0010 |             2 |
|  3 | c       | 0000 0000 0100 |             4 |
|  4 | d       | 0000 0000 1000 |             8 |
|  5 | e       | 0000 0001 0000 |            16 |
|  6 | f       | 0000 0010 0000 |            32 |
|  7 | g       | 0000 0100 0000 |            64 |
|  8 | h       | 0000 1000 0000 |           128 |
|  9 | i       | 0001 0000 0000 |           256 |
| 10 | j       | 0010 0000 0000 |           512 |
+----+---------+----------------+---------------+

Now, suppose we want to insert the value - 'e,f,i' into col1

Then the binary_value of 'e,f,i' is calculated by adding binary_values of 'e', 'f' and 'i' which is 0001 0011 0000 and corresponding decimal_value is 304 as shown below -

+----+---------+----------------+---------------+
| id | element | binary_value   | decimal_value |
+----+---------+----------------+---------------+
|  5 | e       | 0000 0001 0000 |            16 |
|  6 | f       | 0000 0010 0000 |            32 |
|  9 | i       | 0001 0000 0000 |           256 |
+----+---------+----------------+---------------+
|    | e,f,i   | 0001 0011 0000 |           304 |
+----+---------+----------------+---------------+

Thus to enter 'e,f,i' into col1 we use the following query -

INSERT INTO table1  VALUES (1, 304);

Then we check the values present in table1 -

SELECT * FROM table1;
+----+-------+
| id | col1  |
+----+-------+
|  1 | e,f,i |
+----+-------+

and we see that in first row we have the value of col1 as 'e,f,i'

Again, suppose we want to insert the value - 'a,j' into col1

Then the binary_value of 'a,j' is calculated by adding binary_values of 'a' and 'j' which is 0010 0000 0001 and corresponding decimal_value is 513 as shown below -

+----+---------+----------------+---------------+
| id | element | binary_value   | decimal_value |
+----+---------+----------------+---------------+
|  1 | a       | 0000 0000 0001 |             1 |
| 10 | j       | 0010 0000 0000 |           512 |
+----+---------+----------------+---------------+
|    | a,j     | 0010 0000 0001 |           513 |
+----+---------+----------------+---------------+

Thus to enter 'a,j' into col1 we use the following query -

INSERT INTO table1  VALUES (2, 513);

Then we check the values present in table1 -

SELECT * FROM table1;
+----+-------+
| id | col1  |
+----+-------+
|  1 | e,f,i |
|  2 | a,j   |
+----+-------+

and we see that in second row we have the value of col1 as 'a,j'

Again, suppose we want to insert the value - 'b,d,h,i' into col1

Then the binary_value of 'b,d,h,i' is calculated by adding binary_values of 'b', 'd', 'h' and 'i' which is 0001 1000 1010 and corresponding decimal_value is 394 as shown below -

+----+---------+----------------+---------------+
| id | element | binary_value   | decimal_value |
+----+---------+----------------+---------------+
|  2 | b       | 0000 0000 0010 |             2 |
|  4 | d       | 0000 0000 1000 |             8 |
|  8 | h       | 0000 1000 0000 |           128 |
|  9 | i       | 0001 0000 0000 |           256 |
+----+---------+----------------+---------------+
|    | b,d,h,i | 0001 1000 1010 |           394 |
+----+---------+----------------+---------------+

Thus to enter 'b,d,h,i' into col1 we use the following query -

INSERT INTO table1  VALUES (3, 394);

Then we check the values present in table1 -

SELECT * FROM table1;
+----+---------+
| id | col1    |
+----+---------+
|  1 | e,f,i   |
|  2 | a,j     |
|  3 | b,d,h,i |
+----+---------+

and we see that in third row we have the value of col1 as 'b,d,h,i'

Answer 2

ENUM --> choose only one of the available values to insert.

(no_null, no_any_duplicate)

---

SET --> choose any combination or single value like a set and insert them into.

(null, individual_values, all_the_available_values_together)

Answer 3

Actually it's pretty simple:

When you define an ENUM('Yes', 'No', 'Maybe') then you must INSERT only one of these values (or their positional index number)

When you define a SET('R', 'W', 'X') then you can INSERT an empty string, or one or more of these values. If you insert something that's not in the predefined set, an empty string is is inserted instead. Note that before inserting all duplicate values are discarded, so only one instance of each permitted value is being inserted.

Hope this clears it up.

Please note that Winbobob's answer is incorrect and contains flawed examples, as when inserting multiple values, the values must be strings, separated with commas. All his inserts are actually inserting just a single value (and last two aren't in the defined 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 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.

Answer 5

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)

Answer 6

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.

Answer 7

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.