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:

REMOVE_FROM_SET:

Share

Tweet: Het programma voor het 9e Holebifilmfest…

Tweet by @KrisGielen: Het programma voor het 9e Holebifilmfestival is bekend. Bekijk het op onze website: http://www.holebifilmfestival.be/programma/

Share