Unable to update MySQL SET datatype in a table using REPLACE while referring to SET members using in-CodePudding

I have the following table 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 |

Here the set members as indexed as follows -

 ----------- --------- -------------- 
| 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"     |
 ----------- --------- --------------

I populate the table as follows -

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

Now, the content of the table is -

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

Now, I can search for a particular row using index instead of words as follows -

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

Now, if I want to update my table, I can do it using words as follows -

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

But, I intend to do it using indexes rather than words. Thus I replace the words in above query with indexes. But I am unable to get the desired result -

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

I even try to match the entire index value and replace it with another index value, but still unsuccessful.

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

So how can I do update operation using index instead of words?

CodePudding user response：

You can do what you describe with bitwise operations:

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

The & ~8 does a bitwise AND against the bitwise complement of 1000 (binary), which is 1111111111111111111111111111111111111111111111111111111111110111. This strips out the bit that represents C .

The | 4 does a bitwise OR against 100 (binary). This sets the bit for Java.