Geek asks and answers

MYSQL Comma Separated List, Can I Add or Remove Values?

I have a comma delimited list that is stored in the varchar field in the mysql table.

Is it possible to add and remove values ​​from a list directly using sql queries? Or do I need to take data from a table, manipulate it with PHP and replace it back in mysql?

+2
source share
1 answer

In InnoDB and MyIsam tools in mysql there is no way to do this. May be in other engines (check CSV engine).
You can do this in a stored procedure, but not recommended.
What you have to do to solve this problem is to reorganize 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

data_piece , , .

, , , , .

+4

More articles:


All Articles