String Data Types之SET_datatype set

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是一个字符串对象，可以具有零个或多个值，每个值都必须从创建表时指定的允许值列表中选择。 由多个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.

有关SET类型的存储要求，请参见字符串类型存储要求。

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

有关SET类型的语法和长度限制，请参见第11.3.1节“字符串数据类型语法”。

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。 例如，您可以像这样从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'）的列，成员具有以下十进制和二进制值。

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':

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.

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

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.

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

• 如果启用了严格的SQL模式，则尝试插入无效的SET值将导致错误。
• SET值按数字排序。 NULL值在非NULL SET值之前排序。
• 期望数值参数的函数，例如SUM（）或AVG（），可在必要时将其强制转换为数字。 对于SET值，强制转换操作将导致使用数值。
• 通常，您使用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.

The following statements also are permitted:

第一条语句查找其中set_col包含值set成员的行。 第二个相似，但不相同：它找到行，其中set_col包含任何地方的值，甚至作为另一个set成员的子字符串。

也允许使用以下语句：

-- 查找包含第一个set成员的值
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.

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.

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 Data Structures.

这些语句中的第一个语句查找包含第一个set成员的值。 第二个寻找完全匹配。 比较第二种类型时要小心。 将设置值与“ val1，val2”进行比较所返回的结果不同于将值与“ val2，val1”进行比较所得出的结果。 您应按照列定义中列出的顺序指定值。

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

在C API中，SET值以字符串形式返回。 有关使用结果集元数据将它们与其他字符串区分开的信息，请参见C API数据结构。

 

demo

CREATE TABLE myset1 (col SET('aa', 'bb', 'cc', 'dd'));
 
INSERT INTO myset1 (col) VALUES ('aa,dd'), ('dd,aa'), ('aa,dd,aa'), ('aa,dd,dd'), ('dd,aa,dd');
 
 
SELECT * FROM myset1 WHERE FIND_IN_SET('aa',col)>0;
 
SELECT * FROM myset1 WHERE col LIKE '%a,d%';