Bug #35562 Add functions to manipulate SET data (SET data type)
Submitted: 26 Mar 2008 0:26 Modified: 26 Mar 2008 0:41
Reporter: Hubert Roksor Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: General Severity:S4 (Feature request)
Version:5.1+ OS:Any
Assigned to: CPU Architecture:Any
Tags: FIND_IN_SET, SET

[26 Mar 2008 0:26] Hubert Roksor
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
//
[26 Mar 2008 0:41] MySQL Verification Team
Thank you for the bug report feature request.