Workarround to store a clean array in mysql

clean_array.sql

# drop a existing test table
DROP TABLE IF EXISTS `test`;

# create the test table
CREATE TABLE `test` (
  `_h` varchar(5) NOT NULL DEFAULT '',
  `_set` text,
  PRIMARY KEY (`_h`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# delete the test case data
DELETE FROM test;

# insert the test data
INSERT INTO test ( _h, _set )
VALUES ( "c", "|a|aa|aaa|x|y|" );

# do a single remove
UPDATE test
SET _set = Replace(IF(_set is NULL,"|",_set), '|aa|', '|')
WHERE _h = "c";

# do a single add
UPDATE test
SET _set = CONCAT(IF(_set is NULL,"|",_set), 'c|')
WHERE _h = "c";

# do a multiple add with existence check
UPDATE test
SET _set = CONCAT(IF(_set is NULL,"|",_set), IF( INSTR(IF(_set is NULL,"|",_set),'a|') = 0,"a|", "" ), IF( INSTR(IF(_set is NULL,"|",_set),'r|') = 0,"r|", "" ) )
WHERE _h = "c";

# do a multiple remove with existence check
UPDATE test
SET _set = Replace( Replace(IF(_set is NULL,"|",_set), '|r|', '|') , '|aaa|', '|')
WHERE _h = "c";

# do a multiple add and remove with existence check
UPDATE test
SET _set = Replace( Replace( CONCAT(IF(_set is NULL,"|",_set), IF( INSTR(IF(_set is NULL,"|",_set),'a|') = 0,"a|", "" ), IF( INSTR(IF(_set is NULL,"|",_set),'l|') = 0,"l|", "" ) ) , '|x|', '|') , '|eee|', '|')
WHERE _h = "c";

# insert a empty set
INSERT INTO test ( _h )
VALUES ( "b" );

# do a multiple add and remove with existence check for a empty set
UPDATE test
SET _set = Replace( Replace( CONCAT(IF(_set is NULL,"|",_set), IF( INSTR(IF(_set is NULL,"|",_set),'a|') = 0,"a|", "" ), IF( INSTR(IF(_set is NULL,"|",_set),'l|') = 0,"l|", "" ) ) , '|x|', '|') , '|eee|', '|')
WHERE _h = "b";

# do a multiple add and remove with existence check for a empty set
UPDATE test
SET _set = Replace( Replace( IF(_set is NULL,"|",_set), '|a|', '|') , '|l|', '|')
WHERE _h = "b";

# add a single element
UPDATE test
SET _set = CONCAT(IF(_set is NULL,"|",_set), IF( INSTR(IF(_set is NULL,"|",_set),'single|') = 0,"single|", "" ) )
WHERE _h = "b";

# insert with a complex statement
INSERT INTO test ( _h, _set )
VALUES ( "a", Replace( Replace( CONCAT(IF(_set is NULL,"|",_set), IF( INSTR(IF(_set is NULL,"|",_set),'a|') = 0,"a|", "" ), IF( INSTR(IF(_set is NULL,"|",_set),'l|') = 0,"l|", "" ) ) , '|x|', '|') , '|eee|', '|') );

# return the test case
# result should be:
# b = "|single|"
# c = "|a|y|c|l|"
SELECT * FROM  test;