当使用索引而不是词引用SET成员时，无法使用REPLACE更新表中的MySQL SET数据类型 &g

Question

我有以下表格my_set_table-

my_set_table | CREATE TABLE `my_set_table` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`my_col` set('C','C++','Java','C#','JavaScript','Kotlin','SQLite','Python') DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |

这里集合成员的索引如下-

+-----------+---------+--------------+
| Binary    | Decimal | Member       |
+-----------+---------+--------------+
| 0000 0001 | 1       | "C"          |
| 0000 0010 | 2       | "C++"        |
| 0000 0100 | 4       | "Java"       |
| 0000 1000 | 8       | "C#"         |
| 0001 0000 | 16      | "Javascript" |
| 0010 0000 | 32      | "Kotlin"     |
| 0100 0000 | 64      | "SQLite"     |
| 1000 0000 | 128     | "Python"     |
+-----------+---------+--------------+

我按如下方式填充表-

INSERT INTO my_set_table (my_col) VALUES
-> (9),
-> (36),
-> (100),
-> (130),
-> (128),
-> (136);

现在，表的内容是-

SELECT * FROM my_set_table;
+----+--------------------+
| id | my_col             |
+----+--------------------+
|  1 | C,C#               |
|  2 | Java,Kotlin        |
|  3 | Java,Kotlin,SQLite |
|  4 | C++,Python         |
|  5 | Python             |
|  6 | C#,Python          |
+----+--------------------+

现在，我可以使用索引而不是单词搜索特定的行，如下所示-

SELECT * FROM my_set_table  
-> WHERE my_col = 9;
+----+--------+
| id | my_col |
+----+--------+
|  1 | C,C#   |
+----+--------+

现在，如果我想更新我的表，我可以使用下面的单词来做-

UPDATE my_set_table 
-> SET my_col = REPLACE(my_col,"C#","Java")
-> WHERE ID = 1;
SELECT * FROM my_set_table;
+----+--------------------+
| id | my_col             |
+----+--------------------+
|  1 | C,Java             |
|  2 | Java,Kotlin        |
|  3 | Java,Kotlin,SQLite |
|  4 | C++,Python         |
|  5 | Python             |
|  6 | C#,Python          |
+----+--------------------+

但是，我打算使用索引而不是单词。因此，我将上面查询中的单词替换为索引。但是我无法得到想要的结果-

UPDATE my_set_table 
-> SET my_col = REPLACE(my_col,8,4)
-> WHERE id = 1;
SELECT * FROM my_set_table;
+----+--------------------+
| id | my_col             |
+----+--------------------+
|  1 | C,C#               |
|  2 | Java,Kotlin        |
|  3 | Java,Kotlin,SQLite |
|  4 | C++,Python         |
|  5 | Python             |
|  6 | C#,Python          |
+----+--------------------+

我甚至尝试匹配整个索引值并用另一个索引值替换它，但仍然不成功。

UPDATE my_set_table
-> SET my_col = REPLACE(my_col,9,5)
-> WHERE id = 1;
SELECT * FROM my_set_table;
+----+--------------------+
| id | my_col             |
+----+--------------------+
|  1 | C,C#               |
|  2 | Java,Kotlin        |
|  3 | Java,Kotlin,SQLite |
|  4 | C++,Python         |
|  5 | Python             |
|  6 | C#,Python          |
+----+--------------------+

那么我如何使用索引而不是单词来进行更新操作呢?

Answer 1

你可以做你所描述的按位操作:

mysql> select my_col from my_set_table where id=1;
+--------+
| my_col |
+--------+
| C,C#   |
+--------+
mysql> update my_set_table set my_col = my_col & ~8 | 4 where id = 1;
Query OK, 1 row affected (0.02 sec)
mysql> select my_col from my_set_table where id=1;
+--------+
| my_col |
+--------+
| C,Java |
+--------+

& ~8对1000(二进制)的位补码1111111111111111111111111111111111111111111111111111111111110111进行位与运算。这去掉了代表c++的位。

| 4对100(二进制)进行位或操作。

为Java设置位。