I regularly use the SET data type in MySQL. The SET data type is ideal to store multiple values in a column. As an example, you can have a column in a table that stores possible ways to go to work. The column could be defined as SET(‘car’,’public_transport’,’bike’,’walk’). The nice thing about a SET column is that is can contain multiple values, so somebody that typically goes to work by car, but sometimes comes to work by bike, can have the value ‘car,walk’.
You can read more about the use of the SET data type on the MySQL website. The article contains some hints on how to add and remove values from a SET, but as Beat Vontobel points out in a post on his website, there are some issues with those approaches. He also describes a better solution, which leads to creating stored function for ADD_TO_SET and REMOVE_FROM_SET.
My internet provider does not allow stored functions, but you can mimic the behavior by simply using his proposed solution directly into the UPDATE statement.
ADD_TO_SET:
UPDATE set_test SET myset = CONCAT_WS(',', IF(myset = '', NULL, myset), 'value') WHERE ...
REMOVE_FROM_SET:
UPDATE set_test SET myset = TRIM(BOTH ',' FROM REPLACE(CONCAT(',', myset, ','), CONCAT(',', 'value', ','), ',')) WHERE ...
You can simplify the ADD_TO_SET example by using the fact that concat_ws() will ignore null values. The example can be simply:
UPDATE set_test SET myset =
CONCAT_WS(‘,’, myset, ‘value’)
WHERE …
Thanks Steven!
Good illustration, but stevens example (metioned on comment) cannot ignore empty values, only NULL
Your solution helped me a lot. I was struggling to remove item from comma delimated list. Thanks a lot !