Emulating ADD_TO_SET and REMOVE_FROM_SET in MySQL

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

4 Comments

  1. 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 …

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.