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.

No adv/disadv. You should choose one of them depending on your requirements. — ravnur, Feb 10 '13 at 12:02
The two types are unrelated. You might as well ask which is better - int or text? — Bohemian, Feb 10 '13 at 14:20

score 284 · Answer 1 · answered Aug 14 '15 at 20:03

284

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)

answered Aug 14 '15 at 20:03

Brad Kent

4,982
3
22
26

kya baat hai. simple explanation – VishalParkash May 04 '23 at 09:16

score 79 · Accepted Answer · edited Feb 18 '13 at 17:21

79

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.

edited Feb 18 '13 at 17:21

Brad Larson

170,088
45
397
571

answered Feb 10 '13 at 14:02

user2001117

3,727
1
18
18

31

NOTE! A value of `(a, b, c, d)` will not be rejected. Just the `d` will be rejected thus resulting in the value `(a, b, c)`. – Itay Grudev Apr 02 '14 at 06:53
6

This might be the official documentation, but it is still a very confusing way of describing them. Based on other answers the difference becomes clear. SET is an array of ENUM, where each value consists of 0 or more enum style values. – Jonathon Apr 12 '15 at 23:08
11

This is not a good answer. Clear explanation of differences and dis/advantages was asked for; the official documentation, of which this answer is a mere copy, fails doing so. – Smuuf Mar 30 '16 at 13:20
2

(a, b, c, d) will be rejected if Strict mode is on – Chinmay Sep 07 '16 at 00:40
2

This is not the answer. – Amit Shah Dec 20 '18 at 05:29
MySQL documentation anyone can search google, what's the use of copy pasting some documentation without contributing something original? – Payel Senapati Sep 24 '21 at 19:58

score 48 · Answer 3 · edited Oct 13 '14 at 10:00

48

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.

edited Oct 13 '14 at 10:00

droidlabour

567
2
8
21

answered Nov 28 '13 at 08:19

Abhishek Singh

943
8
14

score 27 · Answer 4 · edited May 02 '17 at 02:03

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.

Harly Hallikas · Answer 5 · 2018-08-22T05:47:33.847

11

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)

edited Aug 22 '18 at 05:47

answered Aug 22 '18 at 05:40

Harly Hallikas

610
4
13

I'm not exactly sure what's wrong in Winbobob's answer. Maybe you misunderstood it. – csabinho Dec 22 '22 at 08:42
Respectfully, I and 11 other upvote[r]s would like to disagree with you. – Harly Hallikas Dec 24 '22 at 02:53
1

Respectfully I'd like to ask you whether you thought about that those upvotes could be about your other paragraphs? Also, I don't see any respect in ignoring my question. – csabinho Dec 24 '22 at 15:39
Your question? Where? – Harly Hallikas Dec 25 '22 at 21:18

Mir Man · Answer 6 · 2021-01-06T14:43:22.890

1

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)

edited Jan 06 '21 at 14:43

answered Jan 06 '21 at 14:36

Mir Man

21
2

score 1 · Answer 7 · answered Sep 26 '21 at 08:07

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'

MySQL enum vs. set

7 Answers7

Linked