Mysql 简明教程
MySQL - SET
The MySQL SET data type
MySQL SET 数据类型用于存储从预定义的值列表中选择的一组值。SET 数据类型的每一列都可以从其值列表中选择零个或多个值。在插入或更新数据时，这些值被指定为以逗号分隔的列表。
The MySQL SET data type is used to store a set of values chosen from a predefined list of values. Each column of the SET datatype can have zero or more values chosen from its list of values. These values are specified as a comma-separated list when inserting or updating data.
需要注意的是，可以在 SET 列中存储的值的列表是在创建表时定义的，并且不允许该列表之外的任何值。
It is important to note that the list of values that can be stored in a SET column is defined at the time the table is created, and any values outside this list are not allowed.
例如，如果我们像这样定义 SET 列 -
For example, if we define a SET column like this −
test_col SET('one', 'two') NOT NULL该列的可能值 -
The possible values for this column are −
-
An empty string ('')
-
'one'
-
'two'
-
'one,two'
Storage of SET Data Type
一个 MySQL SET 列最多可以包含 64 distinct 个成员，这意味着不允许重复的值。如果存在重复，MySQL 会在启用严格 SQL 模式时生成错误或警告。此外，MySQL 在创建表时会自动移除 SET 值中的尾部空格。
A MySQL SET column can hold a maximum of 64 distinct members, which means that duplicate values are not allowed. If duplicates exist, MySQL will generate an error or a warning when strict SQL mode is enabled. Additionally, MySQL automatically removes trailing spaces from SET values when creating a table.
在 MySQL 中，当你在 SET 列中存储一个数字时，该数字二进制表示中设置的位确定了哪些集合成员包含在列值中。为了更好地理解，考虑下面的查询 -
In MySQL, when you store a number in a SET column, the bits set in the binary representation of that number determine which set members are included in the column value. Consider the following query for a better understanding −
Create table test_table (
ID int auto_increment primary key ,
COL1 set('Goa', 'Assam', 'Delhi', 'Kerala')
);在上一个查询中，每个集合成员都分配了一个位，具有对应的十进制和二进制值 -
In the above query, each set member is assigned a single bit with corresponding decimal and binary values −
所以，如果将 3 的值分配给列（二进制：0011），则它会选择前两个 SET 成员，从而得到“Goa,Assam”。
So, if a value of 3 is assigned to the column (binary: 0011), it selects the first two SET members, resulting in 'Goa,Assam'.
Example
首先，让我们使用以下查询创建名为 test_table 的表 -
First of all, let us create a table with the name test_table using the following query −
Create table test_table (
ID int auto_increment primary key ,
COL1 set('Goa', 'Assam', 'Delhi', 'Kerala')
);以下是所获得的输出 −
Following is the output obtained −
Query OK, 0 rows affected (0.02 sec)在向 SET 列插入值时，列出元素不需要特定的顺序。即使某个特定元素列出多次，在以后检索时，每个元素只会出现一次，遵循表创建期间指定的顺序。
When inserting values into a SET column, there is no specific order required for listing the elements. Even if a particular element is listed multiple times, when retrieved later, each element will appear only once, following the order specified during table creation.
在此处，我们将值插入到集合中 -
Here, we are inserting the values into the set −
INSERT INTO test_table (COL1) VALUES
('Goa,Assam'),
('Assam,Goa'),
('Goa,Assam,Goa'),
('Goa,Assam,Assam'),
('Assam,Goa,Assam');Output
下面显示了产生的输出：
The output produced is as shown below −
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0Verification
使用 SELECT 语句显示“test_table”的所有记录，如下所示 -
Let us display all the records of the 'test_table' using the SELECT statement as follows −
SELECT * FROM test_table;正如我们在下面的输出中看到的，所有“COL1”中的值都会显示为“Goa,Assam” -
As we can see the output below, all the values in 'COL1' will appear as 'Goa,Assam' −
Example
在以下查询中，我们使用 MySQL LIKE 操作符搜索表中的 SET 值。它查找“COL1”中包含“GOA”的任意位置（甚至是子字符串）的行 -
In the following query, we are searching for the SET values in the table using the MySQL LIKE operator. It finds rows where 'COL1' contains 'GOA' anywhere, even as a substring −
SELECT * FROM test_table WHERE COL1 LIKE '%Goa%';Updating the SET Values
在 MySQL 中，你可以通过替换元素、添加元素或从 SET 数据中移除元素来更新 SET 元素。以下是每种方法的示例 -
In MySQL, you can update SET elements in various ways: by replacing elements, adding elements, or removing elements from the SET data. Here are examples of each method −
Replacing SET Data
在下面的查询中，我们将第 5 行的值替换为数字 11，它对应于 Goa + Assam + Kerala (8 + 2 + 1)。
In the following query, we replace the value in the 5th row with the number 11, which corresponds to Goa + Assam + Kerala (8 + 2 + 1) −
UPDATE test_table SET COL1 = 11 WHERE Id = 5;Output
查询成功执行并生成以下输出：
The query executes successfully and produces the following output −
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0Verification
若要验证 test_table 中所做的更改，请使用以下 SELECT 查询：
To verify the changes done in the test_table, use the following SELECT query −
SELECT * FROM test_table;下面是生成的输出−
Following is the output produced −
Adding Data to SET
您可以使用 CONCAT() 函数向现有 SET 列中添加元素。在此示例中，我们向第 3 行中的值中添加“Kerala”：
You can add elements to an existing SET column using the CONCAT() function. In this example, we add "Kerala" to the value in the 3rd row −
UPDATE test_table SET COL1 = CONCAT(COL1, ",Kerala")
WHERE Id = 3;Output
此查询的输出如下：
The output for this query is as follows −
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0Verification
若要验证 test_table 中所做的更改，请使用以下 SELECT 查询：
To verify the changes done in the test_table, use the following SELECT query −
SELECT * FROM test_table;结果显示更新后的值：
The result shows the updated value −
Removing Data from SET
若要移除特定的 SET 元素，可以使用 & ~ 位操作。在此示例中，我们从第 4 行中的值中移除“Assam”元素：
To remove a specific SET element, you can use the & ~ bitwise operation. In this example, we remove the "Assam" element from the value in the 4th row −
UPDATE test_table SET COL1 = COL1 & ~2 WHERE ID = 4;