SET 是一种字符串对象，可以包含零个或多个值，每个值必须从表创建时指定的允许值列表中选择。SET 列值如果包含多个集合成员，则成员之间用逗号 (,) 分隔。这意味着 SET 成员值本身不应包含逗号。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('one', 'two') NOT NULL，可以具有以下任意值：For example, a column specified as SET('one', 'two') NOT NULL can have any of these values:

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

一个 SET 列最多可以有 64 个不同的成员。A SET column can have a maximum of 64 distinct members.

定义中的重复值会导致警告，如果启用严格 SQL 模式则会导致错误。Duplicate values in the definition cause a warning, or an error if strict SQL mode is enabled.

表创建时，表定义中 SET 成员值末尾的空格会自动删除。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.

有关 SET 类型的语法和长度限制，请参见 第 13.3.1 节，“字符串数据类型语法”。See Section 13.3.1, “String Data Type Syntax” for SET type syntax and length limits.

当检索时，存储在 SET 列中的值会使用列定义中使用的字母大小写显示。请注意，SET 列可以指定字符集和排序规则。对于二进制或区分大小写的排序规则，在为列分配值时会考虑字母大小写。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.

MySQL 将 SET 值以数字形式存储，存储值的最低位对应第一个集合成员。如果在数字上下文中检索 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> SELECT set_col+0 FROM tbl_name;

如果将一个数字存储到 SET 列中，则该数字的二进制表示中被设置的位决定了列值中的集合成员。对于指定为 SET('a','b','c','d') 的列，成员具有以下十进制和二进制值。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.

如果你给这一列赋值 9，在二进制中是 1001，所以第一个和第四个 SET 值成员 'a' 和 'd' 被选中，结果值是 'a,d'。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'.

对于包含多个SET元素的值，在插入该值时元素的列出顺序无关紧要。同样，值中列出某个元素的次数也无关紧要。当值稍后被检索时，值中的每个元素只出现一次，并且元素按表创建时指定的顺序列出。假设某列被指定为SET('a','b','c','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'):

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

如果你插入以下值'a,d'、'd,a'、'a,d,d'、'a,d,a'和'd,a,d'：If you insert the values 'a,d', 'd,a', 'a,d,d', 'a,d,a', and '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

那么当检索这些值时，所有这些值都显示为'a,d'：Then all these values appear as 'a,d' when retrieved:

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

如果你将SET列设置为不支持的值，该值将被忽略并发出警告：If you set a SET column to an unsupported value, the value is ignored and a warning is issued:

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)

如果启用了严格 SQL 模式，尝试插入无效的 SET 值会导致错误。If strict SQL mode is enabled, attempts to insert invalid SET values result in an error.

SET 值按数值排序。NULL 值在非 NULL SET 值之前排序。SET values are sorted numerically. NULL values sort before non-NULL SET values.

像 SUM() 或 AVG() 这样的函数，如果期望数字参数，会在必要时将参数转换为数字。对于 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.

通常，您可以使用 FIND_IN_SET() 函数或 LIKE 操作符来搜索 SET 值：Normally, you search for SET values using the FIND_IN_SET() function or the LIKE operator:

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

第一条语句会查找 set_col 包含 value 集合成员的行。第二条语句类似，但不完全相同：它会查找 set_col 中任何位置包含 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.

以下语句也是允许的：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';

这些语句中的第一个查找包含第一个集合成员的值。第二个查找完全匹配的值。在进行第二类比较时要小心。将集合值与 'val1,val2' 比较，与将值与 'val2,val1' 比较会得到不同的结果。你应该按照列定义中列出的顺序指定值。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 列的所有可能值，请使用 SHOW COLUMNS FROM tbl_name LIKE set_col 并解析输出中 Type 列中的 SET 定义。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.

在 C API 中，SET 值以字符串形式返回。有关使用结果集元数据将它们与其他字符串区分的信息，请参阅 C API 基本数据结构。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.