How to update MySQL SET Type items?

Question

I have a table of values, one of the columns being of type SET .

If there are currently members ('a', 'b', 'c', 'd'), how to add 'e' to the possible values?

I understand that using the SET type is a bit strange, and I don’t understand why it should be used instead of a foreign key in another table with the values ​​of the set, but I did not design the corresponding database, and cannot change it so much.

Thank you for your help!

UPDATE: I want to update the SET type for all rows, not just one if that helps.

Answer 1

Do you want to add 'e' to the valid values ​​in this set field or already there, and you want to add 'e' to the current value of the set field in this table?

If this is not a valid value yet, you will need to do ALTER TABLE and override this field:

ALTER TABLE set_test CHANGE myset myset SET('a','b','c','d','e'); 

(yes, "myset" is placed there twice, this is the "name of the current name".)

Otherwise, just add an UPDATE TABLE and split the "e" field as the previous answer says.

Answer 2

To add an item to an existing SET, use the CONCAT () function to add a new item to the comma-separated list. To work with decimal values, we can use the bitwise operator OR |.

  UPDATE set_test SET myset = CONCAT(myset,",Travel") WHERE rowid = 3; 

or

  UPDATE set_test SET myset = myset | 1 WHERE rowid = 3; 

You can also use the CONCAT_WS () function, which handles list separators for us:

  UPDATE set_test SET myset = CONCAT_WS(',',myset,'Dancing') WHERE rowid = 6;