Description:
The SET data type is powerful and storage-efficient way of storing lists of values. Extracting or filtering values from SET columns is made easy thanks to FIND_IN_SET(). However, manipulating them is quite difficult, and if adding values is relatively easy, removing a value from a SET requires awkward string manipulations or some cryptic bitwise magic.
That's why I propose to add two functions to complement FIND_IN_SET() : ADD_TO_SET(str,strlist) and REMOVE_FROM_SET(str,strlist)
Below is an example of those functions in action, as well as a mockup implementation in SQL.
How to repeat:
mysql> CREATE TABLE set_test(id INT, strlist SET('foo', 'bar', 'baz'));
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO set_test VALUES (1, 'foo'), (2, 'foo,bar'), (3, 'baz');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM set_test;
+------+---------+
| id | strlist |
+------+---------+
| 1 | foo |
| 2 | foo,bar |
| 3 | baz |
+------+---------+
3 rows in set (0.00 sec)
mysql> UPDATE set_test SET strlist = ADD_TO_SET('baz', strlist);
Query OK, 2 rows affected (0.00 sec)
Rows matched: 3 Changed: 2 Warnings: 0
mysql> SELECT * FROM set_test;
+------+-------------+
| id | strlist |
+------+-------------+
| 1 | foo,baz |
| 2 | foo,bar,baz |
| 3 | baz |
+------+-------------+
3 rows in set (0.00 sec)
mysql> UPDATE set_test SET strlist = REMOVE_FROM_SET('baz', strlist);
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> SELECT * FROM set_test;
+------+---------+
| id | strlist |
+------+---------+
| 1 | foo |
| 2 | foo,bar |
| 3 | |
+------+---------+
3 rows in set (0.00 sec)
Suggested fix:
/* Let's simulate those functions in SQL */
DELIMITER //
CREATE FUNCTION ADD_TO_SET(
$str TEXT,
$strlist TEXT
)
RETURNS TEXT
DETERMINISTIC
BEGIN
RETURN IF($strlist = '', $str, CONCAT_WS(',', $strlist, $str));
END
//
CREATE FUNCTION REMOVE_FROM_SET(
$str TEXT,
$strlist TEXT
)
RETURNS TEXT
DETERMINISTIC
BEGIN
IF (FIND_IN_SET($str, $strlist) = 0)
THEN
RETURN $strlist;
END IF;
RETURN SUBSTRING(REPLACE(CONCAT(',', $strlist, ','), CONCAT(',', $str, ','), ',') FROM 2 FOR LENGTH($strlist) - LENGTH($str) - 1);
END
//