blocks|key|2827194|text|正如MySQL+documentation所说的：|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|2827195|ENUM或SET列的|2827196|定义对输入到该列中的值起到约束作用。如果值不满足以下条件，则会出现错误：|blockquote|2827197|2827198|ENUM值必须是列定义中列出的值之一，或者是其内部数字等效值。该值不能为错误值(即0或空字符串)。对于定义为ENUM('a'，'b'，'c')的列，'‘、'd’或'ax‘等值是非法的，因此会被拒绝。|2827199|设置值必须是空字符串或仅由列定义中列出的值组成的值，用逗号分隔。对于定义为SET('a'，'b'，'c')的列，诸如'd‘或'a，b，c，d’之类的值是非法的，因此会被拒绝。|2827200|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.6/en/constraint-enum.html^0|2|J|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|V|8|@]|9|@$A|W|B|X|1|Y]]|C|$]]|$1|D|3|E|5|6|7|Z|8|@]|9|@]|C|$]]|$1|F|3|G|5|H|7|10|8|@]|9|@]|C|$]]|$1|I|3|-4|5|6|7|11|8|@]|9|@]|C|$]]|$1|J|3|K|5|6|7|12|8|@]|9|@]|C|$]]|$1|L|3|M|5|6|7|13|8|@]|9|@]|C|$]]|$1|N|3|-4|5|6|7|14|8|@]|9|@]|C|$]]]|O|$P|$5|Q|R|S|C|$T|U]]]]

As the <a href="http://dev.mysql.com/doc/refman/5.6/en/constraint-enum.html" rel="noreferrer">MySQL documentation</a> states:

<blockquote>
 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.
</blockquote>

blocks|key|196309|text|Enum和Set完全取决于需求，比如如果你有一个单选按钮列表，一次只能选择一个，那就使用Enum。如果您有一个复选框列表，其中一次可以选择多个项目，请使用set。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|196310|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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.

blocks|key|2827293|text|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');|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|2827294|您可以复制上面的代码并将其粘贴到mysql中，您会发现SET实际上是一个集合。您可以存储您声明的每个属性组合。|unstyled|offset|length|style|BOLD|2827295|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');|2827296|你也可以复制上面的代码。您会发现每个ENUM实际上每次只能存储一次。你会发现最后两行的结果都是空的。|2827297|entityMap^0|0|R|3|0|0|I|4|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|R|8|@$G|S|H|T|I|J]]|9|@]|A|$]]|$1|K|3|L|5|6|7|U|8|@]|9|@]|A|$B|C]]|$1|M|3|N|5|F|7|V|8|@$G|W|H|X|I|J]]|9|@]|A|$]]|$1|O|3|-4|5|F|7|Y|8|@]|9|@]|A|$]]]|P|$]]

<pre><code>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');
</code></pre>

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.

<pre><code>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');
</code></pre>

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.

blocks|key|2827307|text|打个比方：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2827308|ENUM+=单选字段(只接受列出的值，只能选择一个)|2827309|SET+=复选框字段(只接受列出的值，可以选择多个)|2827310|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

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)

blocks|key|2827339|text|实际上很简单：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2827340|当你定义一个枚举(‘是’，‘否’，‘可能’)，那么你必须只插入这些值中的一个(或它们的位置索引号)|offset|length|style|BOLD|2827341|当你定义一个空集(‘R’，'W'，'X')，然后你可以插入一个空字符串，或一个或多个这些值。如果插入的内容不在预定义的集合中，则会插入空字符串is。请注意，在插入之前，所有重复的值都会被丢弃，因此只会插入每个允许值的一个实例。|2827342|希望这能澄清这一点。|2827343|请注意，Winbobob的答案是不正确的，并且包含有缺陷的示例，因为当插入多个值时，值必须是字符串，用逗号分隔。他的所有插入实际上只插入了一个值(最后两个不在定义的集合中)|2827344|entityMap^0|0|6|G|0|10|1|0|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Q|8|@$D|R|E|S|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|T|8|@$D|U|E|V|F|G]]|9|@]|A|$]]|$1|J|3|K|5|6|7|W|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|X|8|@]|9|@]|A|$]]|$1|N|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|O|$]]

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)

blocks|key|199007|text|ENUM+-->仅选择一个可用值进行插入。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|199008|(no_null，no_any_duplicate)|199009|199010|集合-->选择任意组合或单个值，如集合，并将它们插入。|199011|(null，individual_values，all_the_available_values_together)|199012|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|M|8|@]|9|@]|A|$]]|$1|E|3|F|5|6|7|N|8|@]|9|@]|A|$]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|P|8|@]|9|@]|A|$]]]|J|$]]

ENUM --&gt; choose only one of the available values to insert.
(no_null, no_any_duplicate)
<hr />
SET --&gt; choose any combination or single value like a set and insert them into.
(null, individual_values, all_the_available_values_together)

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:

<ul>
<li>enum('A', 'B', 'C')</li>
<li>set('A', 'B', 'C')</li>
</ul>

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.

MySQL enum vs. set

MySQL

对于“枚举”和“集”的MySQL数据类型，使用一种和另一种有什么区别和优缺点？示例数据类型：枚举(‘A’，'B'，'C')set('A'，'B'，'C')我知道的唯一区别是ENUM只允许选择一个值，而SET允许选择多个值。

问MySQL枚举与set
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MySQL枚举与setEN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MySQL枚举与set
EN