Q:

MySQL :: Select from comma separated string

I have the following tables:
filters
id | u_ids
1  | 1, 2, 3
2  | 5, 6

users
id | name
1  | Tom
2  | Tim
3  | Sue
4  | Bruce
5  | Ann
6  | George

And I want to run the following select
select * from users where id in (select u_ids from filters where id =1);

I would like to receive 
id | name
1  | Tom
2  | Tim
3  | Sue

But I receive nothing.
The problem is that the field u_ids is a text so the "in select" is returning something like "1, 2, 3" (with the semicolon) so the in does not find any value.
Is there any option to make a casting or something to change the string to an array?

A:

Its better to normalize your schema do not store relations in form of comma separated list instead create a junction table for this like to maintain a m:m many to many relation between users and filters,create a new table as user_filters with columns filter id and user id and in each row save one association per user and filter like in your current schema relation for filter 1 with many users (1, '1, 2, 3')  will become like 
filter id user id
    (1, '1'),
    (1, '2'),
    (1, '3'),

Sample schema will be like this 
CREATE TABLE user_filters
    (`fid` int, `u_id` varchar(50))
;

INSERT INTO user_filters
    (`fid`, `u_id`)
VALUES
    (1, '1'),
    (1, '2'),
    (1, '3'),
    (2, '5'),
    (2, '5')
;

CREATE TABLE filters
    (`id` int, `title` varchar(50))
;

INSERT INTO filters
    (`id`, `title`)
VALUES
    (1, 'test'),
    (2, 'test 1')
;

CREATE TABLE users
    (`id` int, `name` varchar(6))
;

INSERT INTO users
    (`id`, `name`)
VALUES
    (1, 'Tom'),
    (2, 'Tim'),
    (3, 'Sue'),
    (4, 'Bruce'),
    (5, 'Ann'),
    (6, 'George')
;

For above schema you can easily query with join as, below query can be optimized using indexes 
select u.* 
from users u
join user_filters uf on(uf.u_id = u.id)
 where uf.fid =1

Sample Demo

If you are not able to alter your schema and want to stick with the current one you can query as below but this one cannot be optimized enough as compare to above query
select u.* 
from users u
join filters f on(find_in_set(u.id,replace(`u_ids`,' ','')) > 0)
 where f.id =1 

Sample Demo
Database Normalization

