MySQL 枚举与集合

MySQL enum vs. set

提问by tfont

For MySQL Data type of "enum" and "set" what are the differences and advantages and disadvantages of using one versus the other?

对于“枚举”和“集合”的 MySQL 数据类型，使用一种与另一种的区别和优缺点是什么？

Example data type:

示例数据类型：

• enum('A', 'B', 'C')
• set('A', 'B', 'C')

• 枚举（'A'，'B'，'C'）
• 设置（'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.

我知道的唯一区别是 ENUM 只允许选择一个值，而 SET 允许选择多个值。

采纳答案by user2001117

As the MySQL documentationstates:

正如MySQL 文档所述：

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.

ENUM 或 SET 列的定义确实作为对输入到该列中的值的约束。不满足以下条件的值会发生错误：

ENUM 值必须是列定义中列出的值之一，或其内部数字等效值。该值不能是错误值（即 0 或空字符串）。对于定义为 ENUM('a','b','c') 的列，诸如 ''、'd' 或 'ax' 之类的值是非法的并被拒绝。

SET 值必须是空字符串或仅由列定义中以逗号分隔的值组成的值。对于定义为 SET('a','b','c') 的列，诸如 'd' 或 'a,b,c,d' 之类的值是非法的，会被拒绝。

回答by Brad Kent

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)

类比：
ENUM = 单选字段（仅接受值是列出的值，只能选择一个）
SET = 复选框字段（仅接受值是列出的值，可以选择多个）

回答by Abhishek Singh

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.

Enum 和 Set 完全取决于要求，例如如果您有一个单选按钮列表，一次只能选择一个，请使用 Enum。如果您有一个复选框列表，其中一次可以选择多个项目，请使用 set。

回答by Winbobob

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 SETactually is a collection. You can store each combine of attributes you declare.

你可以把上面的代码复制粘贴到mysql中，你会发现SET其实是一个集合。您可以存储您声明的每个属性组合。

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 ENUMactually can only be store once each time. And you will find that the results of last 2 lines will both be empty.

也可以复制上面的代码。而且你会发现每个ENUM实际上每次只能存储一次。你会发现最后两行的结果都是空的。

回答by Harly H.

Actually it's pretty simple:

其实很简单：

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

当您定义ENUM('Yes', 'No', 'Maybe') 时，您必须仅插入这些值之一（或它们的位置索引号）

When you define a SET('R', 'W', 'X')then you canINSERT an empty string, orone 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.

当您定义SET('R', 'W', 'X') 时，您可以插入一个空字符串，或者这些值中的一个或多个。如果您插入的内容不在预定义集中，则会插入一个空字符串。请注意，在插入之前会丢弃所有重复值，因此每个允许值仅插入一个实例。

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)

请注意，Winbobob 的答案是不正确的，并且包含有缺陷的示例，因为在插入多个值时，这些值必须是字符串，用逗号分隔。他的所有插入实际上只插入一个值（最后两个不在定义的集合中）