MYSQL Comma Delimited list, possible to add and remove values?

Question

I have a comma delimited list that im storing in a varchar field in a mysql table.

Is it possible to add and remove values from the list directly using sql queries? Or do I have to take the data out of the table, manipulate in PHP and replace it back into mysql?

What are you doing - are you trying to write your own little database... in SQL? — Kerrek SB, Jul 10 '11 at 21:15
can you be more specific about how you are trying to manipulate this string? are you sure you need to be storing a string into this table? it's really bad practice unless you are doing something like pre-calculating info that you can't do in real time for users. — T. Brian Jones, Jul 10 '11 at 21:17
take a look here http://stackoverflow.com/questions/5454610/mysql-remove-the-specific-word-in-comma-seperated-string — alexbusu, May 31 '13 at 09:09

score 4 · Accepted Answer · answered Jul 10 '11 at 21:24

There is no way to do it in InnoDB and MyIsam engines in mysql. Might be in other engines (check CSV engine).
You can do it in a stored procedure, but, not recommended.
What you should do to solve such an issue is to refactor your code and normalize your DB =>
original table

T1: id | data                    | some_other_data
     1 |  gg,jj,ss,ee,tt,hh      |   abanibi

To become:

T1: id | some_other_data
     1 |   abanibi

T2: id   |  t1_id    |   data_piece
     1   |    1      |      gg 
     2   |    1      |      jj 
     3   |    1      |      ss 
     4   |    1      |      ee
     5   |    1      |      tt
     6   |    1      |      hh

and if data_piece is a constant value in the system which is reused a lot, you need to add there a lookup table too.

I know it looks more work, but then it will save you issues like you have now, which take much more time to solve.

MYSQL Comma Delimited list, possible to add and remove values?

1 Answers1

Linked