mysql field data is array how can select item [duplicate]

Question

table Name Data

ID  | Name | List
-----------------
1   | Test 1 | 1,2,4,6
2   | Test 2 | 2,4
3   | Test 3 | 4,6,7
3   | Test 4 | 2

how can select items from 'data' table where 'list' have 2 ?

after run query want see this result :

ID  | Name | List
-----------------
1   | Test 1 | 1,**2**,4,6
2   | Test 2 | **2**,4
3   | Test 4 | **2**

You should normalize your table. 1 row per value. stackoverflow.com/questions/3653462/… — chris85
– chris85, Commented Feb 9, 2016 at 20:30
Yes, as @chris85 states. This is a horrible way to do it and will cause endless headaches. — AbraCadaver
– AbraCadaver, Commented Feb 9, 2016 at 20:31
quick/dirty hack: find_in_set(). proper fix: normalize your design before you cause yourself any more trouble. — Marc B
– Marc B, Commented Feb 9, 2016 at 20:33
@marc-b agreed bro.. Best way to normalized yur table else use find in set or LIKE — devpro
– devpro, Commented Feb 9, 2016 at 20:34

devpro · Accepted Answer · 2016-02-09 20:46:57Z

3

Suggestion:

First of all you need to normalize your table structure as one list Id in one row else you can use FIND_IN_SET

Solution:

SELECT * FROM Data WHERE FIND_IN_SET(2,List);

Suggestion 2:

One more suggestion you can also create a separate table for saving list Id against data id benefit is that name field which is string column will not repeat just suggestion.

edited Feb 9, 2016 at 20:46

answered Feb 9, 2016 at 20:40

devpro

16.1k3 gold badges29 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mysql field data is array how can select item [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related