Skip to content

Instantly share code, notes, and snippets.

@mpneuried
Last active December 18, 2015 01:29
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save mpneuried/5704200 to your computer and use it in GitHub Desktop.
Save mpneuried/5704200 to your computer and use it in GitHub Desktop.
Workarround to store a clean array in mysql
# 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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment