MySQL 5.7-11.3.6 The SET Type

A SET is a string object that can have zero or more values, each of which must be chosen from a list of permitted values specified when the table is created. SET column values that consist of multiple set members are specified with members separated by commas (,). A consequence of this is that SET member values should not themselves contain commas.

SET是一个字符串对象，可以有0个或多个值，每个值都必须从创建表时指定的允许值列表中选择。由多个SET成员组成的SET列值使用逗号(，)分隔。这样做的结果是SET成员值本身不应该包含逗号。

For example, a column specified as SET('one', 'two') NOT NULL can have any of these values:

例如，指定为SET('one'， 'two') NOT NULL的列可以有以下任何一个值:

''
'one'
'two'
'one,two'

A SET column can have a maximum of 64 distinct members. A table can have no more than 255 unique element list definitions among its ENUM and SET columns considered as a group. For more information on this limit, see Limits Imposed by .frm File Structure.

SET列最多可以有64个不同的成员。一个表的ENUM列和SET列作为一个组，其中唯一的元素列表定义不能超过255个。有关此限制的更多信息，请参见.frm文件结构施加的限制。

Duplicate values in the definition cause a warning, or an error if strict SQL mode is enabled.

如果启用了严格的SQL模式，定义中的重复值将导致警告或错误。

Trailing spaces are automatically deleted from SET member values in the table definition when a table is created.

创建表时，表定义中的SET成员值会自动删除尾随空格。

See String Type Storage Requirements for storage requirements for the SET type.

See Section 11.3.1, “String Data Type Syntax” for SET type syntax and length limits.

When retrieved, values stored in a SET column are displayed using the lettercase that was used in the column definition. Note that SET columns can be assigned a character set and collation. For binary or case-sensitive collations, lettercase is taken into account when assigning values to the column.

检索时，存储在SET列中的值将使用列定义中使用的字母大小写显示。注意，SET列可以被分配一个字符集和排序规则。对于二进制或区分大小写的排序规则，在为列赋值时将考虑字母大小写。

MySQL stores SET values numerically, with the low-order bit of the stored value corresponding to the first set member. If you retrieve a SET value in a numeric context, the value retrieved has bits set corresponding to the set members that make up the column value. For example, you can retrieve numeric values from a SET column like this:

MySQL以数字方式存储SET值，存储值的低阶位对应于第一个SET成员。如果在数字上下文中检索SET值，则检索的值设置了与构成列值的SET成员相对应的位。例如，你可以像这样从SET列中检索数值:

mysql> SELECT set_col+0 FROM tbl_name;

If a number is stored into a SET column, the bits that are set in the binary representation of the number determine the set members in the column value. For a column specified as SET('a','b','c','d'), the members have the following decimal and binary values.

如果一个数字存储在SET列中，则在该数字的二进制表示中设置的位决定了列值中的SET成员。对于指定为SET('a'，'b'，'c'，'d')的列，其成员具有以下十进制和二进制值。

SET Member	Decimal Value	Binary Value
'a'	1	0001
'b'	2	0010
'c'	4	0100
'd'	8	1000

If you assign a value of 9 to this column, that is 1001 in binary, so the first and fourth SET value members 'a' and 'd' are selected and the resulting value is 'a,d'.

如果你给这个列赋值9，即二进制的1001，那么第一个和第四个SET值成员'a'和'd'被选中，结果值是'a,d'。

For a value containing more than one SET element, it does not matter what order the elements are listed in when you insert the value. It also does not matter how many times a given element is listed in the value. When the value is retrieved later, each element in the value appears once, with elements listed according to the order in which they were specified at table creation time. Suppose that a column is specified as SET('a','b','c','d'):

对于包含多个SET元素的值，插入值时列出元素的顺序并不重要。给定元素在值中列出多少次也没有关系。当稍后检索值时，值中的每个元素出现一次，并根据表创建时指定的顺序列出元素。假设一个列被指定为SET('a'，'b'，'c'，'d'):

mysql> CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));

If you insert the values 'a,d', 'd,a', 'a,d,d', 'a,d,a', and 'd,a,d':

如果你插入的值,d, d,, a, d, d ', ' a、d ',和' d, a, d ':

mysql> INSERT INTO myset (col) VALUES 
-> ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

Then all these values appear as 'a,d' when retrieved:

然后所有这些值在检索时都显示为'a,d':

mysql> SELECT col FROM myset;
+------+
| col  |
+------+
| a,d  |
| a,d  |
| a,d  |
| a,d  |
| a,d  |
+------+
5 rows in set (0.04 sec)

If you set a SET column to an unsupported value, the value is ignored and a warning is issued:

如果您将set列设置为不支持的值，该值将被忽略并发出警告:

mysql> INSERT INTO myset (col) VALUES ('a,d,d,s');
Query OK, 1 row affected, 1 warning (0.03 sec)

mysql> SHOW WARNINGS;
+---------+------+------------------------------------------+
| Level   | Code | Message                                  |
+---------+------+------------------------------------------+
| Warning | 1265 | Data truncated for column 'col' at row 1 |
+---------+------+------------------------------------------+
1 row in set (0.04 sec)

mysql> SELECT col FROM myset;
+------+
| col  |
+------+
| a,d  |
| a,d  |
| a,d  |
| a,d  |
| a,d  |
| a,d  |
+------+
6 rows in set (0.01 sec)

If strict SQL mode is enabled, attempts to insert invalid SET values result in an error.

如果启用了严格的SQL模式，尝试插入无效的SET值将导致错误。

SET values are sorted numerically. NULL values sort before non-NULL SET values.

SET值是按数字排序的。NULL值排序在非NULL SET值之前。

Functions such as SUM() or AVG() that expect a numeric argument cast the argument to a number if necessary. For SET values, the cast operation causes the numeric value to be used.

像SUM()或AVG()这样需要数值实参的函数在必要时将实参转换为数字。对于SET值，强制转换操作将使用数值。

Normally, you search for SET values using the FIND_IN_SET() function or the LIKE operator:

通常，使用FIND_IN_SET()函数或LIKE操作符搜索SET值:

mysql> SELECT * FROM tbl_name WHERE FIND_IN_SET('value',set_col)>0;
mysql> SELECT * FROM tbl_name WHERE set_col LIKE '%value%';

The first statement finds rows where set_col contains the value set member. The second is similar, but not the same: It finds rows where set_col contains value anywhere, even as a substring of another set member.

第一个语句找到set_col包含值集成员的行。第二个类似，但不相同:它查找set_col包含值的行，即使是另一个set成员的子字符串。

The following statements also are permitted:

也允许使用下列语句:

mysql> SELECT * FROM tbl_name WHERE set_col & 1;
mysql> SELECT * FROM tbl_name WHERE set_col = 'val1,val2';

The first of these statements looks for values containing the first set member. The second looks for an exact match. Be careful with comparisons of the second type. Comparing set values to 'val1,val2' returns different results than comparing values to 'val2,val1'. You should specify the values in the same order they are listed in the column definition.

第一个语句查找包含第一个集合成员的值。第二种是寻找完全匹配的。注意第二种类型的比较。将set值与'val1,val2'进行比较会得到与'val2,val1'不同的结果。您应该按照列定义中列出的顺序指定值。

To determine all possible values for a SET column, use SHOW COLUMNS FROM tbl_name LIKE set_col and parse the SET definition in the Type column of the output.

要确定SET列的所有可能值，请使用SHOW COLUMNS FROM tbl_name LIKE set_col并解析输出的Type列中的SET定义。

In the C API, SET values are returned as strings. For information about using result set metadata to distinguish them from other strings, see C API Basic Data Structures.

在C API中，SET值作为字符串返回。有关使用结果集元数据将它们与其他字符串区分开来的信息，请参阅C API基本数据结构。